Created
March 9, 2026 13:43
-
-
Save R44VC0RP/82c5895a3a1377165727c24143cf2db0 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ``` | |
| Brought ~/.local/share/opencode/opencode.db back into sync with the current Drizzle schema and repo migrations. | |
| - Backed up the database first to ~/.local/share/opencode/opencode.db.sync-backup-20260309T134117Z. | |
| - The DB had drifted: __drizzle_migrations contained two stale applied entries (20260228203230_blue_harpoon, 20260303231226_add_workspace_fields) and was missing the current 20260227213759_add_session_workspace_id. | |
| - The live schema also had the legacy workspace shape and an extra account table, while current Drizzle expects workspace.config in packages/opencode/src/control-plane/workspace.sql.ts:5 and session.workspace_id in packages/opencode/src/session/session.sql.ts:11. | |
| - I rebuilt workspace to the current shape, removed the legacy account table, and corrected the migration journal. The affected legacy tables were empty, so no user rows were lost. | |
| - Verified the final state: repo migration folders now match applied migrations exactly, workspace.config exists, account is gone, and session_workspace_idx is present. | |
| If you want, next I can: | |
| 1. add a small automated drift check in packages/opencode so this is caught earlier | |
| 2. add a formal repair path for legacy workspace schema similar to the existing session repair in packages/opencode/src/storage/db.ts:84 | |
| ``` | |
| ``` | |
| import os | |
| import sqlite3 | |
| from datetime import datetime, timezone | |
| root = '/Users/ryanvogel/.local/share/opencode' | |
| db_path = os.path.join(root, 'opencode.db') | |
| ts = datetime.now(timezone.utc).strftime('%Y%m%dT%H%M%SZ') | |
| backup_path = os.path.join(root, f'opencode.db.sync-backup-{ts}') | |
| current = [ | |
| '20260127222353_familiar_lady_ursula', | |
| '20260211171708_add_project_commands', | |
| '20260213144116_wakeful_the_professor', | |
| '20260225215848_workspace', | |
| '20260227213759_add_session_workspace_id', | |
| ] | |
| missing = ('20260227213759_add_session_workspace_id', 1772228279000) | |
| stale = [ | |
| '20260228203230_blue_harpoon', | |
| '20260303231226_add_workspace_fields', | |
| ] | |
| conn = sqlite3.connect(db_path) | |
| conn.row_factory = sqlite3.Row | |
| conn.execute('PRAGMA busy_timeout = 5000') | |
| backup = sqlite3.connect(backup_path) | |
| conn.backup(backup) | |
| backup.close() | |
| cols = {row['name'] for row in conn.execute("PRAGMA table_info('workspace')")} | |
| account_exists = conn.execute("SELECT 1 FROM sqlite_master WHERE type='table' AND name='account'").fetchone() is not None | |
| workspace_rows = conn.execute('SELECT COUNT(*) FROM workspace').fetchone()[0] if cols else 0 | |
| legacy_types = 0 | |
| if {'type', 'name', 'directory', 'extra'} & cols: | |
| legacy_types = conn.execute("SELECT COUNT(*) FROM workspace WHERE type IS NOT NULL AND type != 'worktree'").fetchone()[0] | |
| account_rows = conn.execute('SELECT COUNT(*) FROM account').fetchone()[0] if account_exists else 0 | |
| if legacy_types: | |
| raise SystemExit(f'aborting: found {legacy_types} legacy workspace rows with unsupported type values') | |
| if account_rows: | |
| raise SystemExit(f'aborting: found {account_rows} rows in legacy account table') | |
| conn.execute('BEGIN IMMEDIATE') | |
| try: | |
| if 'config' not in cols and cols: | |
| conn.execute( | |
| ''' | |
| CREATE TABLE `workspace__new` ( | |
| `id` text PRIMARY KEY, | |
| `branch` text, | |
| `project_id` text NOT NULL, | |
| `config` text NOT NULL, | |
| CONSTRAINT `fk_workspace_project_id_project_id_fk` | |
| FOREIGN KEY (`project_id`) REFERENCES `project`(`id`) ON DELETE CASCADE | |
| ) | |
| ''' | |
| ) | |
| conn.execute( | |
| ''' | |
| INSERT INTO `workspace__new` (`id`, `branch`, `project_id`, `config`) | |
| SELECT | |
| `id`, | |
| `branch`, | |
| `project_id`, | |
| json_object('type', 'worktree', 'directory', COALESCE(`directory`, '')) | |
| FROM `workspace` | |
| ''' | |
| ) | |
| conn.execute('DROP TABLE `workspace`') | |
| conn.execute('ALTER TABLE `workspace__new` RENAME TO `workspace`') | |
| if account_exists: | |
| conn.execute('DROP TABLE `account`') | |
| conn.execute( | |
| "DELETE FROM `__drizzle_migrations` WHERE name IN (?, ?)", | |
| stale, | |
| ) | |
| conn.execute( | |
| ''' | |
| INSERT INTO `__drizzle_migrations` (`hash`, `created_at`, `name`, `applied_at`) | |
| SELECT '', ?, ?, NULL | |
| WHERE NOT EXISTS ( | |
| SELECT 1 FROM `__drizzle_migrations` WHERE name = ? | |
| ) | |
| ''', | |
| (missing[1], missing[0], missing[0]), | |
| ) | |
| rows = [row[0] for row in conn.execute('SELECT name FROM __drizzle_migrations ORDER BY created_at')] | |
| if rows != current: | |
| raise SystemExit(f'aborting: unexpected migration rows after sync: {rows}') | |
| conn.commit() | |
| print(f'backup={backup_path}') | |
| print(f'workspace_rows={workspace_rows}') | |
| print(f'account_rows={account_rows}') | |
| print('status=ok') | |
| except BaseException: | |
| conn.rollback() | |
| raise | |
| finally: | |
| conn.close() | |
| ``` |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment