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). Covers:
     * projects and their links; the plan-12 goals (with links —
     * plan 15 C1 deleted `goal_blocks` and C2 deleted `goal_comments`:
     * 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` — the subtask machinery's
     * parent_task_id/plan/auto-* columns and the task_dependencies
     * table are gone); tasks with comments, typed blocks, progress
     * and the draft-lifecycle columns (`status` defaults to
     * 'draft'); saddle registrations with git identity
     * and the per-saddle token; the session + command + raw-log audit
     * trail; the plan-13 daemon-global entry dedup
     * (`saddle_entry_dedup`); rules and their attachments; project
     * notes; capability tags; and the plan-11 notification tables for
     * task E
     * (`push_subscriptions`, `task_notify_prefs` — included now so
     * D+E needs no migration).
     */
    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();
            });

            // Plan 12 goals: user-created units of agent-orchestrated
            // work. The brief locks once started; `branch_name` is the
            // user-named goal branch (uniqueness-validated against the
            // other live goals sharing involved repos); `plan` holds
            // the APPROVED plan markdown (rejected revisions live on
            // as the run task's answered plan blocks); `last_seen_event_id`
            // is the goal agent's consumed-event watermark (resume
            // coalescing); `branch_push_state` caches the
            // daemon-reported push state of the goal branches.
            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<int64?>("last_seen_event_id")
                    .default_value<int64?>(0);
                t.column<string>("branch_push_state");
                t.column<DateTime?>("created_at");
                t.column<DateTime?>("started_at");
                t.column<DateTime?>("completed_at");
                t.column<DateTime?>("paused_at");
            });

            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();
                t.column<int64?>("project_id")
                    .not_null()
                    .references("projects", "id")
                    .indexed();
                // Plan 12: goals own their task graph — a task links to
                // its goal (null = standalone), runs as a plain worker
                // or a goal agent (kind), carries a work mode, records
                // its creator (enqueue is creator-only) and —
                // human-created tasks only — an auto-enqueue
                // predecessor.
                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");
                // Plan 11 draft lifecycle: new tasks start in draft and
                // only the human Enqueue action queues them.
                t.column<string>("status")
                    .default_value<string>("draft")
                    .indexed();
                t.column<int64?>("priority")
                    .default_value<int64?>(5);
                t.column<string>("working_branch");
                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");
            });

            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("task_progress", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("task_id")
                    .not_null()
                    .references("tasks", "id")
                    .indexed();
                t.column<string>("summary")
                    .not_null();
                t.column<DateTime?>("created_at");
            });

            // Plan 15 C1/C2: the goal-side block and comment tables
            // are GONE — the goal agent's questions, plans and
            // completion reports ride the RUN TASK's task_blocks rows
            // (one row per question, answered in place; no dual-row
            // pair can fall out of sync), and its comment thread IS
            // the run task's task_comments (human comments inject
            // through the existing delivery paths).

            // The supervision narrative's event log: one row per
            // notable goal occurrence (plan reviews, agent task
            // operations, task terminal events, lifecycle moves) —
            // the resume machinery's watermark (`goals.
            // last_seen_event_id`) also lives on these ids.
            b.create_table("goal_events", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("goal_id")
                    .not_null()
                    .references("goals", "id")
                    .indexed();
                t.column<string>("kind")
                    .not_null();
                t.column<string>("text")
                    .not_null();
                t.column<int64?>("task_id");
                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>("container_id");
                t.column<string>("host")
                    .not_null();
                t.column<string>("status")
                    .default_value<string>("offline");
                t.column<int64?>("current_task_id")
                    .references("tasks", "id");
                t.column<DateTime?>("allocated_at");
                t.column<string>("git_name");
                t.column<string>("git_email");
                t.column<string>("ssh_public_key");
                t.column<string>("token");
            });

            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");
                // Plan 14 B4: what a RESUMED session's agent first
                // received — the resume_notice the allocation built,
                // stored on the session row at resume-allocation (null
                // for fresh sessions; the terminal page shows it above
                // the log).
                t.column<string>("resume_notice");
            });

            b.create_table("session_commands", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("session_id")
                    .not_null()
                    .references("sessions", "id")
                    .indexed();
                t.column<string>("command_text")
                    .not_null();
                t.column<string>("output_text");
                t.column<int64?>("exit_code");
                t.column<string>("type")
                    .default_value<string>("command");
                t.column<string>("piped_input");
                t.column<DateTime?>("started_at");
                t.column<DateTime?>("completed_at");
                t.column<int64?>("duration_ms");
            });

            // Plan 13 B1: the daemon-global entry dedup — every pushed
            // entry id this saddle has already ingested. The unique
            // (saddle_id, entry_id) pair is the replay-integrity key: a
            // redelivered entry under ANY session id (web restart,
            // session churn, a mis-keyed send) finds its row here and is
            // consumed silently.
            b.create_table("saddle_entry_dedup", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("saddle_id")
                    .not_null()
                    .references("saddles", "id")
                    .indexed();
                t.column<int64?>("entry_id")
                    .not_null();
                t.unique("uq_saddle_entry_dedup_pair", {"saddle_id", "entry_id"});
            });

            b.create_table("api_tokens", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("saddle_id")
                    .not_null()
                    .references("saddles", "id")
                    .indexed();
                t.column<string>("token")
                    .not_null()
                    .unique();
                t.column<DateTime?>("created_at");
            });

            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");
                // Bumped on edit — the resume-time context_update
                // delta keys on it (a rule edited while a workspace
                // was parked must reach the agent as a turn).
                t.column<DateTime?>("updated_at");
            });

            // The attachment timestamps (created_at) feed the same
            // delta: attaching an existing rule to a task or project
            // changes the effective instruction set without touching
            // the rule row.
            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");
            });

            b.create_table("session_raw_log", (t) => {
                t.column<int64?>("id")
                    .auto_increment()
                    .primary_key();
                t.column<int64?>("session_id")
                    .not_null()
                    .references("sessions", "id")
                    .indexed();
                t.column<int64?>("seq")
                    .not_null();
                t.column<string>("chunk")
                    .not_null();
                t.unique("uq_session_raw_log_seq", {"session_id", "seq"});
            });

            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"});
            });

            // Plan 11 task E: Web Push subscriptions, one row per
            // browser endpoint a user registered (the VAPID keypair is
            // generated at startup and persisted in the config dir, not
            // here). `user_id` references the Spry users table by value
            // without an FK — the auth schema owns that table.
            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");
            });

            // Plan 11 task E: a user's per-task "Notify me" opt-in for
            // the task's first start, failure and completion (block
            // events notify all push-subscribed users globally).
            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("saddle_entry_dedup");
            b.drop_table("goal_events");
            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("session_raw_log");
            b.drop_table("project_notes");
            b.drop_table("task_rules");
            b.drop_table("rule_projects");
            b.drop_table("rules");
            b.drop_table("api_tokens");
            b.drop_table("session_commands");
            b.drop_table("sessions");
            b.drop_table("saddles");
            b.drop_table("task_progress");
            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("projects");
        }
    }
}