| 1 | package sessioncatalog |
| 2 | |
| 3 | import ( |
| 4 | "context" |
| 5 | "database/sql" |
| 6 | |
| 7 | "reasonix/internal/projectiondb" |
| 8 | ) |
| 9 | |
| 10 | const migrationV1 = ` |
| 11 | CREATE TABLE IF NOT EXISTS catalog_state ( |
| 12 | id INTEGER PRIMARY KEY CHECK (id = 1), |
| 13 | revision INTEGER NOT NULL DEFAULT 0 |
| 14 | ); |
| 15 | INSERT OR IGNORE INTO catalog_state(id, revision) VALUES(1, 0); |
| 16 | |
| 17 | CREATE TABLE IF NOT EXISTS catalog_directories ( |
| 18 | path TEXT PRIMARY KEY, |
| 19 | scope TEXT NOT NULL, |
| 20 | workspace_root TEXT NOT NULL DEFAULT '', |
| 21 | signature TEXT NOT NULL DEFAULT '', |
| 22 | scan_cursor TEXT NOT NULL DEFAULT '', |
| 23 | scan_generation INTEGER NOT NULL DEFAULT 0, |
| 24 | state TEXT NOT NULL DEFAULT 'pending', |
| 25 | error TEXT NOT NULL DEFAULT '', |
| 26 | indexed INTEGER NOT NULL DEFAULT 0, |
| 27 | total INTEGER NOT NULL DEFAULT 0, |
| 28 | completed_at INTEGER NOT NULL DEFAULT 0 |
| 29 | ); |
| 30 | |
| 31 | CREATE TABLE IF NOT EXISTS catalog_projects ( |
| 32 | scope TEXT NOT NULL, |
| 33 | workspace_root TEXT NOT NULL DEFAULT '', |
| 34 | title TEXT NOT NULL DEFAULT '', |
| 35 | color TEXT NOT NULL DEFAULT '', |
| 36 | pinned INTEGER NOT NULL DEFAULT 0, |
| 37 | sort_order INTEGER NOT NULL DEFAULT 0, |
| 38 | updated_at INTEGER NOT NULL DEFAULT 0, |
| 39 | PRIMARY KEY(scope, workspace_root) |
| 40 | ); |
| 41 | |
| 42 | CREATE TABLE IF NOT EXISTS catalog_topics ( |
| 43 | scope TEXT NOT NULL, |
| 44 | workspace_root TEXT NOT NULL DEFAULT '', |
| 45 | topic_id TEXT NOT NULL, |
| 46 | title TEXT NOT NULL DEFAULT '', |
| 47 | title_source TEXT NOT NULL DEFAULT '', |
| 48 | pinned INTEGER NOT NULL DEFAULT 0, |
| 49 | sort_order INTEGER NOT NULL DEFAULT 0, |
| 50 | turns INTEGER NOT NULL DEFAULT 0, |
| 51 | turns_state TEXT NOT NULL DEFAULT 'unknown', |
| 52 | created_at INTEGER NOT NULL DEFAULT 0, |
| 53 | last_activity_at INTEGER NOT NULL DEFAULT 0, |
| 54 | recovery_state TEXT NOT NULL DEFAULT '', |
| 55 | health TEXT NOT NULL DEFAULT 'ok', |
| 56 | PRIMARY KEY(scope, workspace_root, topic_id) |
| 57 | ); |
| 58 | |
| 59 | CREATE TABLE IF NOT EXISTS catalog_sessions ( |
| 60 | path TEXT PRIMARY KEY, |
| 61 | directory TEXT NOT NULL, |
| 62 | scope TEXT NOT NULL, |
| 63 | workspace_root TEXT NOT NULL DEFAULT '', |
| 64 | topic_id TEXT NOT NULL DEFAULT '', |
| 65 | topic_title TEXT NOT NULL DEFAULT '', |
| 66 | custom_title TEXT NOT NULL DEFAULT '', |
| 67 | created_at INTEGER NOT NULL DEFAULT 0, |
| 68 | last_activity_at INTEGER NOT NULL DEFAULT 0, |
| 69 | preview TEXT NOT NULL DEFAULT '', |
| 70 | turns INTEGER NOT NULL DEFAULT 0, |
| 71 | turns_state TEXT NOT NULL DEFAULT 'unknown', |
| 72 | recovered INTEGER NOT NULL DEFAULT 0, |
| 73 | recovery_reason TEXT NOT NULL DEFAULT '', |
| 74 | recovery_digest TEXT NOT NULL DEFAULT '', |
| 75 | parent_id TEXT NOT NULL DEFAULT '', |
| 76 | content_fingerprint TEXT NOT NULL DEFAULT '', |
| 77 | meta_fingerprint TEXT NOT NULL DEFAULT '', |
| 78 | health TEXT NOT NULL DEFAULT 'ok', |
| 79 | missing_since INTEGER NOT NULL DEFAULT 0, |
| 80 | seen_generation INTEGER NOT NULL DEFAULT 0 |
| 81 | ); |
| 82 | |
| 83 | CREATE INDEX IF NOT EXISTS idx_catalog_topics_page |
| 84 | ON catalog_topics(scope, workspace_root, pinned DESC, last_activity_at DESC, topic_id ASC); |
| 85 | CREATE INDEX IF NOT EXISTS idx_catalog_topics_activity |
| 86 | ON catalog_topics(last_activity_at DESC, topic_id ASC); |
| 87 | CREATE INDEX IF NOT EXISTS idx_catalog_sessions_topic |
| 88 | ON catalog_sessions(scope, workspace_root, topic_id, last_activity_at DESC, path ASC); |
| 89 | CREATE INDEX IF NOT EXISTS idx_catalog_sessions_directory |
| 90 | ON catalog_sessions(directory, seen_generation, missing_since); |
| 91 | CREATE INDEX IF NOT EXISTS idx_catalog_sessions_path |
| 92 | ON catalog_sessions(path); |
| 93 | ` |
| 94 | |
| 95 | const migrationV2 = ` |
| 96 | ALTER TABLE catalog_topics ADD COLUMN metadata_present INTEGER NOT NULL DEFAULT 0; |
| 97 | ` |
| 98 | |
| 99 | const migrationV3 = ` |
| 100 | CREATE INDEX IF NOT EXISTS idx_catalog_sessions_history |
| 101 | ON catalog_sessions(scope, workspace_root, last_activity_at DESC, path ASC); |
| 102 | ` |
| 103 | |
| 104 | const migrationV4 = ` |
| 105 | ALTER TABLE catalog_sessions ADD COLUMN recovery_copy INTEGER NOT NULL DEFAULT 0; |
| 106 | ` |
| 107 | |
| 108 | const migrationV5 = ` |
| 109 | ALTER TABLE catalog_sessions ADD COLUMN recovery_group_id TEXT NOT NULL DEFAULT ''; |
| 110 | ALTER TABLE catalog_sessions ADD COLUMN recovery_role TEXT NOT NULL DEFAULT ''; |
| 111 | ALTER TABLE catalog_sessions ADD COLUMN recovery_canonical INTEGER NOT NULL DEFAULT 0; |
| 112 | CREATE INDEX IF NOT EXISTS idx_catalog_sessions_recovery_group |
| 113 | ON catalog_sessions(recovery_group_id, recovery_role, last_activity_at DESC); |
| 114 | ` |
| 115 | |
| 116 | const migrationV6 = ` |
| 117 | ALTER TABLE catalog_topics ADD COLUMN recovery_branch_count INTEGER NOT NULL DEFAULT 0; |
| 118 | ALTER TABLE catalog_topics ADD COLUMN recovery_unresolved_count INTEGER NOT NULL DEFAULT 0; |
| 119 | ALTER TABLE catalog_topics ADD COLUMN recovery_cleanup_eligible_count INTEGER NOT NULL DEFAULT 0; |
| 120 | ` |
| 121 | |
| 122 | const migrationV7 = ` |
| 123 | ALTER TABLE catalog_sessions ADD COLUMN logical_topic_id TEXT NOT NULL DEFAULT ''; |
| 124 | ALTER TABLE catalog_sessions ADD COLUMN ordinary_visible INTEGER NOT NULL DEFAULT 0; |
| 125 | CREATE INDEX IF NOT EXISTS idx_catalog_sessions_ordinary |
| 126 | ON catalog_sessions(scope, workspace_root, ordinary_visible, last_activity_at DESC); |
| 127 | ` |
| 128 | |
| 129 | // Folded-topic tombstones remember that lineage projection re-anchored a |
| 130 | // recovery copy's sessions onto the canonical topic. SyncMetadata consults |
| 131 | // them so the desktop topic registry cannot resurrect the copy's pre-reanchor |
| 132 | // topic as a standalone sidebar row after its session rows moved away. |
| 133 | const migrationV8 = ` |
| 134 | CREATE TABLE IF NOT EXISTS catalog_folded_topics ( |
| 135 | scope TEXT NOT NULL, |
| 136 | workspace_root TEXT NOT NULL DEFAULT '', |
| 137 | topic_id TEXT NOT NULL, |
| 138 | folded_at INTEGER NOT NULL DEFAULT 0, |
| 139 | PRIMARY KEY(scope, workspace_root, topic_id) |
| 140 | ); |
| 141 | ` |
| 142 | |
| 143 | // v9 separates filesystem identity from the original access spelling. The |
| 144 | // production default moves to a fresh v6.sqlite generation together with this |
| 145 | // migration, so old v5 writers never share the new identity columns. Explicit |
| 146 | // legacy catalog paths are invalidated in place: filesystem-aware keys cannot |
| 147 | // be backfilled safely in SQL, and every deleted row is a disposable projection |
| 148 | // that the next authoritative directory reconciliation recreates. |
| 149 | const migrationV9 = ` |
| 150 | ALTER TABLE catalog_directories ADD COLUMN path_key TEXT NOT NULL DEFAULT ''; |
| 151 | ALTER TABLE catalog_sessions ADD COLUMN path_key TEXT NOT NULL DEFAULT ''; |
| 152 | ALTER TABLE catalog_sessions ADD COLUMN directory_key TEXT NOT NULL DEFAULT ''; |
| 153 | |
| 154 | DELETE FROM catalog_sessions; |
| 155 | DELETE FROM catalog_directories; |
| 156 | DELETE FROM catalog_topics; |
| 157 | DELETE FROM catalog_folded_topics; |
| 158 | |
| 159 | CREATE UNIQUE INDEX IF NOT EXISTS idx_catalog_directories_path_key |
| 160 | ON catalog_directories(path_key); |
| 161 | CREATE UNIQUE INDEX IF NOT EXISTS idx_catalog_sessions_path_key |
| 162 | ON catalog_sessions(path_key); |
| 163 | CREATE INDEX IF NOT EXISTS idx_catalog_sessions_directory_key |
| 164 | ON catalog_sessions(directory_key, seen_generation, missing_since); |
| 165 | ` |
| 166 | |
| 167 | // v10 extends filesystem identity to workspace roots. Access spellings remain |
| 168 | // available for UI/file access, while every relationship and uniqueness rule |
| 169 | // uses the filesystem-aware key. Existing v9 projections are disposable and |
| 170 | // must be rebuilt because SQL cannot infer volume-specific case semantics. |
| 171 | const migrationV10 = ` |
| 172 | ALTER TABLE catalog_projects ADD COLUMN workspace_root_key TEXT NOT NULL DEFAULT ''; |
| 173 | ALTER TABLE catalog_topics ADD COLUMN workspace_root_key TEXT NOT NULL DEFAULT ''; |
| 174 | ALTER TABLE catalog_sessions ADD COLUMN workspace_root_key TEXT NOT NULL DEFAULT ''; |
| 175 | ALTER TABLE catalog_folded_topics ADD COLUMN workspace_root_key TEXT NOT NULL DEFAULT ''; |
| 176 | |
| 177 | DELETE FROM catalog_sessions; |
| 178 | DELETE FROM catalog_directories; |
| 179 | DELETE FROM catalog_projects; |
| 180 | DELETE FROM catalog_topics; |
| 181 | DELETE FROM catalog_folded_topics; |
| 182 | |
| 183 | CREATE UNIQUE INDEX IF NOT EXISTS idx_catalog_projects_workspace_key |
| 184 | ON catalog_projects(scope, workspace_root_key); |
| 185 | CREATE UNIQUE INDEX IF NOT EXISTS idx_catalog_topics_workspace_key |
| 186 | ON catalog_topics(scope, workspace_root_key, topic_id); |
| 187 | CREATE UNIQUE INDEX IF NOT EXISTS idx_catalog_folded_topics_workspace_key |
| 188 | ON catalog_folded_topics(scope, workspace_root_key, topic_id); |
| 189 | CREATE INDEX IF NOT EXISTS idx_catalog_topics_workspace_page |
| 190 | ON catalog_topics(scope, workspace_root_key, pinned DESC, last_activity_at DESC, topic_id ASC); |
| 191 | CREATE INDEX IF NOT EXISTS idx_catalog_sessions_workspace_topic |
| 192 | ON catalog_sessions(scope, workspace_root_key, topic_id, last_activity_at DESC, path ASC); |
| 193 | CREATE INDEX IF NOT EXISTS idx_catalog_sessions_workspace_history |
| 194 | ON catalog_sessions(scope, workspace_root_key, last_activity_at DESC, path ASC); |
| 195 | CREATE INDEX IF NOT EXISTS idx_catalog_sessions_workspace_ordinary |
| 196 | ON catalog_sessions(scope, workspace_root_key, ordinary_visible, last_activity_at DESC); |
| 197 | ` |
| 198 | |
| 199 | // v11 persists repair scheduling in the disposable projection. A source or |
| 200 | // engine generation change resets a deferred/blocked row through the normal |
| 201 | // upsert path; otherwise restart preserves its retry budget. |
| 202 | const migrationV11 = ` |
| 203 | ALTER TABLE catalog_sessions ADD COLUMN repair_state TEXT NOT NULL DEFAULT 'pending'; |
| 204 | ALTER TABLE catalog_sessions ADD COLUMN repair_attempts INTEGER NOT NULL DEFAULT 0; |
| 205 | ALTER TABLE catalog_sessions ADD COLUMN repair_retry_at INTEGER NOT NULL DEFAULT 0; |
| 206 | ALTER TABLE catalog_sessions ADD COLUMN repair_error_kind TEXT NOT NULL DEFAULT ''; |
| 207 | ALTER TABLE catalog_sessions ADD COLUMN repair_source_fingerprint TEXT NOT NULL DEFAULT ''; |
| 208 | ALTER TABLE catalog_sessions ADD COLUMN repair_engine_version INTEGER NOT NULL DEFAULT 0; |
| 209 | |
| 210 | UPDATE catalog_sessions SET repair_state=CASE WHEN turns_state='unknown' THEN 'pending' ELSE 'complete' END; |
| 211 | CREATE INDEX IF NOT EXISTS idx_catalog_sessions_repair_due |
| 212 | ON catalog_sessions(repair_state, repair_retry_at, last_activity_at DESC, path_key); |
| 213 | ` |
| 214 | |
| 215 | // migrationV12 adds the schema-2 head projection. The generation file moved |
| 216 | // to v8.sqlite, and clearing the directory scans forces a rescan so every |
| 217 | // existing row learns its log format. |
| 218 | const migrationV12 = ` |
| 219 | ALTER TABLE catalog_sessions ADD COLUMN log_format INTEGER NOT NULL DEFAULT 1; |
| 220 | ALTER TABLE catalog_sessions ADD COLUMN head_count INTEGER NOT NULL DEFAULT 0; |
| 221 | ALTER TABLE catalog_sessions ADD COLUMN selected_head_id TEXT NOT NULL DEFAULT ''; |
| 222 | CREATE TABLE IF NOT EXISTS catalog_heads ( |
| 223 | path_key TEXT NOT NULL, |
| 224 | head_id TEXT NOT NULL, |
| 225 | parent_head_id TEXT NOT NULL DEFAULT '', |
| 226 | kind TEXT NOT NULL DEFAULT 'main', |
| 227 | name TEXT NOT NULL DEFAULT '', |
| 228 | leaf_message_id TEXT NOT NULL DEFAULT '', |
| 229 | writer_id TEXT NOT NULL DEFAULT '', |
| 230 | last_activity_at INTEGER NOT NULL DEFAULT 0, |
| 231 | turns INTEGER NOT NULL DEFAULT 0, |
| 232 | preview TEXT NOT NULL DEFAULT '', |
| 233 | retired INTEGER NOT NULL DEFAULT 0, |
| 234 | selected INTEGER NOT NULL DEFAULT 0, |
| 235 | PRIMARY KEY(path_key, head_id) |
| 236 | ); |
| 237 | CREATE INDEX IF NOT EXISTS idx_catalog_heads_activity ON catalog_heads(path_key, retired, last_activity_at DESC); |
| 238 | DELETE FROM catalog_directories; |
| 239 | ` |
| 240 | |
| 241 | // v13 invalidates every filesystem-derived key after path identity moved to |
| 242 | // the shared strict resolver. The catalog is disposable; transcripts and |
| 243 | // sidecars remain authoritative and rebuild the projection after restart. |
| 244 | const migrationV13 = ` |
| 245 | DELETE FROM catalog_heads; |
| 246 | DELETE FROM catalog_sessions; |
| 247 | DELETE FROM catalog_directories; |
| 248 | DELETE FROM catalog_projects; |
| 249 | DELETE FROM catalog_topics; |
| 250 | DELETE FROM catalog_folded_topics; |
| 251 | ` |
| 252 | |
| 253 | func sessionMigrations() []projectiondb.Migration { |
| 254 | return []projectiondb.Migration{ |
| 255 | {Version: 1, Apply: func(ctx context.Context, tx *sql.Tx) error { |
| 256 | _, err := tx.ExecContext(ctx, migrationV1) |
| 257 | return err |
| 258 | }}, |
| 259 | {Version: 2, Apply: func(ctx context.Context, tx *sql.Tx) error { |
| 260 | _, err := tx.ExecContext(ctx, migrationV2) |
| 261 | return err |
| 262 | }}, |
| 263 | {Version: 3, Apply: func(ctx context.Context, tx *sql.Tx) error { |
| 264 | _, err := tx.ExecContext(ctx, migrationV3) |
| 265 | return err |
| 266 | }}, |
| 267 | {Version: 4, Apply: func(ctx context.Context, tx *sql.Tx) error { |
| 268 | _, err := tx.ExecContext(ctx, migrationV4) |
| 269 | return err |
| 270 | }}, |
| 271 | {Version: 5, Apply: func(ctx context.Context, tx *sql.Tx) error { |
| 272 | _, err := tx.ExecContext(ctx, migrationV5) |
| 273 | return err |
| 274 | }}, |
| 275 | {Version: 6, Apply: func(ctx context.Context, tx *sql.Tx) error { |
| 276 | _, err := tx.ExecContext(ctx, migrationV6) |
| 277 | return err |
| 278 | }}, |
| 279 | {Version: 7, Apply: func(ctx context.Context, tx *sql.Tx) error { |
| 280 | _, err := tx.ExecContext(ctx, migrationV7) |
| 281 | return err |
| 282 | }}, |
| 283 | {Version: 8, Apply: func(ctx context.Context, tx *sql.Tx) error { |
| 284 | _, err := tx.ExecContext(ctx, migrationV8) |
| 285 | return err |
| 286 | }}, |
| 287 | {Version: 9, Apply: func(ctx context.Context, tx *sql.Tx) error { |
| 288 | _, err := tx.ExecContext(ctx, migrationV9) |
| 289 | return err |
| 290 | }}, |
| 291 | {Version: 10, Apply: func(ctx context.Context, tx *sql.Tx) error { |
| 292 | _, err := tx.ExecContext(ctx, migrationV10) |
| 293 | return err |
| 294 | }}, |
| 295 | {Version: 11, Apply: func(ctx context.Context, tx *sql.Tx) error { |
| 296 | _, err := tx.ExecContext(ctx, migrationV11) |
| 297 | return err |
| 298 | }}, |
| 299 | {Version: 12, Apply: func(ctx context.Context, tx *sql.Tx) error { |
| 300 | _, err := tx.ExecContext(ctx, migrationV12) |
| 301 | return err |
| 302 | }}, |
| 303 | {Version: 13, Apply: func(ctx context.Context, tx *sql.Tx) error { |
| 304 | _, err := tx.ExecContext(ctx, migrationV13) |
| 305 | return err |
| 306 | }}, |
| 307 | } |
| 308 | } |
| 309 |