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

main
ts 357 lines 12.3 KB
Raw
1 import * as crypto from "node:crypto";
2 import * as tls from "node:tls";
3 import {
4 makeStatementExecutor,
5 MigrationError,
6 migrationError,
7 probeExecutor,
8 retryLogin,
9 rowsFromCells,
10 runMigrations,
11 withMySqlExecutor,
12 withPgExecutor,
13 type NormalizedMigrationsInput,
14 type SqlExecutor,
15 type StampedMigrationsState,
16 } from "@alchemy.run/sql/Migrations";
17 import * as secretmanager from "@distilled.cloud/gcp/secretmanager_v1";
18 import * as sqladmin from "@distilled.cloud/gcp/sqladmin_v1";
19 import * as Effect from "effect/Effect";
20 import * as Redacted from "effect/Redacted";
21
22 /**
23 * How migrations reach a Cloud SQL database:
24 *
25 * - `"data-api"` — Cloud SQL's `instances.executeSql` over HTTPS. Needs
26 * `dataApiAccess: true` on the instance; no network route to the
27 * database. The Data API only reads passwords from Secret Manager, so
28 * each deploy stages the password in a short-lived regional secret.
29 * - `"connector"` — a `pg`/`mysql2` connection from the deploying machine
30 * through the Cloud SQL connector (an ephemeral client certificate from
31 * the SQL Admin API, TLS to port 3307). Needs a route to the instance's
32 * public (or private) IP, but no authorized networks.
33 */
34 export type MigrationsTransport = "data-api" | "connector";
35
36 /** The connector's server-side proxy port. */
37 const CONNECTOR_PORT = 3307;
38 const CONNECT_TIMEOUT_MILLIS = 15_000;
39
40 const dialectOf = (
41 instance: string,
42 databaseVersion: string | undefined,
43 ): Effect.Effect<"postgres" | "mysql", MigrationError> => {
44 const version = (databaseVersion ?? "").toUpperCase();
45 if (version.startsWith("POSTGRES_")) return Effect.succeed("postgres");
46 if (version.startsWith("MYSQL_")) return Effect.succeed("mysql");
47 return Effect.fail(
48 new MigrationError({
49 message: `Cloud SQL instance '${instance}' runs ${databaseVersion ?? "an unknown engine"}; migrations support PostgreSQL and MySQL`,
50 }),
51 );
52 };
53
54 /** Secret ids allow letters, numbers, `-` and `_`, up to 255 characters. */
55 const stagedSecretId = (instance: string, database: string) =>
56 `alchemy-migrations-${instance}-${database}`.replace(/[^A-Za-z0-9_-]/g, "-").slice(0, 255);
57
58 /**
59 * Write the password into a regional secret in the instance's region for
60 * the scope of `use`, then delete the secret. The id is deterministic, so a
61 * secret left behind by a crashed deploy is reused and removed by the next.
62 */
63 const withStagedPassword = <A, E, R>(
64 options: {
65 project: string;
66 region: string;
67 secretId: string;
68 password: string;
69 labels: Record<string, string>;
70 },
71 use: (secretVersion: string) => Effect.Effect<A, E, R>,
72 ) => {
73 const parent = `projects/${options.project}/locations/${options.region}`;
74 const name = `${parent}/secrets/${options.secretId}`;
75 return Effect.acquireUseRelease(
76 Effect.gen(function* () {
77 const data = yield* Effect.sync(() =>
78 Buffer.from(options.password, "utf8").toString("base64"),
79 );
80 yield* secretmanager
81 .createProjectsLocationsSecrets({
82 parent,
83 secretId: options.secretId,
84 body: { labels: options.labels },
85 })
86 .pipe(Effect.catchTag("Conflict", () => Effect.void));
87 const version = yield* secretmanager.addVersionProjectsLocationsSecrets({
88 parent: name,
89 body: { payload: { data } },
90 });
91 return version.name ?? `${name}/versions/latest`;
92 }).pipe(Effect.mapError(migrationError(`Staging the migrations password in ${name}`))),
93 use,
94 () =>
95 secretmanager.deleteProjectsLocationsSecrets({ name }).pipe(
96 Effect.catchTag("NotFound", () => Effect.void),
97 Effect.ignore,
98 ),
99 );
100 };
101
102 /**
103 * `instances.executeSql` as a {@link SqlExecutor}: each migration batch is
104 * one request wrapped in a transaction (MySQL DDL still commits
105 * implicitly).
106 */
107 const makeDataApiExecutor = Effect.fn(function* (options: {
108 dialect: "postgres" | "mysql";
109 project: string;
110 instance: string;
111 database: string;
112 user: string;
113 passwordSecretVersion: string;
114 }) {
115 const executeSql = yield* sqladmin.executeSqlInstances;
116 return makeStatementExecutor({
117 dialect: options.dialect,
118 batch: "transaction",
119 execute: (sqlStatement) =>
120 executeSql({
121 project: options.project,
122 instance: options.instance,
123 body: {
124 database: options.database,
125 user: options.user,
126 passwordSecretVersion: options.passwordSecretVersion,
127 sqlStatement,
128 },
129 }).pipe(
130 Effect.mapError(migrationError(`executeSql on '${options.instance}'`)),
131 // SQL errors come back inside a 200 response.
132 Effect.flatMap((response) => {
133 const failure = response.status?.code
134 ? response.status
135 : response.results?.find((result) => result.status?.code)?.status;
136 if (failure) {
137 return Effect.fail(
138 new MigrationError({
139 message: `executeSql on '${options.instance}' failed: ${failure.message ?? `code ${failure.code}`}`,
140 }),
141 );
142 }
143 const last = response.results?.at(-1);
144 return Effect.succeed(rowsFromCells(last?.columns, last?.rows));
145 }),
146 ),
147 });
148 });
149
150 /**
151 * Open a TLS socket to the instance through the Cloud SQL connector: an
152 * ephemeral client certificate signed by the SQL Admin API, verified
153 * against the instance's server CA. The socket is ready for the database
154 * protocol (no `SSLRequest`, no MySQL TLS upgrade).
155 */
156 const dialConnector = Effect.fn(function* (options: { project: string; instance: string }) {
157 const settings = yield* sqladmin
158 .getConnect({ project: options.project, instance: options.instance })
159 .pipe(Effect.mapError(migrationError(`Reading connect settings of '${options.instance}'`)));
160 const address =
161 settings.ipAddresses?.find((ip) => ip.type === "PRIMARY")?.ipAddress ??
162 settings.ipAddresses?.find((ip) => ip.type === "PRIVATE")?.ipAddress;
163 const serverCa = settings.serverCaCert?.cert;
164 if (!address || !serverCa) {
165 return yield* new MigrationError({
166 message: `Cloud SQL instance '${options.instance}' has no IP address to connect to`,
167 });
168 }
169 const keys = yield* Effect.sync(() =>
170 crypto.generateKeyPairSync("rsa", {
171 modulusLength: 2048,
172 publicKeyEncoding: { type: "spki", format: "pem" },
173 privateKeyEncoding: { type: "pkcs8", format: "pem" },
174 }),
175 );
176 const ephemeral = yield* sqladmin
177 .generateEphemeralCertConnect({
178 project: options.project,
179 instance: options.instance,
180 body: { public_key: keys.publicKey },
181 })
182 .pipe(
183 Effect.mapError(migrationError(`Requesting a client certificate for '${options.instance}'`)),
184 );
185 const clientCert = ephemeral.ephemeralCert?.cert;
186 if (!clientCert) {
187 return yield* new MigrationError({
188 message: `The SQL Admin API returned no client certificate for '${options.instance}'`,
189 });
190 }
191 // Per-instance CAs name the instance `project:instance`; shared and
192 // customer CAs identify it by its DNS name.
193 const legacyName = `${options.project}:${options.instance}`;
194 const dnsName = settings.serverCaMode?.includes("CAS") ? settings.dnsName : undefined;
195 return yield* Effect.callback<tls.TLSSocket, MigrationError>((resume) => {
196 const socket = tls.connect({
197 host: address,
198 port: CONNECTOR_PORT,
199 ca: serverCa,
200 cert: clientCert,
201 key: keys.privateKey,
202 servername: dnsName,
203 timeout: CONNECT_TIMEOUT_MILLIS,
204 checkServerIdentity: (host, cert) =>
205 dnsName
206 ? tls.checkServerIdentity(dnsName.replace(/\.$/, ""), cert)
207 : cert.subject.CN === legacyName
208 ? undefined
209 : new Error(
210 `server certificate names '${[cert.subject.CN].flat().join(", ")}', expected '${legacyName}'`,
211 ),
212 });
213 const fail = (cause: Error) => {
214 socket.destroy();
215 resume(
216 Effect.fail(
217 new MigrationError({
218 message: `Failed to connect to '${options.instance}' at ${address}:${CONNECTOR_PORT}: ${cause.message}`,
219 cause,
220 }),
221 ),
222 );
223 };
224 socket.once("error", fail);
225 socket.once("timeout", () => fail(new Error("timed out")));
226 socket.once("secureConnect", () => {
227 socket.off("error", fail);
228 socket.setTimeout(0);
229 resume(Effect.succeed(socket));
230 });
231 return Effect.sync(() => socket.destroy());
232 });
233 });
234
235 /**
236 * A `pg`/`mysql2` session over the Cloud SQL connector for the scope of
237 * `use`. A new user's login delay is retried on the dial and login, never
238 * on `use`.
239 */
240 const withConnectorExecutor = <A, E, R>(
241 options: {
242 dialect: "postgres" | "mysql";
243 project: string;
244 instance: string;
245 database: string;
246 user: string;
247 password: string;
248 },
249 use: (executor: SqlExecutor) => Effect.Effect<A, E, R>,
250 ) =>
251 Effect.gen(function* () {
252 const socket = yield* dialConnector(options);
253 const credentials = {
254 database: options.database,
255 user: options.user,
256 password: options.password,
257 };
258 return yield* options.dialect === "postgres"
259 ? withPgExecutor(
260 {
261 ...credentials,
262 // `pg` dials its stream itself; this one is already connected.
263 stream: () =>
264 Object.assign(socket, {
265 connect: () => {
266 process.nextTick(() => socket.emit("connect"));
267 return socket;
268 },
269 }),
270 ssl: false,
271 },
272 use,
273 )
274 : withMySqlExecutor({ ...credentials, stream: socket }, use);
275 });
276
277 /**
278 * Apply migrations to a database on a Cloud SQL instance. Call once the
279 * database exists.
280 */
281 export const runDatabaseMigrations = Effect.fn(function* (options: {
282 project: string;
283 instance: string;
284 database: string;
285 input: NormalizedMigrationsInput;
286 stamped: StampedMigrationsState;
287 transport: MigrationsTransport | undefined;
288 user: string | undefined;
289 password: string | Redacted.Redacted<string> | undefined;
290 labels: Record<string, string>;
291 }) {
292 const { project, instance, database } = options;
293 const live = yield* sqladmin
294 .getInstances({ project, instance })
295 .pipe(Effect.mapError(migrationError(`Reading Cloud SQL instance '${instance}'`)));
296 const dialect = yield* dialectOf(instance, live.databaseVersion);
297 if (options.user === undefined || options.password === undefined) {
298 return yield* new MigrationError({
299 message: `Migrations on '${instance}/${database}' need credentials; set migrationsUser and migrationsPassword`,
300 });
301 }
302 const user = options.user;
303 const password = Redacted.isRedacted(options.password)
304 ? Redacted.value(options.password)
305 : options.password;
306 const dataApi = live.settings?.dataApiAccess === "ALLOW_DATA_API";
307 const transport = options.transport ?? (dataApi ? "data-api" : "connector");
308
309 if (transport === "data-api") {
310 if (!dataApi) {
311 return yield* new MigrationError({
312 message: `migrationsTransport "data-api" needs dataApiAccess: true on Cloud SQL instance '${instance}'`,
313 });
314 }
315 const region = live.region;
316 if (!region) {
317 return yield* new MigrationError({
318 message: `Cloud SQL instance '${instance}' has no region`,
319 });
320 }
321 return yield* runMigrations({
322 input: options.input,
323 stamped: options.stamped,
324 withExecutor: (apply) =>
325 withStagedPassword(
326 {
327 project,
328 region,
329 secretId: stagedSecretId(instance, database),
330 password,
331 labels: options.labels,
332 },
333 (passwordSecretVersion) =>
334 makeDataApiExecutor({
335 dialect,
336 project,
337 instance,
338 database,
339 user,
340 passwordSecretVersion,
341 }).pipe(Effect.flatMap(probeExecutor), Effect.flatMap(apply)),
342 ),
343 });
344 }
345
346 const target = { dialect, project, instance, database, user, password };
347 return yield* runMigrations({
348 input: options.input,
349 stamped: options.stamped,
350 withExecutor: (apply) =>
351 // Logins fail while opening the session, so probe with a session of
352 // its own; the retry never reruns `apply`.
353 retryLogin(withConnectorExecutor(target, (executor) => executor.query("SELECT 1"))).pipe(
354 Effect.andThen(withConnectorExecutor(target, apply)),
355 ),
356 });
357 });