clanker-saddle/src/Data/Migrations/M0006_Advisor.vala

using InvercargillSql.Migrations;
using Sqlite;

namespace ClankerSaddle.Data.Migrations {

    /**
     * Goal 27 (the advisor): the AI Chat loop's storage — chats,
     * messages and suggestions.
     *
     * advisor_chats is one conversation between a user and the
     * read-only advisor LLM loop; `status` is the loop's coarse state
     * ('idle' — no turn in flight, 'running' — a turn is executing,
     * 'awaiting_answer' — the loop parked on an ask_question and waits
     * for the human's answer).
     *
     * advisor_messages is the conversation stored IN THE WIRE FORMAT:
     * user / assistant / tool rows reconstruct the OpenAI messages
     * array on every turn (tool rows carry the call's name, arguments
     * and result in the JSON `payload`), so a restart loses nothing —
     * there is no separate transcript cache. 'error' rows carry a
     * failed turn's message; 'system' rows carry advisor-external
     * notices (task completion callbacks). A question is an assistant
     * row whose payload holds the options array and whose `answer`
     * column is filled when the human replies.
     *
     * advisor_suggestions is a suggested task or goal: INERT until the
     * chat's user approves it in the UI ('pending' → 'approved' or
     * 'declined'); an approved row keeps the created task_id / goal_id
     * so the completion callback (task 3 of the goal) can find the chat
     * again. delivered_at marks the callback delivered.
     *
     * IDEMPOTENCE: same contract as M0002–M0005 — up() runs before the
     * apply transaction opens (the builder only queues), so it
     * introspects sqlite_master through a separate read-only handle and
     * queues the tables only when the schema still lacks them.
     */
    public class M0006_Advisor : Migration {

        public override string name { get { return "Advisor chats"; } }
        public override uint64 serial { get { return 6; } }
        public override string migration_namespace { get { return "clanker-saddle"; } }

        // Same resolution as M0002–M0005: the runner never hands up()
        // a connection, so the schema is read through a read-only
        // handle on the SPRY_DB_PATH main.vala connects to.
        private string db_path() {
            var path = Environment.get_variable("SPRY_DB_PATH") ?? "";
            if (path.strip().length == 0) {
                return "./clanker-saddle.sqlite";
            }
            return path;
        }

        private bool table_exists(string name) {
            Database db;
            int rc = Database.open_v2(db_path(), out db, Sqlite.OPEN_READONLY);
            if (rc != Sqlite.OK) {
                return false;
            }
            Statement statement;
            rc = db.prepare_v2(
                "SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = ?",
                -1, out statement);
            if (rc != Sqlite.OK) {
                return false;
            }
            statement.bind_text(1, name, -1);
            if (statement.step() != Sqlite.ROW) {
                return false;
            }
            return statement.column_int64(0) > 0;
        }

        public override void up(MigrationBuilder b) {
            if (table_exists("advisor_chats")) {
                return;
            }
            b.create_table("advisor_chats", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("user_id")
                    .not_null();
                t.column<string>("title")
                    .not_null()
                    .default_value<string>("");
                t.column<string>("status")
                    .not_null()
                    .default_value<string>("idle");
                t.column<DateTime?>("created_at");
                t.column<DateTime?>("updated_at");
            });
            b.create_table("advisor_messages", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("chat_id")
                    .not_null();
                t.column<string>("kind")
                    .not_null()
                    .default_value<string>("user");
                t.column<string>("content")
                    .not_null()
                    .default_value<string>("");
                t.column<string>("payload");
                t.column<string>("answer");
                t.column<DateTime?>("answered_at");
                t.column<DateTime?>("created_at");
            });
            b.create_table("advisor_suggestions", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("chat_id")
                    .not_null();
                t.column<int64?>("user_id")
                    .not_null();
                t.column<string>("kind")
                    .not_null()
                    .default_value<string>("task");
                t.column<string>("payload")
                    .not_null()
                    .default_value<string>("{}");
                t.column<string>("status")
                    .not_null()
                    .default_value<string>("pending");
                t.column<int64?>("task_id")
                    .not_null()
                    .default_value<int64?>(0);
                t.column<int64?>("goal_id")
                    .not_null()
                    .default_value<int64?>(0);
                t.column<DateTime?>("created_at");
                t.column<DateTime?>("decided_at");
                t.column<DateTime?>("delivered_at");
            });
        }

        public override void down(MigrationBuilder b) {
            b.drop_table("advisor_suggestions");
            b.drop_table("advisor_messages");
            b.drop_table("advisor_chats");
        }
    }
}