The best way to control Claude Code, Codex and more from any computer or phone

main
ts 265 lines 10.5 KB
Raw
1 import {
2 MigrationError,
3 migrationError,
4 quoteIdentifier,
5 resolveMigrations,
6 runMigrations,
7 type NormalizedMigrationsInput,
8 type SqlExecutor,
9 type StampedMigrationsState,
10 withPgClient,
11 } from "@alchemy.run/sql/Migrations";
12 import * as Effect from "effect/Effect";
13 import * as Redacted from "effect/Redacted";
14 import * as Schedule from "effect/Schedule";
15 import type { Client } from "pg";
16 import { generateDbAuthToken } from "../Connection/DbAuthToken.ts";
17 import { dsqlIndexTarget, parseDsqlStatements, prepareDsqlStatements } from "./MigrationSql.ts";
18
19 const dsqlError = migrationError("DSQL migration");
20
21 /** Acquire a verified TLS connection using the deploy identity's admin token. */
22 export const withDsqlClient = <A, E, R>(
23 endpoint: string,
24 use: (client: Client) => Effect.Effect<A, E, R>,
25 ) =>
26 Effect.gen(function* () {
27 const password = yield* generateDbAuthToken({
28 service: "dsql",
29 hostname: endpoint,
30 action: "DbConnectAdmin",
31 });
32 return yield* withPgClient(
33 {
34 host: endpoint,
35 port: 5432,
36 user: "admin",
37 database: "postgres",
38 password: Redacted.value(password),
39 ssl: { rejectUnauthorized: true },
40 connectionTimeoutMillis: 10_000,
41 // Client-side only: DSQL rejects a server-side `statement_timeout`.
42 query_timeout: 35_000,
43 },
44 use,
45 );
46 });
47
48 /**
49 * DSQL executes every statement in autocommit. A durable progress row records
50 * completed statements, and a running row deliberately blocks replay after an
51 * ambiguous disconnect. PostgreSQL ERROR responses confirm statement rollback;
52 * those statements can be retried. Async jobs are persisted before polling.
53 */
54 export const makeDsqlMigrationExecutor = (
55 client: Pick<Client, "query">,
56 table: string,
57 ): SqlExecutor => {
58 const progress = quoteIdentifier(`${table}__progress`, "postgres");
59 const query: SqlExecutor["query"] = (sql, params) =>
60 Effect.tryPromise({
61 try: async () =>
62 (await client.query(sql, [...(params ?? [])])).rows as Array<Record<string, unknown>>,
63 catch: dsqlError,
64 }).pipe(
65 Effect.retry({
66 while: (error) => sqlState(error.cause) === "40001",
67 schedule: Schedule.max([Schedule.exponential("100 millis"), Schedule.recurs(5)]),
68 }),
69 );
70 const batch: SqlExecutor["batch"] = (statements) =>
71 Effect.forEach(statements, (sql) => query(sql), { discard: true });
72
73 const waitForJob = (job: string) =>
74 Effect.gen(function* () {
75 for (let attempt = 0; attempt < 10; attempt++) {
76 const [row] = yield* query("SELECT status, details FROM sys.jobs WHERE job_id = $1", [job]);
77 if (row?.status === "completed") return;
78 if (row?.status === "failed") {
79 return yield* new MigrationError({
80 message: `DSQL index job ${job} failed: ${row.details as string}. Drop the invalid index and repair the migration progress before retrying.`,
81 });
82 }
83 if (!row)
84 return yield* new MigrationError({
85 message: `DSQL index job ${job} is no longer available. Verify the index and repair the migration progress before retrying.`,
86 });
87 if (attempt < 9) yield* Effect.sleep("5 seconds");
88 }
89 return yield* new MigrationError({
90 message: `DSQL index job ${job} is still running after 45 seconds. Retry the deploy to continue waiting.`,
91 });
92 });
93
94 return {
95 dialect: "postgres",
96 migrationTableId: "uuid",
97 transactionalDdl: false,
98 query,
99 batch,
100 applyMigration: (record, bookkeeping) =>
101 Effect.gen(function* () {
102 const statements = [
103 ...(yield* prepareDsqlStatements(record.sql, record.name)),
104 bookkeeping,
105 ];
106 yield* query(`CREATE TABLE IF NOT EXISTS ${progress} (
107 name text PRIMARY KEY, hash text NOT NULL,
108 next_statement integer NOT NULL, status text NOT NULL, job_id text
109 )`);
110 let [state] = yield* query(
111 `SELECT hash, next_statement, status, job_id FROM ${progress} WHERE name = $1`,
112 [record.name],
113 );
114 if (!state) {
115 yield* query(
116 `INSERT INTO ${progress} (name, hash, next_statement, status) VALUES ($1, $2, 0, 'ready')`,
117 [record.name, record.hash],
118 );
119 state = { hash: record.hash, next_statement: 0, status: "ready" };
120 }
121 if (state.hash !== record.hash) {
122 return yield* new MigrationError({
123 message: `DSQL migration ${record.name} changed after it started. Restore the original file (SHA256 ${state.hash as string}); actual SHA256: ${record.hash}.`,
124 });
125 }
126 const nextStatement = Number(state.next_statement);
127 if (
128 !Number.isSafeInteger(nextStatement) ||
129 nextStatement < 0 ||
130 nextStatement >= statements.length ||
131 !["ready", "running", "waiting"].includes(String(state.status)) ||
132 (state.status === "waiting" && typeof state.job_id !== "string")
133 ) {
134 return yield* new MigrationError({
135 message: `DSQL migration ${record.name} has inconsistent progress in ${progress}. Inspect and repair its history before deploying.`,
136 });
137 }
138 if (state.status === "running") {
139 return yield* new MigrationError({
140 message: `DSQL migration ${record.name}, statement ${Number(state.next_statement) + 1} has an uncertain outcome. Inspect the database before repairing ${progress}; Alchemy will not replay it automatically.`,
141 });
142 }
143 for (let index = Number(state.next_statement); index < statements.length; index++) {
144 if (state.status === "waiting" && state.job_id) {
145 yield* waitForJob(state.job_id as string);
146 } else {
147 yield* query(
148 `UPDATE ${progress} SET status = 'running', job_id = NULL WHERE name = $1`,
149 [record.name],
150 );
151 const rows = yield* query(statements[index]).pipe(
152 Effect.catchTag("MigrationError", (error) =>
153 // ERROR is a server-confirmed failed autocommit statement. A
154 // socket error/timeout has no such assurance: leave it running.
155 serverRejected(error.cause)
156 ? query(`UPDATE ${progress} SET status = 'ready' WHERE name = $1`, [
157 record.name,
158 ]).pipe(Effect.andThen(Effect.fail(error)))
159 : Effect.fail(error),
160 ),
161 );
162 const isIndex = /^\s*CREATE\s+(?:UNIQUE\s+)?INDEX\s+ASYNC\b/i.test(
163 parseDsqlStatements(statements[index])[0]?.code ?? "",
164 );
165 const job = isIndex ? rows[0]?.job_id : undefined;
166 if (isIndex && job === undefined) {
167 const target = dsqlIndexTarget(statements[index]);
168 const existing = target
169 ? yield* query(
170 `SELECT i.indisvalid FROM pg_index i
171 JOIN pg_class c ON c.oid = i.indexrelid
172 WHERE c.relname = $1 AND i.indrelid = to_regclass($2)`,
173 [target.name, target.table],
174 )
175 : [];
176 if (existing[0]?.indisvalid !== true) {
177 return yield* new MigrationError({
178 message: `DSQL index statement in ${record.name} returned no job ID and the index could not be verified as valid. Inspect the existing index before advancing the progress row in ${progress}.`,
179 });
180 }
181 }
182 if (job !== undefined) {
183 yield* query(
184 `UPDATE ${progress} SET status = 'waiting', job_id = $2 WHERE name = $1`,
185 [record.name, job],
186 );
187 yield* waitForJob(job as string);
188 }
189 }
190 yield* query(
191 `UPDATE ${progress} SET next_statement = $2, status = 'ready', job_id = NULL WHERE name = $1`,
192 [record.name, index + 1],
193 );
194 state.status = "ready";
195 }
196 }),
197 };
198 };
199
200 const sqlState = (cause: unknown) =>
201 typeof cause === "object" && cause !== null && "code" in cause ? cause.code : undefined;
202 const serverRejected = (cause: unknown) =>
203 typeof cause === "object" &&
204 cause !== null &&
205 "severity" in cause &&
206 cause.severity === "ERROR" &&
207 typeof sqlState(cause) === "string";
208
209 /**
210 * One cluster-wide durable mutex serializes migrators even across different
211 * migration table names. It has no lease: a crashed owner must be inspected
212 * before its lock is removed, so a slow DDL cannot outlive a stolen lease.
213 */
214 export const withDsqlMigrationLock = <A, E, R>(
215 executor: SqlExecutor,
216 use: Effect.Effect<A, E, R>,
217 ) =>
218 Effect.gen(function* () {
219 const owner = crypto.randomUUID();
220 yield* executor.query(
221 `CREATE TABLE IF NOT EXISTS "__alchemy_dsql_migration_lock" (id integer PRIMARY KEY, owner uuid NOT NULL)`,
222 );
223 yield* executor
224 .query(`INSERT INTO "__alchemy_dsql_migration_lock" (id, owner) VALUES (1, $1)`, [owner])
225 .pipe(
226 Effect.mapError(
227 (cause) =>
228 new MigrationError({
229 message:
230 "Could not acquire the DSQL migration lock. Another deploy may be active. After a crashed deploy, inspect migration progress and confirm the old process stopped before deleting the row in __alchemy_dsql_migration_lock.",
231 cause,
232 }),
233 ),
234 );
235 const result = yield* Effect.result(use);
236 // A failed release is surfaced; a retained lock must never look successful.
237 yield* executor.query(
238 `DELETE FROM "__alchemy_dsql_migration_lock" WHERE id = 1 AND owner = $1`,
239 [owner],
240 );
241 return yield* Effect.fromResult(result);
242 }).pipe(Effect.uninterruptible);
243
244 export const runDsqlMigrations = (options: {
245 endpoint: string;
246 input: NormalizedMigrationsInput;
247 stamped: StampedMigrationsState;
248 }) =>
249 runMigrations({
250 ...options,
251 withExecutor: (apply) =>
252 withDsqlClient(options.endpoint, (client) => {
253 const table = resolveMigrations(options).table;
254 if (new TextEncoder().encode(`${table}__progress`).length > 63) {
255 return Effect.fail(
256 new MigrationError({
257 message:
258 "DSQL migration table names must leave room for the __progress suffix within PostgreSQL's 63-byte identifier limit.",
259 }),
260 );
261 }
262 const executor = makeDsqlMigrationExecutor(client, table);
263 return withDsqlMigrationLock(executor, apply(executor));
264 }),
265 });