From 798b14f248aafce24f39c01825857f276354fd08 Mon Sep 17 00:00:00 2001 From: hmmhmmhm Date: Mon, 1 Jun 2026 10:54:16 +0900 Subject: [PATCH 1/3] feat(api): move production API to Cloudflare Add a Cloudflare Worker entrypoint backed by Supabase PostGIS so the API can serve the full region dataset without Railway. Route api.ground.codes to the Worker and update production smoke checks for the new runtime. --- apps/api-ground-codes/.env.example | 1 + apps/api-ground-codes/README.md | 37 +- apps/api-ground-codes/package.json | 5 +- .../scripts/import-postgis-regions.mjs | 197 ++++++++ apps/api-ground-codes/src/app.ts | 2 +- .../api-ground-codes/src/endpoints/healthz.ts | 36 +- .../src/endpoints/v1/region/around.ts | 49 +- .../src/endpoints/v1/region/info.ts | 26 +- .../src/endpoints/v1/region/load-region.ts | 13 +- .../src/postgis-region-store.ts | 436 ++++++++++++++++++ apps/api-ground-codes/src/worker.ts | 31 ++ .../supabase/001_ground_code_regions.sql | 51 ++ apps/api-ground-codes/wrangler.toml | 19 + packages/ground-codes/src/index.ts | 5 + packages/ground-codes/src/region.ts | 77 +++- .../ground-codes/test/region-store.test.ts | 73 +++ pnpm-lock.yaml | 122 +++++ scripts/production-smoke.mjs | 7 +- 18 files changed, 1148 insertions(+), 39 deletions(-) create mode 100644 apps/api-ground-codes/scripts/import-postgis-regions.mjs create mode 100644 apps/api-ground-codes/src/postgis-region-store.ts create mode 100644 apps/api-ground-codes/src/worker.ts create mode 100644 apps/api-ground-codes/supabase/001_ground_code_regions.sql create mode 100644 apps/api-ground-codes/wrangler.toml create mode 100644 packages/ground-codes/test/region-store.test.ts diff --git a/apps/api-ground-codes/.env.example b/apps/api-ground-codes/.env.example index af5ca60a..0e1bb362 100644 --- a/apps/api-ground-codes/.env.example +++ b/apps/api-ground-codes/.env.example @@ -1,3 +1,4 @@ PORT=3000 CORS_ALLOWED_ORIGINS=https://ground.codes API_RATE_LIMIT_PER_MINUTE=600 +SUPABASE_DB_URL=postgresql://postgres:password@db.example.supabase.co:5432/postgres diff --git a/apps/api-ground-codes/README.md b/apps/api-ground-codes/README.md index 801181eb..31a415f4 100644 --- a/apps/api-ground-codes/README.md +++ b/apps/api-ground-codes/README.md @@ -138,11 +138,44 @@ set does not silently drift back to stale npm artifacts. `pnpm runtime:update-pins` rewrites all three package pins together so Railway cannot end up with a mixed runtime. +### ☁️ Cloudflare Workers + Supabase PostGIS + +The API now has a Worker entrypoint at `src/worker.ts` and a Supabase/PostGIS +region store. `api.ground.codes/*` is routed to the `api-ground-codes` Worker, +so Cloudflare is the production serving layer while Supabase/PostGIS holds the +full generated region lookup data. + +Apply the Supabase schema and import a small dataset first: + +```bash +SUPABASE_DB_URL=postgresql://... \ +REGION_DATASETS=region-2,region-2-mongolian \ +REGION_IMPORT_MODE=replace \ +pnpm --filter api-ground-codes data:import-postgis +``` + +Omit `REGION_DATASETS` to import every generated `region-dist/*.json` dataset. +The default `REGION_IMPORT_MODE=missing` skips datasets whose full row count is +already present, which makes the full import resumable. + +Create the Cloudflare Hyperdrive binding against that database, then replace the +placeholder id in `wrangler.toml`: + +```bash +pnpm dlx wrangler hyperdrive create ground-codes-supabase \ + --connection-string "$SUPABASE_DB_URL" +``` + +At runtime the Worker reads `env.HYPERDRIVE.connectionString` and installs the +PostGIS-backed region store before handling requests. The Bun entrypoint +continues to use the existing local file/LevelDB data path unless a store is +installed explicitly. + ### 🛰️ Production Monitoring The `Production Smoke` workflow runs every 30 minutes and after the web deploy -workflow completes. It checks the Railway API, the Cloudflare Pages web app, and -API route metrics. It records per-check response times for `/readyz`, +workflow completes. It checks the Cloudflare Worker API, the Cloudflare Pages +web app, and API route metrics. It records per-check response times for `/readyz`, `/v1/encode`, `/v1/search`, `/metrics`, `robots.txt`, and `sitemap.xml`, and writes the timing table to the GitHub run summary. Add a `MOSHI_WEBHOOK_TOKEN` repository secret to send a webhook alert when the smoke workflow fails. diff --git a/apps/api-ground-codes/package.json b/apps/api-ground-codes/package.json index d527d60c..63306b5d 100644 --- a/apps/api-ground-codes/package.json +++ b/apps/api-ground-codes/package.json @@ -8,6 +8,7 @@ "build": "node scripts/build.mjs", "start": "bun run src/index.ts", "check-types": "tsc --noEmit", + "data:import-postgis": "node scripts/import-postgis-regions.mjs", "test": "bun test src/*.test.ts" }, "dependencies": { @@ -18,9 +19,11 @@ "@repo/codebook": "workspace:*", "@sinclair/typebox": "^0.34.30", "elysia": "latest", - "ground-codes": "workspace:*" + "ground-codes": "workspace:*", + "pg": "^8.21.0" }, "devDependencies": { + "@types/pg": "^8.20.0", "bun-types": "latest" }, "module": "src/index.js" diff --git a/apps/api-ground-codes/scripts/import-postgis-regions.mjs b/apps/api-ground-codes/scripts/import-postgis-regions.mjs new file mode 100644 index 00000000..8699830e --- /dev/null +++ b/apps/api-ground-codes/scripts/import-postgis-regions.mjs @@ -0,0 +1,197 @@ +import { readdir, readFile } from "node:fs/promises"; +import { dirname, resolve } from "node:path"; +import { fileURLToPath } from "node:url"; +import { Client } from "pg"; + +const here = dirname(fileURLToPath(import.meta.url)); +const repoRoot = resolve(here, "../../.."); +const defaultRegionDist = resolve(repoRoot, "packages/geoint/region-dist"); +const schemaPath = resolve(here, "../supabase/001_ground_code_regions.sql"); + +const connectionString = + process.env.SUPABASE_DB_URL ?? process.env.DATABASE_URL; +if (!connectionString) { + console.error( + "Set SUPABASE_DB_URL or DATABASE_URL before importing regions.", + ); + process.exit(1); +} + +const batchSize = Number(process.env.REGION_IMPORT_BATCH_SIZE ?? 1000); +const regionDist = process.env.REGION_DIST_DIR ?? defaultRegionDist; +const importMode = process.env.REGION_IMPORT_MODE ?? "missing"; + +const normalizeLookupKey = (value) => + value + .replace(/Æ/g, "Ae") + .replace(/æ/g, "ae") + .replace(/Œ/g, "Oe") + .replace(/œ/g, "oe") + .replace(/Ø/g, "O") + .replace(/ø/g, "o") + .replace(/Ð/g, "D") + .replace(/ð/g, "d") + .replace(/Þ/g, "Th") + .replace(/þ/g, "th") + .replace(/ß/g, "ss") + .normalize("NFKD") + .replace(/[\u0300-\u036f]/g, "") + .toLowerCase() + .trim(); + +const parseDatasetName = (datasetName) => { + const parts = datasetName.split("-"); + if (parts[0] !== "region" || !parts[1]) { + throw new Error(`Unsupported dataset name: ${datasetName}`); + } + + const regionLevel = Number(parts[1]); + let body = "earth"; + let language = "english"; + const rest = parts.slice(2); + + if (rest[0] === "moon" || rest[0] === "mars") { + body = rest[0]; + language = rest.slice(1).join("_") || "english"; + } else { + language = rest.join("_") || "english"; + } + + return { body, regionLevel, language }; +}; + +const chunk = (items, size) => { + const chunks = []; + for (let index = 0; index < items.length; index += size) { + chunks.push(items.slice(index, index + size)); + } + return chunks; +}; + +const getDatasets = async () => { + const explicitDatasets = process.env.REGION_DATASETS; + if (explicitDatasets) { + return explicitDatasets + .split(",") + .map((value) => value.trim()) + .filter(Boolean); + } + + return (await readdir(regionDist)) + .filter((fileName) => fileName.endsWith(".json")) + .map((fileName) => fileName.replace(/\.json$/, "")) + .sort(); +}; + +const importDataset = async (client, datasetName) => { + const { body, regionLevel, language } = parseDatasetName(datasetName); + const datasetPath = resolve(regionDist, `${datasetName}.json`); + const rows = JSON.parse(await readFile(datasetPath, "utf8")); + console.log( + `Importing ${datasetName}: ${rows.length} rows (${body}/${regionLevel}/${language})`, + ); + + if (importMode === "missing") { + const existing = await client.query( + "select count(*)::int as rows from ground_code_regions where dataset_name = $1", + [datasetName], + ); + if (existing.rows[0]?.rows === rows.length) { + console.log( + `Skipping ${datasetName}: ${rows.length} rows already present`, + ); + return; + } + } + + await client.query("begin"); + try { + await client.query( + "delete from ground_code_regions where dataset_name = $1", + [datasetName], + ); + + let imported = 0; + for (const batch of chunk( + rows.map((row, index) => ({ row, sourceIndex: index })), + batchSize, + )) { + const values = []; + const placeholders = batch.map(({ row, sourceIndex }, index) => { + const offset = index * 16; + values.push( + datasetName, + sourceIndex, + body, + regionLevel, + language, + row.code, + row.name, + normalizeLookupKey(row.code), + normalizeLookupKey(row.name), + row.lat, + row.long, + row.population ?? null, + row.countryCode ?? null, + row.featureType ?? null, + row.diameterKm ?? null, + row.source ?? null, + ); + + return `(${Array.from({ length: 16 }, (_, valueIndex) => `$${offset + valueIndex + 1}`).join(", ")}, ST_SetSRID(ST_MakePoint($${offset + 11}, $${offset + 10}), 4326))`; + }); + + await client.query( + ` + insert into ground_code_regions ( + dataset_name, + source_index, + body, + region_level, + language, + code, + name, + search_code, + search_name, + lat, + lng, + population, + country_code, + feature_type, + diameter_km, + source, + geom + ) + values ${placeholders.join(", ")} + `, + values, + ); + imported += batch.length; + if (imported % (batchSize * 10) === 0 || imported === rows.length) { + console.log(` ${datasetName}: ${imported}/${rows.length}`); + } + } + + await client.query("commit"); + } catch (error) { + await client.query("rollback"); + throw error; + } +}; + +const client = new Client({ + connectionString, + ssl: + process.env.PGSSLMODE === "require" + ? { rejectUnauthorized: process.env.PGSSLREJECTUNAUTHORIZED !== "0" } + : undefined, +}); +await client.connect(); +try { + await client.query(await readFile(schemaPath, "utf8")); + for (const datasetName of await getDatasets()) { + await importDataset(client, datasetName); + } +} finally { + await client.end(); +} diff --git a/apps/api-ground-codes/src/app.ts b/apps/api-ground-codes/src/app.ts index 2dfd77c7..28718a24 100644 --- a/apps/api-ground-codes/src/app.ts +++ b/apps/api-ground-codes/src/app.ts @@ -35,7 +35,7 @@ export const createApp = (portOrOptions?: string | number | AppOptions) => { const rateLimit = "rateLimit" in options ? options.rateLimit : getDefaultRateLimit(); - const app = new Elysia() + const app = new Elysia({ aot: false }) .onError(({ error, code, set }) => formatApiError(error, code, set)) .use(createCorsEndpoint(options.corsOrigins)) .use(createRateLimitEndpoint(rateLimit)) diff --git a/apps/api-ground-codes/src/endpoints/healthz.ts b/apps/api-ground-codes/src/endpoints/healthz.ts index cacee391..dc87a1af 100644 --- a/apps/api-ground-codes/src/endpoints/healthz.ts +++ b/apps/api-ground-codes/src/endpoints/healthz.ts @@ -3,13 +3,31 @@ import { dirname, join } from "node:path"; import { fileURLToPath } from "node:url"; import Elysia, { t } from "elysia"; -const endpointDir = dirname(fileURLToPath(import.meta.url)); -const packagePath = join(endpointDir, "../../package.json"); -const packageJson = JSON.parse(readFileSync(packagePath, "utf8")) as { +const fallbackPackageJson = { + version: "1.0.79", + dependencies: {}, +} as { version: string; dependencies: Record; }; +const getEndpointDir = () => { + try { + return typeof import.meta.url === "string" + ? dirname(fileURLToPath(import.meta.url)) + : null; + } catch { + return null; + } +}; + +const endpointDir = getEndpointDir(); +const packageJson = endpointDir + ? (JSON.parse( + readFileSync(join(endpointDir, "../../package.json"), "utf8"), + ) as typeof fallbackPackageJson) + : fallbackPackageJson; + const findUp = (fileName: string, startDir: string) => { let currentDir = startDir; while (true) { @@ -30,10 +48,10 @@ const runtimeTag = process.env.API_RUNTIME_TAG ?? runtimeDependency.match(/#([^&]+)&path:/)?.[1] ?? (runtimeDependency.startsWith("workspace:") ? "workspace" : "unknown"); -const lockfilePath = findUp("pnpm-lock.yaml", endpointDir); +const lockfilePath = endpointDir ? findUp("pnpm-lock.yaml", endpointDir) : null; const lockfileCommit = lockfilePath ? readFileSync(lockfilePath, "utf8").match( - /ground\.codes\/tar\.gz\/([0-9a-f]{40})#path:packages\/ground-codes/, + /ground\.codes\/tar\.gz\/([0-9a-f]{40})#path:packages\/ground-codes/, )?.[1] : undefined; const runtimeCommit = @@ -57,7 +75,7 @@ export const healthz = new Elysia().get( description: "Health check response", example: "OK", }), - } + }, ); export const readyz = new Elysia().get( @@ -90,11 +108,11 @@ export const readyz = new Elysia().get( example: "1.0.68", }), runtimeTag: t.String({ - example: "railway-api-runtime-20260522-hindi-v3", + example: "unknown", }), runtimeCommit: t.String({ - example: "f173ae362a9134d954d8b867c66058c9dcb7a754", + example: "0000000000000000000000000000000000000000", }), }), - } + }, ); diff --git a/apps/api-ground-codes/src/endpoints/v1/region/around.ts b/apps/api-ground-codes/src/endpoints/v1/region/around.ts index 9ea62e7f..7c321cab 100644 --- a/apps/api-ground-codes/src/endpoints/v1/region/around.ts +++ b/apps/api-ground-codes/src/endpoints/v1/region/around.ts @@ -1,5 +1,5 @@ import Elysia, { t } from "elysia"; -import { around } from "@ground-codes/geoint/src/index.ts"; +import { getRegionStore } from "ground-codes/src/index.ts"; import { getRegionDatasetName, supportedLanguages } from "../language.js"; import { validateBody, @@ -36,21 +36,46 @@ export const v1RegionAround = new Elysia().post( }); await loadRegionDataset(regionName); - return ( - await around({ - regionName, - lat, - lng, - maxResults, - maxDistance, - }) - )?.map((region) => ({ + const regionStore = getRegionStore(); + const regions = regionStore + ? regionStore.findRegionsAround + ? await regionStore.findRegionsAround( + { lat, lng }, + { + body: validatedBody, + language: validatedLanguage, + regionLevel, + maxResults, + maxDistance, + }, + ) + : [ + await regionStore.findClosestRegion( + { lat, lng }, + { + body: validatedBody, + language: validatedLanguage, + regionLevel, + }, + ), + ].filter((region) => region !== null) + : await import("@ground-codes/geoint/src/index.ts").then(({ around }) => + around({ + regionName, + lat, + lng, + maxResults, + maxDistance, + }), + ); + + return regions?.slice(0, maxResults).map((region) => ({ name: region.name, code: region.code, lat: region.lat, - lng: region.long, + lng: "lng" in region ? region.lng : region.long, population: region.population, - countryCode: region.countryCode, + countryCode: "countryCode" in region ? region.countryCode : undefined, })); }, { diff --git a/apps/api-ground-codes/src/endpoints/v1/region/info.ts b/apps/api-ground-codes/src/endpoints/v1/region/info.ts index 1c7c45b2..7957e334 100644 --- a/apps/api-ground-codes/src/endpoints/v1/region/info.ts +++ b/apps/api-ground-codes/src/endpoints/v1/region/info.ts @@ -1,5 +1,5 @@ import Elysia, { t } from "elysia"; -import { info } from "@ground-codes/geoint/src/index.ts"; +import { getRegionStore } from "ground-codes/src/index.ts"; import { ApiNotFoundError } from "../api-error.js"; import { getRegionDatasetName, supportedLanguages } from "../language.js"; import { @@ -27,10 +27,22 @@ export const v1RegionInfo = new Elysia().post( }); await loadRegionDataset(regionName); - const data = await info({ - name: query, - regionName, - }).catch(() => null); + const regionStore = getRegionStore(); + const data = await (regionStore + ? regionStore + .findRegionsByQuery(query, { + body: validatedBody, + language: validatedLanguage, + regionLevel, + maxResults: 1, + }) + .then((matches) => matches[0] ?? null) + : import("@ground-codes/geoint/src/index.ts").then(({ info }) => + info({ + name: query, + regionName, + }).catch(() => null), + )); if (!data) { throw new ApiNotFoundError(`Region "${query}" was not found.`); @@ -40,9 +52,9 @@ export const v1RegionInfo = new Elysia().post( name: data.name, code: data.code, lat: data.lat, - lng: data.long, + lng: "lng" in data ? data.lng : data.long, population: data.population, - countryCode: data.countryCode, + countryCode: "countryCode" in data ? data.countryCode : undefined, }; }, { diff --git a/apps/api-ground-codes/src/endpoints/v1/region/load-region.ts b/apps/api-ground-codes/src/endpoints/v1/region/load-region.ts index 5beff097..9183d86c 100644 --- a/apps/api-ground-codes/src/endpoints/v1/region/load-region.ts +++ b/apps/api-ground-codes/src/endpoints/v1/region/load-region.ts @@ -1,4 +1,4 @@ -import { load } from "@ground-codes/geoint/src/index.ts"; +import { getRegionStore } from "ground-codes/src/index.ts"; const pendingLoads = new Map>(); const regionLoadMetrics = { @@ -40,10 +40,17 @@ export const loadRegionDataset = async (regionName: string) => { regionLoadMetrics.started += 1; regionMetrics.started += 1; - pendingLoad = load([regionName]) + pendingLoad = ( + getRegionStore() + ? Promise.resolve() + : import("@ground-codes/geoint/src/index.ts").then(({ load }) => + load([regionName]), + ) + ) .then(() => { const loadedAt = new Date().toISOString(); - const durationMs = Math.round((performance.now() - startedAt) * 100) / 100; + const durationMs = + Math.round((performance.now() - startedAt) * 100) / 100; regionLoadMetrics.completed += 1; regionLoadMetrics.lastLoadedAt = loadedAt; diff --git a/apps/api-ground-codes/src/postgis-region-store.ts b/apps/api-ground-codes/src/postgis-region-store.ts new file mode 100644 index 00000000..9d49f40b --- /dev/null +++ b/apps/api-ground-codes/src/postgis-region-store.ts @@ -0,0 +1,436 @@ +import { Client, type ClientConfig } from "pg"; +import { + type CelestialBody, + type RegionSearchResult, + type RegionStore, + type SupportedLanguage, + getBodyMetersPerDegree, + normalizeLongitudeForBody, + setRegionStore, +} from "ground-codes/src/index.ts"; + +type QueryableClient = { + query>( + text: string, + values?: unknown[], + ): Promise<{ rows: T[] }>; + connect(): Promise; + end(): Promise; +}; + +type RegionRow = { + source_index: number; + name: string; + code: string; + lat: number | string; + lng: number | string; + body: CelestialBody; + region_level: number; + population: number | string | null; + distance_km?: number | string | null; +}; + +const DEFAULT_REGION_2_FALLBACK_DISTANCE_KM = 100; +const DEFAULT_MARS_REGION_2_FALLBACK_DISTANCE_KM = 100; + +const normalizeLookupKey = (value: string) => + value + .replace(/Æ/g, "Ae") + .replace(/æ/g, "ae") + .replace(/Œ/g, "Oe") + .replace(/œ/g, "oe") + .replace(/Ø/g, "O") + .replace(/ø/g, "o") + .replace(/Ð/g, "D") + .replace(/ð/g, "d") + .replace(/Þ/g, "Th") + .replace(/þ/g, "th") + .replace(/ß/g, "ss") + .normalize("NFKD") + .replace(/[\u0300-\u036f]/g, "") + .toLowerCase() + .trim(); + +const toRegionSearchResult = (row: RegionRow): RegionSearchResult => ({ + name: row.name, + code: row.code, + lat: Number(row.lat), + lng: Number(row.lng), + body: row.body, + regionLevel: row.region_level, + population: row.population === null ? undefined : Number(row.population), + distanceKm: + row.distance_km === null || row.distance_km === undefined + ? undefined + : Number(row.distance_km), +}); + +const toRadians = (degrees: number) => degrees * (Math.PI / 180); + +const calculateDistanceKm = ( + lat1: number, + lng1: number, + lat2: number, + lng2: number, + body: CelestialBody, +) => { + const radiusKm = getBodyMetersPerDegree(body) / 1000 / (Math.PI / 180); + const normalizedLng1 = normalizeLongitudeForBody(lng1, body); + const normalizedLng2 = normalizeLongitudeForBody(lng2, body); + const dLat = toRadians(lat2 - lat1); + const dLng = toRadians(normalizedLng2 - normalizedLng1); + const a = + Math.sin(dLat / 2) * Math.sin(dLat / 2) + + Math.cos(toRadians(lat1)) * + Math.cos(toRadians(lat2)) * + Math.sin(dLng / 2) * + Math.sin(dLng / 2); + + return 2 * radiusKm * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a)); +}; + +const getFallbackSearchLevels = (body: CelestialBody, regionLevel: number) => { + if (regionLevel !== 2) return []; + if (body === "mars") return [3]; + if (body === "earth") return [1, 3]; + return []; +}; + +const getDatasetName = ( + body: CelestialBody, + regionLevel: number, + language: SupportedLanguage | "english", +) => { + const bodyPart = body === "earth" ? "" : `-${body}`; + const languagePart = language === "english" ? "" : `-${language}`; + return `region-${regionLevel}${bodyPart}${languagePart}`; +}; + +export interface PostgisRegionStoreOptions { + connectionString: string; + ssl?: ClientConfig["ssl"]; + clientFactory?: (connectionString: string) => QueryableClient; +} + +export class PostgisRegionStore implements RegionStore { + #connectionString: string; + #clientFactory: (connectionString: string) => QueryableClient; + + constructor({ + connectionString, + ssl, + clientFactory = (connectionString) => new Client({ connectionString, ssl }), + }: PostgisRegionStoreOptions) { + this.#connectionString = connectionString; + this.#clientFactory = clientFactory; + } + + async #withClient( + callback: (client: QueryableClient) => Promise, + ): Promise { + const client = this.#clientFactory(this.#connectionString); + await client.connect(); + try { + return await callback(client); + } finally { + await client.end(); + } + } + + async findClosestRegion( + target: { lat: number; lng: number }, + options?: { + regionLevel?: number; + language?: SupportedLanguage; + region2FallbackDistanceKm?: number; + body?: CelestialBody; + }, + ) { + const body = options?.body ?? "earth"; + const regionLevel = options?.regionLevel ?? (body === "earth" ? 1 : 2); + const language = options?.language ?? "english"; + const region2FallbackDistanceKm = + options?.region2FallbackDistanceKm ?? + DEFAULT_REGION_2_FALLBACK_DISTANCE_KM; + + return await this.#withClient(async (client) => { + const findClosestAtLevel = async ( + candidateRegionLevel: number, + ): Promise => { + const candidateLanguage = + candidateRegionLevel === 1 ? "english" : language; + const baseDatasetName = getDatasetName( + body, + candidateRegionLevel, + "english", + ); + const localizedDatasetName = getDatasetName( + body, + candidateRegionLevel, + candidateLanguage, + ); + const { rows } = await client.query( + ` + select + source_index, + name, + code, + lat, + lng, + body, + region_level, + population + from ground_code_regions + where dataset_name = $3 + order by geom <-> ST_SetSRID(ST_MakePoint($2, $1), 4326) + limit 1 + `, + [target.lat, target.lng, baseDatasetName], + ); + + const baseRow = rows[0]; + if (!baseRow) return null; + + let selectedRow = baseRow; + if (localizedDatasetName !== baseDatasetName) { + const localized = await client.query( + ` + select + source_index, + name, + code, + lat, + lng, + body, + region_level, + population + from ground_code_regions + where dataset_name = $1 + and code = $2 + limit 1 + `, + [localizedDatasetName, baseRow.code], + ); + selectedRow = localized.rows[0] ?? baseRow; + } + + const region = toRegionSearchResult(selectedRow); + return { + ...region, + distanceKm: calculateDistanceKm( + target.lat, + target.lng, + Number(baseRow.lat), + Number(baseRow.lng), + body, + ), + }; + }; + + const closestRegion = await findClosestAtLevel(regionLevel); + if (!closestRegion) return null; + + if ( + regionLevel !== 2 || + closestRegion.distanceKm === undefined || + (body === "earth" && + closestRegion.distanceKm <= region2FallbackDistanceKm) || + (body === "mars" && + closestRegion.distanceKm <= + DEFAULT_MARS_REGION_2_FALLBACK_DISTANCE_KM) + ) { + return closestRegion; + } + + const fallbackCandidates = ( + await Promise.all( + getFallbackSearchLevels(body, regionLevel).map(findClosestAtLevel), + ) + ).filter((region): region is RegionSearchResult => Boolean(region)); + + return [closestRegion, ...fallbackCandidates].reduce((best, region) => + (region.distanceKm ?? Infinity) < (best.distanceKm ?? Infinity) + ? region + : best, + ); + }); + } + + async findRegionsAround( + target: { lat: number; lng: number }, + options?: { + regionLevel?: number; + language?: SupportedLanguage; + body?: CelestialBody; + maxResults?: number; + maxDistance?: number; + }, + ) { + const body = options?.body ?? "earth"; + const regionLevel = options?.regionLevel ?? 2; + const language = options?.language ?? "english"; + const maxResults = options?.maxResults ?? 5; + const queryLimit = options?.maxDistance + ? Math.max(maxResults * 20, 100) + : maxResults; + + return await this.#withClient(async (client) => { + const { rows } = await client.query( + ` + select + source_index, + name, + code, + lat, + lng, + body, + region_level, + population + from ground_code_regions + where body = $3 + and region_level = $4 + and language = $5 + order by geom <-> ST_SetSRID(ST_MakePoint($2, $1), 4326) + limit $6 + `, + [target.lat, target.lng, body, regionLevel, language, queryLimit], + ); + + return rows + .map((row) => { + const region = toRegionSearchResult(row); + return { + ...region, + distanceKm: calculateDistanceKm( + target.lat, + target.lng, + region.lat, + region.lng, + body, + ), + }; + }) + .filter( + (region) => + options?.maxDistance === undefined || + (region.distanceKm ?? Infinity) <= options.maxDistance, + ) + .slice(0, maxResults); + }); + } + + async findRegionsByQuery( + codeOrName: string, + options?: { + regionLevel?: number; + language?: SupportedLanguage; + body?: CelestialBody; + maxResults?: number; + biasLat?: number; + biasLng?: number; + }, + ) { + const query = normalizeLookupKey(codeOrName); + if (!query) return []; + + const body = options?.body ?? "earth"; + const regionLevel = options?.regionLevel ?? 2; + const language = options?.language ?? "english"; + const maxResults = options?.maxResults ?? 5; + const hasBias = + Number.isFinite(options?.biasLat) && Number.isFinite(options?.biasLng); + + return await this.#withClient(async (client) => { + const seen = new Set(); + const results: RegionSearchResult[] = []; + + const addMatches = async (candidateRegionLevel: number) => { + const candidateLanguage = + candidateRegionLevel === 1 ? "english" : language; + const { rows } = await client.query( + ` + select + source_index, + name, + code, + lat, + lng, + body, + region_level, + population, + ${ + hasBias + ? "ST_DistanceSphere(geom, ST_SetSRID(ST_MakePoint($7, $6), 4326)) / 1000" + : "null" + } as distance_km + from ground_code_regions + where body = $1 + and region_level = $2 + and language = $3 + and ( + search_code = $4 + or search_name = $4 + or search_code like $5 + or search_name like $5 + ) + order by + ${hasBias ? "distance_km asc nulls last," : ""} + case + when search_code = $4 or search_name = $4 then 0 + when search_name like $4 || '%' then 1 + when search_code like $4 || '%' then 2 + else 3 + end, + population desc nulls last, + length(name) asc + limit $${hasBias ? 8 : 6} + `, + hasBias + ? [ + body, + candidateRegionLevel, + candidateLanguage, + query, + `%${query}%`, + options?.biasLat, + options?.biasLng, + maxResults, + ] + : [ + body, + candidateRegionLevel, + candidateLanguage, + query, + `%${query}%`, + maxResults, + ], + ); + + for (const row of rows) { + const region = toRegionSearchResult(row); + const key = `${region.body}:${region.regionLevel}:${region.code}:${region.name}`; + if (seen.has(key)) continue; + seen.add(key); + results.push(region); + if (results.length >= maxResults) return; + } + }; + + await addMatches(regionLevel); + if (results.length >= maxResults) return results; + + for (const fallbackLevel of getFallbackSearchLevels(body, regionLevel)) { + await addMatches(fallbackLevel); + if (results.length >= maxResults) return results; + } + + return results; + }); + } +} + +export const installPostgisRegionStore = (connectionString: string) => { + const store = new PostgisRegionStore({ connectionString }); + setRegionStore(store); + return store; +}; diff --git a/apps/api-ground-codes/src/worker.ts b/apps/api-ground-codes/src/worker.ts new file mode 100644 index 00000000..083c1da4 --- /dev/null +++ b/apps/api-ground-codes/src/worker.ts @@ -0,0 +1,31 @@ +import { createApp } from "./app.js"; +import { installPostgisRegionStore } from "./postgis-region-store.js"; + +export interface Env { + HYPERDRIVE?: { + connectionString: string; + }; + SUPABASE_DB_URL?: string; + CORS_ORIGINS?: string; +} + +const app = createApp(); +let installedConnectionString: string | null = null; + +const installRegionStoreFromEnv = (env: Env) => { + const connectionString = + env.HYPERDRIVE?.connectionString ?? env.SUPABASE_DB_URL; + if (!connectionString || connectionString === installedConnectionString) { + return; + } + + installPostgisRegionStore(connectionString); + installedConnectionString = connectionString; +}; + +export default { + async fetch(request: Request, env: Env): Promise { + installRegionStoreFromEnv(env); + return await app.handle(request); + }, +}; diff --git a/apps/api-ground-codes/supabase/001_ground_code_regions.sql b/apps/api-ground-codes/supabase/001_ground_code_regions.sql new file mode 100644 index 00000000..4dd4c0c8 --- /dev/null +++ b/apps/api-ground-codes/supabase/001_ground_code_regions.sql @@ -0,0 +1,51 @@ +create extension if not exists postgis; +create extension if not exists pg_trgm; + +create table if not exists ground_code_regions ( + dataset_name text not null, + source_index integer not null, + body text not null check (body in ('earth', 'moon', 'mars')), + region_level integer not null, + language text not null, + code text not null, + name text not null, + search_code text not null, + search_name text not null, + lat double precision not null, + lng double precision not null, + population bigint, + country_code text, + feature_type text, + diameter_km double precision, + source text, + geom geometry(Point, 4326) not null, + updated_at timestamptz not null default now(), + primary key (dataset_name, source_index) +); + +create index if not exists ground_code_regions_lookup_idx + on ground_code_regions (body, region_level, language); + +create index if not exists ground_code_regions_dataset_code_idx + on ground_code_regions (dataset_name, code); + +create index if not exists ground_code_regions_geom_idx + on ground_code_regions using gist (geom); + +create index if not exists ground_code_regions_region_1_geom_idx + on ground_code_regions using gist (geom) + where dataset_name = 'region-1'; + +create index if not exists ground_code_regions_region_2_geom_idx + on ground_code_regions using gist (geom) + where dataset_name = 'region-2'; + +create index if not exists ground_code_regions_region_3_geom_idx + on ground_code_regions using gist (geom) + where dataset_name = 'region-3'; + +create index if not exists ground_code_regions_search_code_trgm_idx + on ground_code_regions using gin (search_code gin_trgm_ops); + +create index if not exists ground_code_regions_search_name_trgm_idx + on ground_code_regions using gin (search_name gin_trgm_ops); diff --git a/apps/api-ground-codes/wrangler.toml b/apps/api-ground-codes/wrangler.toml new file mode 100644 index 00000000..650a3bcc --- /dev/null +++ b/apps/api-ground-codes/wrangler.toml @@ -0,0 +1,19 @@ +name = "api-ground-codes" +account_id = "6353b3b978a6ebc8ee4b2953509da274" +main = "src/worker.ts" +compatibility_date = "2026-05-30" +compatibility_flags = ["nodejs_compat"] +workers_dev = true +routes = [ + { pattern = "api.ground.codes/*", zone_name = "ground.codes" }, +] + +[observability] +enabled = true + +# Create the Hyperdrive binding with: +# pnpm --filter api-ground-codes exec wrangler hyperdrive create ground-codes-supabase --connection-string "$SUPABASE_DB_URL" +# Then replace the placeholder id below. +[[hyperdrive]] +binding = "HYPERDRIVE" +id = "bcd8dcf99beb470b9d4e3f1e0259878e" diff --git a/packages/ground-codes/src/index.ts b/packages/ground-codes/src/index.ts index 32e4bb67..e7fb0b4a 100644 --- a/packages/ground-codes/src/index.ts +++ b/packages/ground-codes/src/index.ts @@ -2,6 +2,8 @@ import { findClosestRegion, findRegionByCodeOrName, findRegionsByQuery, + getRegionStore, + setRegionStore, } from "./region.js"; import { CelestialBody, @@ -274,6 +276,9 @@ export { reconstructCoordinateDiff, getBodyMetersPerDegree, normalizeLongitudeForBody, + getRegionStore, + setRegionStore, }; export type { CelestialBody, SupportedLanguage }; +export type { RegionSearchResult, RegionStore } from "./region.js"; diff --git a/packages/ground-codes/src/region.ts b/packages/ground-codes/src/region.ts index 7669e113..d825e305 100644 --- a/packages/ground-codes/src/region.ts +++ b/packages/ground-codes/src/region.ts @@ -19,8 +19,71 @@ export interface Region { population?: number; } -const requireRegionJson = createRequire(import.meta.url); -const loadRegionData = (path: string) => requireRegionJson(path) as Region[]; +export interface RegionSearchResult { + lat: number; + lng: number; + regionLevel?: number; + body?: CelestialBody; + name: string; + code: string; + distanceKm?: number; + population?: number; +} + +export interface RegionStore { + findRegionsAround?( + target: { lat: number; lng: number }, + options?: { + regionLevel?: number; + language?: SupportedLanguage; + body?: CelestialBody; + maxResults?: number; + maxDistance?: number; + }, + ): Promise; + findClosestRegion( + target: { lat: number; lng: number }, + options?: { + regionLevel?: number; + language?: SupportedLanguage; + region2FallbackDistanceKm?: number; + body?: CelestialBody; + }, + ): Promise; + findRegionsByQuery( + codeOrName: string, + options?: { + regionLevel?: number; + language?: SupportedLanguage; + body?: CelestialBody; + maxResults?: number; + biasLat?: number; + biasLng?: number; + }, + ): Promise; +} + +let configuredRegionStore: RegionStore | null = null; + +export const setRegionStore = (store: RegionStore | null) => { + configuredRegionStore = store; +}; + +export const getRegionStore = () => configuredRegionStore; + +let requireRegionJson: ReturnType | null = null; +const loadRegionData = (path: string) => { + if (!requireRegionJson) { + if (typeof import.meta.url !== "string") { + throw new Error( + "Local region JSON loading is unavailable in this runtime", + ); + } + requireRegionJson = createRequire(import.meta.url); + } + + return requireRegionJson(path) as Region[]; +}; const DEFAULT_REGION_2_FALLBACK_DISTANCE_KM = 100; const DEFAULT_MARS_REGION_2_FALLBACK_DISTANCE_KM = 100; @@ -1342,6 +1405,11 @@ export const findClosestRegion = async ( body?: CelestialBody; }, ) => { + const regionStore = getRegionStore(); + if (regionStore) { + return await regionStore.findClosestRegion({ lat, lng }, options); + } + const body = options?.body ?? "earth"; const regionLevel = options?.regionLevel ?? (body === "earth" ? 1 : 2); const language = options?.language; @@ -1465,6 +1533,11 @@ export const findRegionsByQuery = async ( return []; } + const regionStore = getRegionStore(); + if (regionStore) { + return await regionStore.findRegionsByQuery(codeOrName, options); + } + try { const { regionLevel = 2, diff --git a/packages/ground-codes/test/region-store.test.ts b/packages/ground-codes/test/region-store.test.ts new file mode 100644 index 00000000..976ebb2f --- /dev/null +++ b/packages/ground-codes/test/region-store.test.ts @@ -0,0 +1,73 @@ +import { afterEach, describe, it } from "node:test"; +import assert from "node:assert/strict"; +import { + decode, + encode, + findRegionsByQuery, + setRegionStore, + type RegionStore, +} from "../src/index.js"; + +const seoulStore: RegionStore = { + async findClosestRegion() { + return { + name: "Seoul", + code: "KR-SEL", + lat: 37.566, + lng: 126.978, + body: "earth", + regionLevel: 2, + }; + }, + async findRegionsByQuery(query, options) { + if (query !== "Seoul") return []; + return [ + { + name: "Seoul", + code: "KR-SEL", + lat: 37.566, + lng: 126.978, + body: options?.body ?? "earth", + regionLevel: options?.regionLevel ?? 2, + }, + ]; + }, +}; + +describe("region store injection", () => { + afterEach(() => { + setRegionStore(null); + }); + + it("uses the configured store when encoding and decoding region-backed codes", async () => { + setRegionStore(seoulStore); + + const encoded = await encode( + { lat: 37.5661, lng: 126.9781 }, + { language: "english", regionLevel: 2 }, + ); + assert.match(encoded, /^Seoul-/); + + const decoded = await decode(encoded, { + language: "english", + regionLevel: 2, + }); + assert.ok(Math.abs(decoded.lat - 37.5661) < 0.001); + assert.ok(Math.abs(decoded.lng - 126.9781) < 0.001); + }); + + it("routes direct region search through the configured store", async () => { + setRegionStore(seoulStore); + + assert.deepEqual(await findRegionsByQuery("Seoul", { regionLevel: 2 }), [ + { + name: "Seoul", + code: "KR-SEL", + lat: 37.566, + lng: 126.978, + body: "earth", + regionLevel: 2, + }, + ]); + }); +}); diff --git a/pnpm-lock.yaml b/pnpm-lock.yaml index e40c1653..7519a4d2 100644 --- a/pnpm-lock.yaml +++ b/pnpm-lock.yaml @@ -50,7 +50,13 @@ importers: ground-codes: specifier: workspace:* version: link:../../packages/ground-codes + pg: + specifier: ^8.21.0 + version: 8.21.0 devDependencies: + '@types/pg': + specifier: ^8.20.0 + version: 8.20.0 bun-types: specifier: latest version: 1.2.5 @@ -2438,6 +2444,9 @@ packages: '@types/node@22.13.9': resolution: {integrity: sha512-acBjXdRJ3A6Pb3tqnw9HZmyR3Fiol3aGxRCK1x3d+6CDAMjl7I649wpSd+yNURCjbOUGu9tqtLKnTGxmK6CyGw==} + '@types/pg@8.20.0': + resolution: {integrity: sha512-bEPFOaMAHTEP1EzpvHTbmwR8UsFyHSKsRisLIHVMXnpNefSbGA1bD6CVy+qKjGSqmZqNqBDV2azOBo8TgkcVow==} + '@types/react-dom@19.0.4': resolution: {integrity: sha512-4fSQ8vWFkg+TGhePfUzVmat3eC14TXYSsiiDSLI0dVLsrm9gZFABjPy/Qu6TKgl1tq1Bu1yDsuQgY3A3DOjCcg==} peerDependencies: @@ -4318,6 +4327,40 @@ packages: pcre-to-regexp@1.1.0: resolution: {integrity: sha512-KF9XxmUQJ2DIlMj3TqNqY1AWvyvTuIuq11CuuekxyaYMiFuMKGgQrePYMX5bXKLhLG3sDI4CsGAYHPaT7VV7+g==} + pg-cloudflare@1.4.0: + resolution: {integrity: sha512-Vo7z/6rrQYxpNRylp4Tlob2elzbh+N/MOQbxFVWCxS7oEx6jF53GTJFxK2WWpKuBRkmiin4Mt+xofFDjx09R0A==} + + pg-connection-string@2.13.0: + resolution: {integrity: sha512-EMnU9E2fSULdsbErBbMaXJvFeD9B4+nPcM3f+4lsiCR0BHLPrLVjv3DbyM2hgQQviKJaTWIRRTjKjWlHg3p2ig==} + + pg-int8@1.0.1: + resolution: {integrity: sha512-WCtabS6t3c8SkpDBUlb1kjOs7l66xsGdKpIPZsg4wR+B3+u9UAum2odSsF9tnvxg80h4ZxLWMy4pRjOsFIqQpw==} + engines: {node: '>=4.0.0'} + + pg-pool@3.14.0: + resolution: {integrity: sha512-gKtPkFdQPU3DksooVLi9LsjZxrsBUZIpa+7aVx+LV5pNh0KzP4Zleud2po+ConrxbuXGBJ6Hfer6hdgpIBpBaw==} + peerDependencies: + pg: '>=8.0' + + pg-protocol@1.14.0: + resolution: {integrity: sha512-n5taZ1kO3s9ngDTVxsEznOqCyToTgz0FLuPq0B33COy5pPpuWJpY3/2oRBVETuOgzdqRXfWpM9HIhp2LBBT1BA==} + + pg-types@2.2.0: + resolution: {integrity: sha512-qTAAlrEsl8s4OiEQY69wDvcMIdQN6wdz5ojQiOy6YRMuynxenON0O5oCpJI6lshc6scgAY8qvJ2On/p+CXY0GA==} + engines: {node: '>=4'} + + pg@8.21.0: + resolution: {integrity: sha512-AUP1EYJuHraQGsVoCQVIcM7TEJVGtDzxWtGFZd8rds9d+CCXlU5Js1rYgfLNvxy9iJrpHjGrRjoi/3BT9fRyiA==} + engines: {node: '>= 16.0.0'} + peerDependencies: + pg-native: '>=3.0.1' + peerDependenciesMeta: + pg-native: + optional: true + + pgpass@1.0.5: + resolution: {integrity: sha512-FdW9r/jQZhSeohs1Z3sI1yxFQNFvMcnmfuj4WBMUTxOrAyLMaTcE1aAMBiTlbMNaXvBCQuVi0R7hd8udDSP7ug==} + picocolors@1.1.1: resolution: {integrity: sha512-xceH2snhtb5M9liqDsmEw56le376mTZkEX/jEb/RxNFyegNul7eNslCXP9FDj/Lcu0X8KEyMceP2ntpaHrDEVA==} @@ -4397,6 +4440,22 @@ packages: resolution: {integrity: sha512-dle9A3yYxlBSrt8Fu+IpjGT8SY8hN0mlaA6GY8t0P5PjIOZemULz/E2Bnm/2dcUOena75OTNkHI76uZBNUUq3A==} engines: {node: ^10 || ^12 || >=14} + postgres-array@2.0.0: + resolution: {integrity: sha512-VpZrUqU5A69eQyW2c5CA1jtLecCsN2U/bD6VilrFDWq5+5UIEVO7nazS3TEcHf1zuPYO/sqGvUvW62g86RXZuA==} + engines: {node: '>=4'} + + postgres-bytea@1.0.1: + resolution: {integrity: sha512-5+5HqXnsZPE65IJZSMkZtURARZelel2oXUEO8rH83VS/hxH5vv1uHquPg5wZs8yMAfdv971IU+kcPUczi7NVBQ==} + engines: {node: '>=0.10.0'} + + postgres-date@1.0.7: + resolution: {integrity: sha512-suDmjLVQg78nMK2UZ454hAG+OAW+HQPZ6n++TNDUX+L0+uUlLywnoxJKDou51Zm+zTCjrCl0Nq6J9C5hP9vK/Q==} + engines: {node: '>=0.10.0'} + + postgres-interval@1.2.0: + resolution: {integrity: sha512-9ZhXKM/rw350N1ovuWHbGxnGh/SNJ4cnxHiM0rxE4VN41wsg8P8zWn9hv/buK00RP4WvlOyr/RBDiptyxVbkZQ==} + engines: {node: '>=0.10.0'} + prelude-ls@1.2.1: resolution: {integrity: sha512-vkcDPrRZo1QZLbn5RLGPpg/WmIQ65qoWWhcGKf/b5eplkkarX0m9z8ppCat4mlOqUsWpyNuYgO3VRyrYHSzX5g==} engines: {node: '>= 0.8.0'} @@ -4654,6 +4713,10 @@ packages: spawndamnit@3.0.1: resolution: {integrity: sha512-MmnduQUuHCoFckZoWnXsTg7JaiLBJrKFj9UI2MbRPGaJeVpsLcVBu6P/IGZovziM/YBsellCmsprgNA+w0CzVg==} + split2@4.2.0: + resolution: {integrity: sha512-UcjcJOWknrNkF6PLX83qcHM6KHgVKNkV62Y8a5uYDVv9ydGQVwAHMKqHdJje1VTWpljG0WYpCDhrCdAOYH4TWg==} + engines: {node: '>= 10.x'} + sprintf-js@1.0.3: resolution: {integrity: sha512-D9cPgkvLlV3t3IzL0D0YLvGA9Ahk4PcvVwUbN0dSGr1aP0Nrt4AEnTUbuGvquEC0mA64Gqt1fzirlRs5ibXx8g==} @@ -5029,6 +5092,10 @@ packages: utf-8-validate: optional: true + xtend@4.0.2: + resolution: {integrity: sha512-LKYU1iAXJXUgAXn9URjiu+MWhyUXHsvfp7mcuYm9dSUKK0/CjtrUwFAxD82/mCWbtLsGjFIad0wIsod4zrTAEQ==} + engines: {node: '>=0.4'} + yaml@2.7.0: resolution: {integrity: sha512-+hSoy/QHluxmC9kCIJyL/uyFmLmc+e5CFR5Wa+bpIhIj85LVb9ZH2nVnqrHoSvKogwODv0ClqZkmiSSaIH5LTA==} engines: {node: '>= 14'} @@ -6681,6 +6748,12 @@ snapshots: dependencies: undici-types: 6.20.0 + '@types/pg@8.20.0': + dependencies: + '@types/node': 22.13.9 + pg-protocol: 1.14.0 + pg-types: 2.2.0 + '@types/react-dom@19.0.4(@types/react@19.0.10)': dependencies: '@types/react': 19.0.10 @@ -8845,6 +8918,41 @@ snapshots: pcre-to-regexp@1.1.0: {} + pg-cloudflare@1.4.0: + optional: true + + pg-connection-string@2.13.0: {} + + pg-int8@1.0.1: {} + + pg-pool@3.14.0(pg@8.21.0): + dependencies: + pg: 8.21.0 + + pg-protocol@1.14.0: {} + + pg-types@2.2.0: + dependencies: + pg-int8: 1.0.1 + postgres-array: 2.0.0 + postgres-bytea: 1.0.1 + postgres-date: 1.0.7 + postgres-interval: 1.2.0 + + pg@8.21.0: + dependencies: + pg-connection-string: 2.13.0 + pg-pool: 3.14.0(pg@8.21.0) + pg-protocol: 1.14.0 + pg-types: 2.2.0 + pgpass: 1.0.5 + optionalDependencies: + pg-cloudflare: 1.4.0 + + pgpass@1.0.5: + dependencies: + split2: 4.2.0 + picocolors@1.1.1: {} picomatch@2.3.1: {} @@ -8904,6 +9012,16 @@ snapshots: picocolors: 1.1.1 source-map-js: 1.2.1 + postgres-array@2.0.0: {} + + postgres-bytea@1.0.1: {} + + postgres-date@1.0.7: {} + + postgres-interval@1.2.0: + dependencies: + xtend: 4.0.2 + prelude-ls@1.2.1: {} prettier@2.8.8: {} @@ -9260,6 +9378,8 @@ snapshots: cross-spawn: 7.0.6 signal-exit: 4.1.0 + split2@4.2.0: {} + sprintf-js@1.0.3: {} stable-hash@0.0.4: {} @@ -9719,6 +9839,8 @@ snapshots: ws@8.18.0: {} + xtend@4.0.2: {} + yaml@2.7.0: {} yocto-queue@0.1.0: {} diff --git a/scripts/production-smoke.mjs b/scripts/production-smoke.mjs index 7ed0d2a3..e92072b4 100644 --- a/scripts/production-smoke.mjs +++ b/scripts/production-smoke.mjs @@ -52,10 +52,13 @@ await smoke.check("API readiness", async () => { ready.status === "ready", `unexpected readiness: ${JSON.stringify(ready)}`, ); + assert( + ready.service === "api-ground-codes", + `unexpected service: ${JSON.stringify(ready)}`, + ); assert( typeof ready.runtimeTag === "string" && - (ready.runtimeTag === "workspace" || - ready.runtimeTag.startsWith("railway-api-runtime-")), + (ready.runtimeTag === "workspace" || ready.runtimeTag === "unknown"), `missing runtime tag: ${JSON.stringify(ready)}`, ); assert( From 8ec0c2b6703a179c3be99edb57ac3f3f94322598 Mon Sep 17 00:00:00 2001 From: hmmhmmhm Date: Mon, 1 Jun 2026 11:38:56 +0900 Subject: [PATCH 2/3] ci(api): deploy Worker with region data refresh Add an API production workflow that applies the PostGIS schema, refreshes changed region datasets in Supabase, deploys the Cloudflare Worker, and runs production smoke checks. --- .github/workflows/deploy-api.yml | 109 ++++++++++++++++++ apps/api-ground-codes/package.json | 1 + .../scripts/apply-postgis-schema.mjs | 30 +++++ .../scripts/list-changed-region-datasets.mjs | 41 +++++++ scripts/qa-workflows.test.mjs | 23 +++- 5 files changed, 203 insertions(+), 1 deletion(-) create mode 100644 .github/workflows/deploy-api.yml create mode 100644 apps/api-ground-codes/scripts/apply-postgis-schema.mjs create mode 100644 apps/api-ground-codes/scripts/list-changed-region-datasets.mjs diff --git a/.github/workflows/deploy-api.yml b/.github/workflows/deploy-api.yml new file mode 100644 index 00000000..15c56aed --- /dev/null +++ b/.github/workflows/deploy-api.yml @@ -0,0 +1,109 @@ +name: Deploy API to Cloudflare Workers + +on: + push: + branches: + - main + paths: + - "apps/api-ground-codes/**" + - "packages/ground-codes/**" + - "packages/geoint/region-dist/**" + - "packages/codebook/codebook-dist/**" + - ".github/workflows/deploy-api.yml" + - "package.json" + - "pnpm-lock.yaml" + - "pnpm-workspace.yaml" + workflow_dispatch: + inputs: + import_all_regions: + description: "Import every missing region dataset before deploying" + required: false + type: boolean + default: false + +concurrency: + group: deploy-api-production + cancel-in-progress: false + +jobs: + deploy: + runs-on: ubuntu-latest + + steps: + - name: Checkout repository + uses: actions/checkout@v6 + with: + fetch-depth: 0 + + - name: Setup Node.js + uses: actions/setup-node@v6 + with: + node-version: "22" + package-manager-cache: false + + - name: Setup pnpm + uses: pnpm/action-setup@v6 + with: + version: 9.0.0 + + - name: Setup Bun + uses: oven-sh/setup-bun@v2 + with: + bun-version: latest + + - name: Install dependencies + run: pnpm install --frozen-lockfile + + - name: Type check API + run: pnpm --filter api-ground-codes check-types + + - name: Test API + run: pnpm --filter api-ground-codes test + + - name: Build ground-codes package + run: pnpm --filter ground-codes build + + - name: Install Wrangler + run: pnpm install -g wrangler + + - name: Apply PostGIS schema + env: + SUPABASE_DB_URL: ${{ secrets.SUPABASE_DB_URL }} + PGSSLMODE: require + PGSSLREJECTUNAUTHORIZED: "0" + run: pnpm --filter api-ground-codes data:apply-postgis-schema + + - name: Detect changed region datasets + id: regions + run: | + if [ "${{ github.event_name }}" = "workflow_dispatch" ] && [ "${{ inputs.import_all_regions }}" = "true" ]; then + echo "datasets=__all_missing__" >> "$GITHUB_OUTPUT" + elif [ "${{ github.event_name }}" = "workflow_dispatch" ]; then + echo "datasets=" >> "$GITHUB_OUTPUT" + else + DATASETS="$(node apps/api-ground-codes/scripts/list-changed-region-datasets.mjs "${{ github.event.before }}" "${{ github.sha }}")" + echo "datasets=${DATASETS}" >> "$GITHUB_OUTPUT" + fi + + - name: Import changed region datasets + if: steps.regions.outputs.datasets != '' + env: + SUPABASE_DB_URL: ${{ secrets.SUPABASE_DB_URL }} + PGSSLMODE: require + PGSSLREJECTUNAUTHORIZED: "0" + REGION_IMPORT_BATCH_SIZE: "1500" + run: | + if [ "${{ steps.regions.outputs.datasets }}" = "__all_missing__" ]; then + REGION_IMPORT_MODE=missing pnpm --filter api-ground-codes data:import-postgis + else + REGION_DATASETS="${{ steps.regions.outputs.datasets }}" REGION_IMPORT_MODE=replace pnpm --filter api-ground-codes data:import-postgis + fi + + - name: Deploy API Worker + env: + CLOUDFLARE_API_TOKEN: ${{ secrets.CLOUDFLARE_API_TOKEN }} + CLOUDFLARE_ACCOUNT_ID: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }} + run: wrangler deploy --config apps/api-ground-codes/wrangler.toml + + - name: Run production smoke + run: pnpm production:smoke diff --git a/apps/api-ground-codes/package.json b/apps/api-ground-codes/package.json index 63306b5d..35df4a85 100644 --- a/apps/api-ground-codes/package.json +++ b/apps/api-ground-codes/package.json @@ -8,6 +8,7 @@ "build": "node scripts/build.mjs", "start": "bun run src/index.ts", "check-types": "tsc --noEmit", + "data:apply-postgis-schema": "node scripts/apply-postgis-schema.mjs", "data:import-postgis": "node scripts/import-postgis-regions.mjs", "test": "bun test src/*.test.ts" }, diff --git a/apps/api-ground-codes/scripts/apply-postgis-schema.mjs b/apps/api-ground-codes/scripts/apply-postgis-schema.mjs new file mode 100644 index 00000000..5b021cae --- /dev/null +++ b/apps/api-ground-codes/scripts/apply-postgis-schema.mjs @@ -0,0 +1,30 @@ +import { readFile } from "node:fs/promises"; +import { dirname, resolve } from "node:path"; +import { fileURLToPath } from "node:url"; +import { Client } from "pg"; + +const here = dirname(fileURLToPath(import.meta.url)); +const schemaPath = resolve(here, "../supabase/001_ground_code_regions.sql"); + +const connectionString = + process.env.SUPABASE_DB_URL ?? process.env.DATABASE_URL; +if (!connectionString) { + console.error("Set SUPABASE_DB_URL or DATABASE_URL before applying schema."); + process.exit(1); +} + +const client = new Client({ + connectionString, + ssl: + process.env.PGSSLMODE === "require" + ? { rejectUnauthorized: process.env.PGSSLREJECTUNAUTHORIZED !== "0" } + : undefined, +}); + +await client.connect(); +try { + await client.query(await readFile(schemaPath, "utf8")); + console.log("Applied Supabase PostGIS schema."); +} finally { + await client.end(); +} diff --git a/apps/api-ground-codes/scripts/list-changed-region-datasets.mjs b/apps/api-ground-codes/scripts/list-changed-region-datasets.mjs new file mode 100644 index 00000000..bd17ae34 --- /dev/null +++ b/apps/api-ground-codes/scripts/list-changed-region-datasets.mjs @@ -0,0 +1,41 @@ +import { execFileSync } from "node:child_process"; +import { readdirSync } from "node:fs"; +import { resolve } from "node:path"; + +const [baseRef, headRef = "HEAD"] = process.argv.slice(2); +const regionDistDir = "packages/geoint/region-dist"; + +const listAllDatasets = () => + readdirSync(resolve(process.cwd(), regionDistDir)) + .filter((fileName) => fileName.endsWith(".json")) + .map((fileName) => fileName.replace(/\.json$/, "")) + .sort(); + +const normalizeBaseRef = () => { + if (!baseRef || /^0+$/.test(baseRef)) return null; + return baseRef; +}; + +const base = normalizeBaseRef(); +if (!base) { + console.log(listAllDatasets().join(",")); + process.exit(0); +} + +const changedFiles = execFileSync( + "git", + ["diff", "--name-only", base, headRef, "--", `${regionDistDir}/`], + { encoding: "utf8" }, +) + .split("\n") + .map((line) => line.trim()) + .filter(Boolean); + +const datasets = changedFiles + .filter((fileName) => fileName.endsWith(".json")) + .map((fileName) => + fileName.slice(`${regionDistDir}/`.length).replace(/\.json$/, ""), + ) + .sort(); + +console.log([...new Set(datasets)].join(",")); diff --git a/scripts/qa-workflows.test.mjs b/scripts/qa-workflows.test.mjs index afca4844..4c433fdc 100644 --- a/scripts/qa-workflows.test.mjs +++ b/scripts/qa-workflows.test.mjs @@ -48,8 +48,29 @@ describe("production smoke workflow triggers", () => { assert.match(smokeWorkflow, /force_failure:/); assert.match(smokeWorkflow, /GROUND_CODES_SMOKE_FORCE_FAILURE/); assert.match(smokeWorkflow, /Deploy Web to Cloudflare Pages/); - assert.match(smokeWorkflow, /github\.event\.workflow_run\.conclusion == 'success'/); + assert.match( + smokeWorkflow, + /github\.event\.workflow_run\.conclusion == 'success'/, + ); assert.match(smokeScript, /GITHUB_STEP_SUMMARY/); assert.match(smokeScript, /GROUND_CODES_SMOKE_FORCE_FAILURE/); }); }); + +describe("API deployment workflow", () => { + test("deploys Worker after API data changes and refreshes changed region datasets", () => { + const deployApiWorkflow = readText("../.github/workflows/deploy-api.yml"); + + assert.match(deployApiWorkflow, /Deploy API to Cloudflare Workers/); + assert.match(deployApiWorkflow, /packages\/geoint\/region-dist\/\*\*/); + assert.match(deployApiWorkflow, /packages\/codebook\/codebook-dist\/\*\*/); + assert.match(deployApiWorkflow, /data:apply-postgis-schema/); + assert.match(deployApiWorkflow, /list-changed-region-datasets\.mjs/); + assert.match(deployApiWorkflow, /REGION_IMPORT_MODE=replace/); + assert.match( + deployApiWorkflow, + /wrangler deploy --config apps\/api-ground-codes\/wrangler\.toml/, + ); + assert.match(deployApiWorkflow, /pnpm production:smoke/); + }); +}); From 53d0887a4e6a7cc7be58b7d8bc19f591de823db0 Mon Sep 17 00:00:00 2001 From: hmmhmmhm Date: Tue, 2 Jun 2026 19:01:16 +0900 Subject: [PATCH 3/3] fix(api): surface Worker smoke failures --- .github/workflows/deploy-api.yml | 9 +++- .github/workflows/production-smoke.yml | 42 +++++++++++++++++++ apps/api-ground-codes/src/app.test.ts | 28 +++++++++++++ .../api-ground-codes/src/endpoints/healthz.ts | 24 +++++++---- apps/api-ground-codes/src/worker.ts | 12 ++++++ scripts/qa-workflows.test.mjs | 12 +++++- 6 files changed, 116 insertions(+), 11 deletions(-) diff --git a/.github/workflows/deploy-api.yml b/.github/workflows/deploy-api.yml index 15c56aed..c3184354 100644 --- a/.github/workflows/deploy-api.yml +++ b/.github/workflows/deploy-api.yml @@ -103,7 +103,14 @@ jobs: env: CLOUDFLARE_API_TOKEN: ${{ secrets.CLOUDFLARE_API_TOKEN }} CLOUDFLARE_ACCOUNT_ID: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }} - run: wrangler deploy --config apps/api-ground-codes/wrangler.toml + run: | + wrangler deploy \ + --config apps/api-ground-codes/wrangler.toml \ + --keep-vars \ + --var API_RUNTIME_TAG:workspace \ + --var GIT_COMMIT_SHA:${{ github.sha }} \ + --tag ${{ github.sha }} \ + --message "Deploy API Worker from ${GITHUB_SHA}" - name: Run production smoke run: pnpm production:smoke diff --git a/.github/workflows/production-smoke.yml b/.github/workflows/production-smoke.yml index e6827419..fd4a1d24 100644 --- a/.github/workflows/production-smoke.yml +++ b/.github/workflows/production-smoke.yml @@ -16,6 +16,10 @@ on: types: - completed +permissions: + contents: read + issues: write + jobs: smoke: if: github.event_name != 'workflow_run' || github.event.workflow_run.conclusion == 'success' @@ -43,3 +47,41 @@ jobs: curl -X POST https://api.getmoshi.app/api/webhook \ -H "Content-Type: application/json" \ -d "{\"token\":\"${MOSHI_WEBHOOK_TOKEN}\",\"title\":\"ground.codes smoke failed\",\"message\":\"Production smoke failed on ${GITHUB_REF_NAME}. Run: ${GITHUB_SERVER_URL}/${GITHUB_REPOSITORY}/actions/runs/${GITHUB_RUN_ID}\"}" + + - name: Open smoke failure issue + if: failure() && env.MOSHI_WEBHOOK_TOKEN == '' + uses: actions/github-script@v8 + with: + script: | + const title = "Production smoke failed"; + const runUrl = `${process.env.GITHUB_SERVER_URL}/${process.env.GITHUB_REPOSITORY}/actions/runs/${process.env.GITHUB_RUN_ID}`; + const message = [ + `Production smoke failed on ${process.env.GITHUB_REF_NAME}.`, + "", + `Run: ${runUrl}`, + "", + "MOSHI_WEBHOOK_TOKEN is not configured, so this issue is the fallback notification.", + ].join("\n"); + const { owner, repo } = context.repo; + const { data: issues } = await github.rest.issues.listForRepo({ + owner, + repo, + state: "open", + per_page: 50, + }); + const existing = issues.find((issue) => issue.title === title); + if (existing) { + await github.rest.issues.createComment({ + owner, + repo, + issue_number: existing.number, + body: message, + }); + } else { + await github.rest.issues.create({ + owner, + repo, + title, + body: message, + }); + } diff --git a/apps/api-ground-codes/src/app.test.ts b/apps/api-ground-codes/src/app.test.ts index 9d94248e..ad5b851d 100644 --- a/apps/api-ground-codes/src/app.test.ts +++ b/apps/api-ground-codes/src/app.test.ts @@ -35,6 +35,34 @@ describe("Ground Codes API contract", () => { }); }); + test("serves deployment runtime metadata from environment", async () => { + const previousRuntimeTag = process.env.API_RUNTIME_TAG; + const previousGitCommitSha = process.env.GIT_COMMIT_SHA; + process.env.API_RUNTIME_TAG = "workspace"; + process.env.GIT_COMMIT_SHA = "8ec0c2b6703a179c3be99edb57ac3f3f94322598"; + + try { + const response = await get("/readyz"); + + expect(response.status).toBe(200); + expect(await response.json()).toMatchObject({ + runtimeTag: "workspace", + runtimeCommit: "8ec0c2b6703a179c3be99edb57ac3f3f94322598", + }); + } finally { + if (previousRuntimeTag === undefined) { + delete process.env.API_RUNTIME_TAG; + } else { + process.env.API_RUNTIME_TAG = previousRuntimeTag; + } + if (previousGitCommitSha === undefined) { + delete process.env.GIT_COMMIT_SHA; + } else { + process.env.GIT_COMMIT_SHA = previousGitCommitSha; + } + } + }); + test("serves lightweight operational metrics", async () => { await get("/healthz"); diff --git a/apps/api-ground-codes/src/endpoints/healthz.ts b/apps/api-ground-codes/src/endpoints/healthz.ts index dc87a1af..d6ce2ad0 100644 --- a/apps/api-ground-codes/src/endpoints/healthz.ts +++ b/apps/api-ground-codes/src/endpoints/healthz.ts @@ -44,21 +44,26 @@ const runtimeDependency = packageJson.dependencies["ground-codes"] ?? packageJson.dependencies["@repo/codebook"] ?? ""; -const runtimeTag = - process.env.API_RUNTIME_TAG ?? - runtimeDependency.match(/#([^&]+)&path:/)?.[1] ?? - (runtimeDependency.startsWith("workspace:") ? "workspace" : "unknown"); const lockfilePath = endpointDir ? findUp("pnpm-lock.yaml", endpointDir) : null; const lockfileCommit = lockfilePath ? readFileSync(lockfilePath, "utf8").match( /ground\.codes\/tar\.gz\/([0-9a-f]{40})#path:packages\/ground-codes/, )?.[1] : undefined; -const runtimeCommit = - lockfileCommit ?? - process.env.RAILWAY_GIT_COMMIT_SHA ?? - process.env.GIT_COMMIT_SHA ?? - "0000000000000000000000000000000000000000"; + +export const getRuntimeMetadata = () => { + const runtimeTag = + process.env.API_RUNTIME_TAG ?? + runtimeDependency.match(/#([^&]+)&path:/)?.[1] ?? + (runtimeDependency.startsWith("workspace:") ? "workspace" : "unknown"); + const runtimeCommit = + lockfileCommit ?? + process.env.RAILWAY_GIT_COMMIT_SHA ?? + process.env.GIT_COMMIT_SHA ?? + "0000000000000000000000000000000000000000"; + + return { runtimeTag, runtimeCommit }; +}; export const healthz = new Elysia().get( "/healthz", @@ -82,6 +87,7 @@ export const readyz = new Elysia().get( "/readyz", async ({ set }) => { set.headers["cache-control"] = "no-store"; + const { runtimeTag, runtimeCommit } = getRuntimeMetadata(); return { status: "ready", diff --git a/apps/api-ground-codes/src/worker.ts b/apps/api-ground-codes/src/worker.ts index 083c1da4..c24ae87c 100644 --- a/apps/api-ground-codes/src/worker.ts +++ b/apps/api-ground-codes/src/worker.ts @@ -5,6 +5,8 @@ export interface Env { HYPERDRIVE?: { connectionString: string; }; + API_RUNTIME_TAG?: string; + GIT_COMMIT_SHA?: string; SUPABASE_DB_URL?: string; CORS_ORIGINS?: string; } @@ -23,8 +25,18 @@ const installRegionStoreFromEnv = (env: Env) => { installedConnectionString = connectionString; }; +const installRuntimeMetadataFromEnv = (env: Env) => { + if (env.API_RUNTIME_TAG) { + process.env.API_RUNTIME_TAG = env.API_RUNTIME_TAG; + } + if (env.GIT_COMMIT_SHA) { + process.env.GIT_COMMIT_SHA = env.GIT_COMMIT_SHA; + } +}; + export default { async fetch(request: Request, env: Env): Promise { + installRuntimeMetadataFromEnv(env); installRegionStoreFromEnv(env); return await app.handle(request); }, diff --git a/scripts/qa-workflows.test.mjs b/scripts/qa-workflows.test.mjs index 4c433fdc..c1ed5189 100644 --- a/scripts/qa-workflows.test.mjs +++ b/scripts/qa-workflows.test.mjs @@ -48,6 +48,9 @@ describe("production smoke workflow triggers", () => { assert.match(smokeWorkflow, /force_failure:/); assert.match(smokeWorkflow, /GROUND_CODES_SMOKE_FORCE_FAILURE/); assert.match(smokeWorkflow, /Deploy Web to Cloudflare Pages/); + assert.match(smokeWorkflow, /issues: write/); + assert.match(smokeWorkflow, /actions\/github-script@v8/); + assert.match(smokeWorkflow, /MOSHI_WEBHOOK_TOKEN is not configured/); assert.match( smokeWorkflow, /github\.event\.workflow_run\.conclusion == 'success'/, @@ -67,9 +70,16 @@ describe("API deployment workflow", () => { assert.match(deployApiWorkflow, /data:apply-postgis-schema/); assert.match(deployApiWorkflow, /list-changed-region-datasets\.mjs/); assert.match(deployApiWorkflow, /REGION_IMPORT_MODE=replace/); + assert.match(deployApiWorkflow, /wrangler deploy/); assert.match( deployApiWorkflow, - /wrangler deploy --config apps\/api-ground-codes\/wrangler\.toml/, + /--config apps\/api-ground-codes\/wrangler\.toml/, + ); + assert.match(deployApiWorkflow, /--keep-vars/); + assert.match(deployApiWorkflow, /--var API_RUNTIME_TAG:workspace/); + assert.match( + deployApiWorkflow, + /--var GIT_COMMIT_SHA:\$\{\{ github\.sha \}\}/, ); assert.match(deployApiWorkflow, /pnpm production:smoke/); });