Database (PostgreSQL on Cloud SQL) - Applied AI Foundation

The platform talks to Google Cloud SQL Postgres via the Cloud SQL Connector. Connection setup lives in src/lib/db/postgres.ts.

Connection

import { Connector, IpAddressTypes } from '@google-cloud/cloud-sql-connector'
import { Pool } from 'pg'

The Connector authenticates via a service account JSON, base64-encoded into the GOOGLE_SERVICE_ACCOUNT_BASE64 env var. At startup:

Decode the service account JSON
Write it to a tmp file (mode 0o600), point GOOGLE_APPLICATION_CREDENTIALS at it
Create a Connector instance and request connection options for the configured Cloud SQL instance
Delete the tmp file
Open a pg.Pool with the connector’s TCP options plus POSTGRES_USER / POSTGRES_PASSWORD / POSTGRES_DB

Pool configuration

new Pool({
  ...clientOpts,
  max: 5,
  min: 1,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 10_000,
})

Plus a per-connection statement timeout:

pool.on('connect', (client) => {
  client.query('SET statement_timeout = 30000').catch(() => {})
})

So the worst a stuck query can do is hold a connection for 30 seconds, then it gets killed.

Retry logic

isTransientError() matches:

ECONNRESET, ECONNREFUSED, ETIMEDOUT, EPIPE, EAI_AGAIN,
CONNECTION_TERMINATED_UNEXPECTEDLY

Plus Postgres SQL state codes:

57P01 admin_shutdown
57P02 crash_shutdown
57P03 cannot_connect_now
08006 connection_failure
08001 sqlclient_unable_to_establish_sqlconnection
08004 sqlserver_rejected_establishment_of_sqlconnection

A single automatic retry is performed when one of these is hit.

Type parsing

NUMERIC/DECIMAL (Postgres OID 1700) is parsed as float; INT8/BIGINT (OID 20) is parsed as int. Default pg behaviour is to return them as strings, which would break arithmetic everywhere — overridden in postgres.ts.

Graceful shutdown

SIGTERM and SIGINT handlers drain the pool and close the connector. Important on Vercel where deployments roll without warning.

Tables

Three configurable table names (src/lib/db/tables.ts):

export const VESSELS_TABLE     = process.env.TABLE_VESSELS ?? 'common_vessel_details'
export const CONSUMPTION_TABLE = process.env.TABLE_CONSUMPTION ?? 'common_consumption_log'
export const PARTICULARS_TABLE = process.env.TABLE_PARTICULARS ?? 'vessel_particulars_data'

This lets multiple deployments share schema but point at different physical tables — useful for multi-tenant setups or staging vs. production.

Query helpers

postgres.ts exports query() and queryOne() helpers used everywhere instead of acquiring a client manually. They handle the retry-on-transient logic and ensure clients are released back to the pool.

Schema management

DDL is not in the repo. Tables are managed externally. The platform connects to whatever schema is provided.

​Connection

​Pool configuration

​Retry logic

​Type parsing

​Graceful shutdown

​Tables

​Query helpers

​Schema management