wip: logging improvements

7 files changed, 221 insertions, 5 deletions

diff --git a/internal/db/connect.go b/internal/db/connect.go
index b8fcb7362..f77ebdd2a 100644
--- a/internal/db/connect.go
+++ b/internal/db/connect.go
@@ -10,7 +10,7 @@ import (
 	_ "github.com/ncruces/go-sqlite3/embed"
 
 	"github.com/opencode-ai/opencode/internal/config"
-	"github.com/opencode-ai/opencode/internal/logging"
+	"log/slog"
 
 	"github.com/pressly/goose/v3"
 )
@@ -47,21 +47,21 @@ func Connect() (*sql.DB, error) {
 
 	for _, pragma := range pragmas {
 		if _, err = db.Exec(pragma); err != nil {
-			logging.Error("Failed to set pragma", pragma, err)
+			slog.Error("Failed to set pragma", pragma, err)
 		} else {
-			logging.Debug("Set pragma", "pragma", pragma)
+			slog.Debug("Set pragma", "pragma", pragma)
 		}
 	}
 
 	goose.SetBaseFS(FS)
 
 	if err := goose.SetDialect("sqlite3"); err != nil {
-		logging.Error("Failed to set dialect", "error", err)
+		slog.Error("Failed to set dialect", "error", err)
 		return nil, fmt.Errorf("failed to set dialect: %w", err)
 	}
 
 	if err := goose.Up(db, "migrations"); err != nil {
-		logging.Error("Failed to apply migrations", "error", err)
+		slog.Error("Failed to apply migrations", "error", err)
 		return nil, fmt.Errorf("failed to apply migrations: %w", err)
 	}
 	return db, nil
diff --git a/internal/db/db.go b/internal/db/db.go
index e71b86227..dfd606cb5 100644
--- a/internal/db/db.go
+++ b/internal/db/db.go
@@ -27,6 +27,9 @@ func Prepare(ctx context.Context, db DBTX) (*Queries, error) {
 	if q.createFileStmt, err = db.PrepareContext(ctx, createFile); err != nil {
 		return nil, fmt.Errorf("error preparing query CreateFile: %w", err)
 	}
+	if q.createLogStmt, err = db.PrepareContext(ctx, createLog); err != nil {
+		return nil, fmt.Errorf("error preparing query CreateLog: %w", err)
+	}
 	if q.createMessageStmt, err = db.PrepareContext(ctx, createMessage); err != nil {
 		return nil, fmt.Errorf("error preparing query CreateMessage: %w", err)
 	}
@@ -60,6 +63,9 @@ func Prepare(ctx context.Context, db DBTX) (*Queries, error) {
 	if q.getSessionByIDStmt, err = db.PrepareContext(ctx, getSessionByID); err != nil {
 		return nil, fmt.Errorf("error preparing query GetSessionByID: %w", err)
 	}
+	if q.listAllLogsStmt, err = db.PrepareContext(ctx, listAllLogs); err != nil {
+		return nil, fmt.Errorf("error preparing query ListAllLogs: %w", err)
+	}
 	if q.listFilesByPathStmt, err = db.PrepareContext(ctx, listFilesByPath); err != nil {
 		return nil, fmt.Errorf("error preparing query ListFilesByPath: %w", err)
 	}
@@ -69,6 +75,9 @@ func Prepare(ctx context.Context, db DBTX) (*Queries, error) {
 	if q.listLatestSessionFilesStmt, err = db.PrepareContext(ctx, listLatestSessionFiles); err != nil {
 		return nil, fmt.Errorf("error preparing query ListLatestSessionFiles: %w", err)
 	}
+	if q.listLogsBySessionStmt, err = db.PrepareContext(ctx, listLogsBySession); err != nil {
+		return nil, fmt.Errorf("error preparing query ListLogsBySession: %w", err)
+	}
 	if q.listMessagesBySessionStmt, err = db.PrepareContext(ctx, listMessagesBySession); err != nil {
 		return nil, fmt.Errorf("error preparing query ListMessagesBySession: %w", err)
 	}
@@ -100,6 +109,11 @@ func (q *Queries) Close() error {
 			err = fmt.Errorf("error closing createFileStmt: %w", cerr)
 		}
 	}
+	if q.createLogStmt != nil {
+		if cerr := q.createLogStmt.Close(); cerr != nil {
+			err = fmt.Errorf("error closing createLogStmt: %w", cerr)
+		}
+	}
 	if q.createMessageStmt != nil {
 		if cerr := q.createMessageStmt.Close(); cerr != nil {
 			err = fmt.Errorf("error closing createMessageStmt: %w", cerr)
@@ -155,6 +169,11 @@ func (q *Queries) Close() error {
 			err = fmt.Errorf("error closing getSessionByIDStmt: %w", cerr)
 		}
 	}
+	if q.listAllLogsStmt != nil {
+		if cerr := q.listAllLogsStmt.Close(); cerr != nil {
+			err = fmt.Errorf("error closing listAllLogsStmt: %w", cerr)
+		}
+	}
 	if q.listFilesByPathStmt != nil {
 		if cerr := q.listFilesByPathStmt.Close(); cerr != nil {
 			err = fmt.Errorf("error closing listFilesByPathStmt: %w", cerr)
@@ -170,6 +189,11 @@ func (q *Queries) Close() error {
 			err = fmt.Errorf("error closing listLatestSessionFilesStmt: %w", cerr)
 		}
 	}
+	if q.listLogsBySessionStmt != nil {
+		if cerr := q.listLogsBySessionStmt.Close(); cerr != nil {
+			err = fmt.Errorf("error closing listLogsBySessionStmt: %w", cerr)
+		}
+	}
 	if q.listMessagesBySessionStmt != nil {
 		if cerr := q.listMessagesBySessionStmt.Close(); cerr != nil {
 			err = fmt.Errorf("error closing listMessagesBySessionStmt: %w", cerr)
@@ -245,6 +269,7 @@ type Queries struct {
 	db                             DBTX
 	tx                             *sql.Tx
 	createFileStmt                 *sql.Stmt
+	createLogStmt                  *sql.Stmt
 	createMessageStmt              *sql.Stmt
 	createSessionStmt              *sql.Stmt
 	deleteFileStmt                 *sql.Stmt
@@ -256,9 +281,11 @@ type Queries struct {
 	getFileByPathAndSessionStmt    *sql.Stmt
 	getMessageStmt                 *sql.Stmt
 	getSessionByIDStmt             *sql.Stmt
+	listAllLogsStmt                *sql.Stmt
 	listFilesByPathStmt            *sql.Stmt
 	listFilesBySessionStmt         *sql.Stmt
 	listLatestSessionFilesStmt     *sql.Stmt
+	listLogsBySessionStmt          *sql.Stmt
 	listMessagesBySessionStmt      *sql.Stmt
 	listMessagesBySessionAfterStmt *sql.Stmt
 	listNewFilesStmt               *sql.Stmt
@@ -273,6 +300,7 @@ func (q *Queries) WithTx(tx *sql.Tx) *Queries {
 		db:                             tx,
 		tx:                             tx,
 		createFileStmt:                 q.createFileStmt,
+		createLogStmt:                  q.createLogStmt,
 		createMessageStmt:              q.createMessageStmt,
 		createSessionStmt:              q.createSessionStmt,
 		deleteFileStmt:                 q.deleteFileStmt,
@@ -284,9 +312,11 @@ func (q *Queries) WithTx(tx *sql.Tx) *Queries {
 		getFileByPathAndSessionStmt:    q.getFileByPathAndSessionStmt,
 		getMessageStmt:                 q.getMessageStmt,
 		getSessionByIDStmt:             q.getSessionByIDStmt,
+		listAllLogsStmt:                q.listAllLogsStmt,
 		listFilesByPathStmt:            q.listFilesByPathStmt,
 		listFilesBySessionStmt:         q.listFilesBySessionStmt,
 		listLatestSessionFilesStmt:     q.listLatestSessionFilesStmt,
+		listLogsBySessionStmt:          q.listLogsBySessionStmt,
 		listMessagesBySessionStmt:      q.listMessagesBySessionStmt,
 		listMessagesBySessionAfterStmt: q.listMessagesBySessionAfterStmt,
 		listNewFilesStmt:               q.listNewFilesStmt,
diff --git a/internal/db/logs.sql.go b/internal/db/logs.sql.go
new file mode 100644
index 000000000..d227b472a
--- /dev/null
+++ b/internal/db/logs.sql.go
@@ -0,0 +1,128 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+// source: logs.sql
+
+package db
+
+import (
+	"context"
+	"database/sql"
+)
+
+const createLog = `-- name: CreateLog :exec
+INSERT INTO logs (
+    id,
+    session_id,
+    timestamp,
+    level,
+    message,
+    attributes,
+    created_at
+) VALUES (
+    ?,
+    ?,
+    ?,
+    ?,
+    ?,
+    ?,
+    ?
+)
+`
+
+type CreateLogParams struct {
+	ID         string         `json:"id"`
+	SessionID  sql.NullString `json:"session_id"`
+	Timestamp  int64          `json:"timestamp"`
+	Level      string         `json:"level"`
+	Message    string         `json:"message"`
+	Attributes sql.NullString `json:"attributes"`
+	CreatedAt  int64          `json:"created_at"`
+}
+
+func (q *Queries) CreateLog(ctx context.Context, arg CreateLogParams) error {
+	_, err := q.exec(ctx, q.createLogStmt, createLog,
+		arg.ID,
+		arg.SessionID,
+		arg.Timestamp,
+		arg.Level,
+		arg.Message,
+		arg.Attributes,
+		arg.CreatedAt,
+	)
+	return err
+}
+
+const listAllLogs = `-- name: ListAllLogs :many
+SELECT id, session_id, timestamp, level, message, attributes, created_at FROM logs
+ORDER BY timestamp DESC
+LIMIT ?
+`
+
+func (q *Queries) ListAllLogs(ctx context.Context, limit int64) ([]Log, error) {
+	rows, err := q.query(ctx, q.listAllLogsStmt, listAllLogs, limit)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []Log{}
+	for rows.Next() {
+		var i Log
+		if err := rows.Scan(
+			&i.ID,
+			&i.SessionID,
+			&i.Timestamp,
+			&i.Level,
+			&i.Message,
+			&i.Attributes,
+			&i.CreatedAt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const listLogsBySession = `-- name: ListLogsBySession :many
+SELECT id, session_id, timestamp, level, message, attributes, created_at FROM logs
+WHERE session_id = ?
+ORDER BY timestamp ASC
+`
+
+func (q *Queries) ListLogsBySession(ctx context.Context, sessionID sql.NullString) ([]Log, error) {
+	rows, err := q.query(ctx, q.listLogsBySessionStmt, listLogsBySession, sessionID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []Log{}
+	for rows.Next() {
+		var i Log
+		if err := rows.Scan(
+			&i.ID,
+			&i.SessionID,
+			&i.Timestamp,
+			&i.Level,
+			&i.Message,
+			&i.Attributes,
+			&i.CreatedAt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
diff --git a/internal/db/migrations/20250508122310_create_logs_table.sql b/internal/db/migrations/20250508122310_create_logs_table.sql
new file mode 100644
index 000000000..f4876fbae
--- /dev/null
+++ b/internal/db/migrations/20250508122310_create_logs_table.sql
@@ -0,0 +1,16 @@
+-- +goose Up
+CREATE TABLE logs (
+    id TEXT PRIMARY KEY,
+    session_id TEXT REFERENCES sessions(id) ON DELETE CASCADE,
+    timestamp INTEGER NOT NULL,
+    level TEXT NOT NULL,
+    message TEXT NOT NULL,
+    attributes TEXT,
+    created_at INTEGER NOT NULL
+);
+
+CREATE INDEX logs_session_id_idx ON logs(session_id);
+CREATE INDEX logs_timestamp_idx ON logs(timestamp);
+
+-- +goose Down
+DROP TABLE logs;
\ No newline at end of file
diff --git a/internal/db/models.go b/internal/db/models.go
index 47028c19a..473e18db6 100644
--- a/internal/db/models.go
+++ b/internal/db/models.go
@@ -18,6 +18,16 @@ type File struct {
 	UpdatedAt int64  `json:"updated_at"`
 }
 
+type Log struct {
+	ID         string         `json:"id"`
+	SessionID  sql.NullString `json:"session_id"`
+	Timestamp  int64          `json:"timestamp"`
+	Level      string         `json:"level"`
+	Message    string         `json:"message"`
+	Attributes sql.NullString `json:"attributes"`
+	CreatedAt  int64          `json:"created_at"`
+}
+
 type Message struct {
 	ID         string         `json:"id"`
 	SessionID  string         `json:"session_id"`
diff --git a/internal/db/querier.go b/internal/db/querier.go
index ee0a2f7bd..08ad41ec7 100644
--- a/internal/db/querier.go
+++ b/internal/db/querier.go
@@ -6,10 +6,12 @@ package db
 
 import (
 	"context"
+	"database/sql"
 )
 
 type Querier interface {
 	CreateFile(ctx context.Context, arg CreateFileParams) (File, error)
+	CreateLog(ctx context.Context, arg CreateLogParams) error
 	CreateMessage(ctx context.Context, arg CreateMessageParams) (Message, error)
 	CreateSession(ctx context.Context, arg CreateSessionParams) (Session, error)
 	DeleteFile(ctx context.Context, id string) error
@@ -21,9 +23,11 @@ type Querier interface {
 	GetFileByPathAndSession(ctx context.Context, arg GetFileByPathAndSessionParams) (File, error)
 	GetMessage(ctx context.Context, id string) (Message, error)
 	GetSessionByID(ctx context.Context, id string) (Session, error)
+	ListAllLogs(ctx context.Context, limit int64) ([]Log, error)
 	ListFilesByPath(ctx context.Context, path string) ([]File, error)
 	ListFilesBySession(ctx context.Context, sessionID string) ([]File, error)
 	ListLatestSessionFiles(ctx context.Context, sessionID string) ([]File, error)
+	ListLogsBySession(ctx context.Context, sessionID sql.NullString) ([]Log, error)
 	ListMessagesBySession(ctx context.Context, sessionID string) ([]Message, error)
 	ListMessagesBySessionAfter(ctx context.Context, arg ListMessagesBySessionAfterParams) ([]Message, error)
 	ListNewFiles(ctx context.Context) ([]File, error)
diff --git a/internal/db/sql/logs.sql b/internal/db/sql/logs.sql
new file mode 100644
index 000000000..1a0655f7e
--- /dev/null
+++ b/internal/db/sql/logs.sql
@@ -0,0 +1,28 @@
+-- name: CreateLog :exec
+INSERT INTO logs (
+    id,
+    session_id,
+    timestamp,
+    level,
+    message,
+    attributes,
+    created_at
+) VALUES (
+    ?,
+    ?,
+    ?,
+    ?,
+    ?,
+    ?,
+    ?
+);
+
+-- name: ListLogsBySession :many
+SELECT * FROM logs
+WHERE session_id = ?
+ORDER BY timestamp ASC;
+
+-- name: ListAllLogs :many
+SELECT * FROM logs
+ORDER BY timestamp DESC
+LIMIT ?;
\ No newline at end of file