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

using InvercargillSql.Migrations;

namespace ClankerSaddle.Data.Migrations {

    /**
     * The full ClankerSaddle schema in one migration (plan 11: every
     * earlier migration flattened — the database is not deployed, so
     * tear-down-and-recreate replaced incremental evolution; plan 17
     * flattened again over the force-wake era). Covers: projects and
     * their links; the plan-12 goals (with links — plan 15 C1/C2
     * deleted the goal-side block/comment tables: the goal agent's
     * blocks and comments ride the run task's own rows) and the
     * flattened task shape (`goal_id`, `kind`, `mode`, `creator`,
     * `enqueue_after_task_id`, plus the force-wake flags folded in
     * from the deleted M0002); tasks with comments and typed blocks;
     * saddle registrations with git identity and the per-saddle
     * token; sessions; the plan-17 UNIFIED EVENT LEDGER (`events`)
     * that replaced `session_commands`, `task_progress`,
     * `session_raw_log` and `saddle_entry_dedup` — every timeline,
     * narrative, resume-notice and log-view surface derives from it,
     * and daemon chunk pushes are idempotent on its
     * UNIQUE(session_id, seq); rules and their attachments; project
     * notes; the dashboard scratchpad notes (goal 8 task 3);
     * capability tags; the plan-11 notification tables.
     */
    public class M0001_Initial : Migration {

        public override string name { get { return "Create the ClankerSaddle schema"; } }
        public override uint64 serial { get { return 1; } }
        public override string migration_namespace { get { return "clanker-saddle"; } }

        public override void up(MigrationBuilder b) {
            b.create_table("projects", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<string>("name")
                    .not_null();
                t.column<string>("git_url")
                    .not_null();
                t.column<string>("default_branch")
                    .default_value<string>("main");
                t.column<string>("summary");
                t.column<DateTime?>("created_at");
            });

            b.create_table("project_links", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("parent_project_id")
                    .not_null()
                    .references("projects", "id");
                t.column<int64?>("linked_project_id")
                    .not_null()
                    .references("projects", "id");
                t.column<string>("relationship")
                    .not_null();
            });

            // Stacks (goal 8 task 2): a named bundle of reference
            // projects applied one-click to a project's or a goal's
            // link set — the common reference collections a fleet of
            // repos always wants attached.
            b.create_table("stacks", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<string>("name")
                    .not_null()
                    .unique();
                t.column<string>("summary");
                t.column<DateTime?>("created_at");
            });

            b.create_table("stack_projects", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("stack_id")
                    .not_null()
                    .references("stacks", "id")
                    .indexed();
                t.column<int64?>("project_id")
                    .not_null()
                    .references("projects", "id")
                    .indexed();
                t.column<string>("relationship")
                    .not_null()
                    .default_value<string>("reference");
                t.column<DateTime?>("created_at");
                t.unique("uq_stack_projects_pair", {"stack_id", "project_id"});
            });

            b.create_table("goals", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("project_id")
                    .not_null()
                    .references("projects", "id")
                    .indexed();
                t.column<string>("title")
                    .not_null();
                t.column<string>("brief")
                    .not_null();
                t.column<string>("branch_name")
                    .not_null();
                t.column<string>("base_branch");
                t.column<string>("status")
                    .default_value<string>("draft")
                    .indexed();
                t.column<int64?>("priority")
                    .default_value<int64?>(3);
                t.column<string>("plan");
                t.column<string>("completion_report");
                t.column<string>("creator")
                    .default_value<string>("human");
                t.column<DateTime?>("last_terminal_watermark");
                t.column<DateTime?>("created_at");
                t.column<DateTime?>("started_at");
                t.column<DateTime?>("completed_at");
                t.column<DateTime?>("paused_at");
                t.column<bool>("force_wake")
                    .default_value<bool>(false);
            });

            b.create_table("goal_links", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("goal_id")
                    .not_null()
                    .references("goals", "id")
                    .indexed();
                t.column<int64?>("project_id")
                    .not_null()
                    .references("projects", "id")
                    .indexed();
                t.column<string>("relationship")
                    .not_null();
            });

            b.create_table("tasks", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                // Goal 8 task 4: 0 = a STANDALONE task — no project
                // row exists, the workspace holds no clones and the
                // deliverable is the completion report. No FK (0 is
                // not a project id) and no index (every browse path
                // filters by goal or lists all).
                t.column<int64?>("project_id")
                    .not_null()
                    .default_value<int64?>(0);
                t.column<int64?>("goal_id")
                    .references("goals", "id")
                    .indexed();
                t.column<string>("kind")
                    .default_value<string>("task");
                t.column<string>("mode")
                    .default_value<string>("implement");
                t.column<string>("creator")
                    .default_value<string>("human");
                t.column<int64?>("enqueue_after_task_id")
                    .references("tasks", "id");
                t.column<string>("title")
                    .not_null();
                t.column<string>("description");
                t.column<string>("status")
                    .default_value<string>("draft")
                    .indexed();
                t.column<int64?>("priority")
                    .default_value<int64?>(5);
                t.column<string>("summary");
                t.column<string>("task_branch");
                t.column<string>("branch_from");
                t.column<DateTime?>("created_at");
                t.column<DateTime?>("started_at");
                t.column<DateTime?>("completed_at");
                t.column<DateTime?>("suspended_at");
                t.column<bool>("force_wake")
                    .default_value<bool>(false);
            });

            b.create_table("task_comments", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("task_id")
                    .not_null()
                    .references("tasks", "id")
                    .indexed();
                t.column<string>("author")
                    .not_null();
                t.column<string>("body")
                    .not_null();
                t.column<string>("quote");
                t.column<DateTime?>("created_at");
            });

            b.create_table("task_blocks", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("task_id")
                    .not_null()
                    .references("tasks", "id")
                    .indexed();
                t.column<string>("question")
                    .not_null();
                t.column<string>("answer");
                t.column<string>("type")
                    .default_value<string>("question");
                t.column<string>("options");
                t.column<string>("body");
                t.column<DateTime?>("answered_at");
                t.column<string>("answered_by");
                t.column<DateTime?>("created_at");
            });

            b.create_table("saddles", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<string>("name")
                    .not_null();
                t.column<string>("host")
                    .not_null();
                t.column<string>("status")
                    .default_value<string>("idle");
                t.column<string>("git_name");
                t.column<string>("git_email");
                t.column<string>("prompt_note");
                t.column<string>("ssh_public_key");
                t.column<string>("token");
                // goal 8 task 6: the workspace root the daemon reported at
                // /register — assignment paths are addressed on the ASKING
                // saddle's filesystem, not a shared /workspace default
                t.column<string>("workspace_root");
            });

            b.create_table("sessions", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("task_id")
                    .not_null()
                    .references("tasks", "id")
                    .indexed();
                t.column<int64?>("saddle_id")
                    .not_null()
                    .references("saddles", "id");
                t.column<DateTime?>("started_at");
                t.column<DateTime?>("ended_at");
                t.column<string>("outcome");
                t.column<string>("resume_notice");
            });

            // Plan 17 §7.3: the unified append-only event ledger.
            // Daemon events carry their session and its monotonic seq
            // (idempotent upsert on UNIQUE(session_id, seq) — the
            // entire dedup machinery in one constraint); web-originated
            // events carry neither (NULL session_id rows never collide
            // under SQLite's NULLs-distinct unique indexes). Everything
            // downstream — timelines, goal narratives, resume-notice
            // coalescing, the event log view — derives from this table.
            b.create_table("events", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("session_id")
                    .references("sessions", "id");
                t.column<int64?>("seq");
                t.column<string>("kind")
                    .not_null();
                t.column<string>("actor")
                    .not_null();
                t.column<string>("scope_type")
                    .not_null();
                t.column<int64?>("scope_id")
                    .not_null();
                t.column<string>("payload")
                    .not_null();
                t.column<DateTime?>("created_at")
                    .not_null();
                t.unique("uq_events_session_seq", {"session_id", "seq"});
            });

            b.create_table("rules", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<string>("name")
                    .not_null()
                    .unique();
                t.column<string>("body")
                    .not_null();
                t.column<DateTime?>("created_at");
                t.column<DateTime?>("updated_at");
            });

            b.create_table("rule_projects", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("rule_id")
                    .not_null()
                    .references("rules", "id")
                    .indexed();
                t.column<int64?>("project_id")
                    .not_null()
                    .references("projects", "id")
                    .indexed();
                t.column<DateTime?>("created_at");
                t.unique("uq_rule_projects_pair", {"rule_id", "project_id"});
            });

            b.create_table("task_rules", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("rule_id")
                    .not_null()
                    .references("rules", "id")
                    .indexed();
                t.column<int64?>("task_id")
                    .not_null()
                    .references("tasks", "id")
                    .indexed();
                t.column<DateTime?>("created_at");
                t.unique("uq_task_rules_pair", {"rule_id", "task_id"});
            });

            b.create_table("project_notes", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("project_id")
                    .not_null()
                    .references("projects", "id")
                    .indexed();
                t.column<int64?>("source_task_id")
                    .references("tasks", "id");
                t.column<string>("body")
                    .not_null();
                t.column<DateTime?>("created_at");
            });

            // Personal scratchpad notes (goal 8 task 3): a lightweight
            // todo/idea list on the dashboard — jotted down before a
            // batch of changes is ready to become a full goal. A note
            // promotes into a DRAFT goal (title = first line, brief =
            // the whole body); goal_id then links the created goal and
            // freezes the note (no further promote).
            b.create_table("notes", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<string>("body")
                    .not_null();
                t.column<bool>("done")
                    .not_null()
                    .default_value<bool>(false);
                t.column<DateTime?>("done_at");
                t.column<int64?>("goal_id")
                    .references("goals", "id");
                t.column<DateTime?>("created_at");
            });

            b.create_table("capabilities", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<string>("name")
                    .not_null()
                    .unique();
                t.column<DateTime?>("created_at");
            });

            b.create_table("saddle_capabilities", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("capability_id")
                    .not_null()
                    .references("capabilities", "id")
                    .indexed();
                t.column<int64?>("saddle_id")
                    .not_null()
                    .references("saddles", "id")
                    .indexed();
                t.unique("uq_saddle_capabilities_pair", {"capability_id", "saddle_id"});
            });

            b.create_table("task_capabilities", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("capability_id")
                    .not_null()
                    .references("capabilities", "id")
                    .indexed();
                t.column<int64?>("task_id")
                    .not_null()
                    .references("tasks", "id")
                    .indexed();
                t.unique("uq_task_capabilities_pair", {"capability_id", "task_id"});
            });

            b.create_table("push_subscriptions", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("user_id")
                    .not_null()
                    .indexed();
                t.column<string>("endpoint")
                    .not_null()
                    .unique();
                t.column<string>("p256dh")
                    .not_null();
                t.column<string>("auth")
                    .not_null();
                t.column<DateTime?>("created_at");
            });

            b.create_table("task_notify_prefs", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("task_id")
                    .not_null()
                    .references("tasks", "id")
                    .indexed();
                t.column<int64?>("user_id")
                    .not_null()
                    .indexed();
                t.unique("uq_task_notify_prefs_pair", {"task_id", "user_id"});
            });
        }

        public override void down(MigrationBuilder b) {
            b.drop_table("task_notify_prefs");
            b.drop_table("push_subscriptions");
            b.drop_table("task_capabilities");
            b.drop_table("saddle_capabilities");
            b.drop_table("capabilities");
            b.drop_table("notes");
            b.drop_table("project_notes");
            b.drop_table("task_rules");
            b.drop_table("rule_projects");
            b.drop_table("rules");
            b.drop_table("events");
            b.drop_table("sessions");
            b.drop_table("saddles");
            b.drop_table("task_blocks");
            b.drop_table("task_comments");
            b.drop_table("tasks");
            b.drop_table("goal_links");
            b.drop_table("goals");
            b.drop_table("project_links");
            b.drop_table("stack_projects");
            b.drop_table("stacks");
            b.drop_table("projects");
        }
    }
}