Application databases

How a Sites app connects

Create a database on the exact Sites app, then build a new deployment. Current protocolVersion 2 bindings in THORA_DATABASES have id, name, endpoint, batchEndpoint, contextEndpoint, and a deployment-specific token. Resolve the environment once per authenticated app request, then include that same environment on every query and batch. Your backend sends parameterized SQL over HTTPS; browsers call your backend's own API routes. Check the binding protocol and current capabilities before using these source-level features.

There is no customer DATABASE_URL or PostgreSQL password to retrieve, and no pg/Prisma connection string to invent. The Sites SQL gateway is an HTTPS interface, not a native PostgreSQL socket. A separate managed Compute/Labs database has a different contract, described below.

• Browser → authenticated app API → Sites SQL gateway → app database.
• A static Vite bundle or Next.js static export cannot run database queries by itself. Run a Node server in the Sites container (it listens on the app port, 8080 for workflow sites) and call the database from that server.
• Bindings are available at container runtime, not Docker build time. A database added after deployment requires a new build.
• Never place THORA_DATABASES or its token in VITE_*, NEXT_PUBLIC_*, generated HTML, Git, logs, or browser responses.

Agent setup: choose, create, prepare, build

1. Read the active organization and thora_capabilities_read. Discover the app with thora_sites_hosting_read, then read thora_sites_site_read and thora_sites_addons_read with that exact appId.
2. Call thora_sites_database_setup with the appId once per site, before the first draft preview. It is safe to repeat: it reuses the site's included database or creates one, makes sure its private preview copy exists, and returns one nextAction plus a plain userMessage to relay. Legacy databases get their preview copy automatically; you never need preview-setup. Then poll thora_sites_database_inspect until environments.preview.state is "ready" and nextAction is not wait_for_database. Migrate only after that.
3. Retain the database UUID and its exact, case-sensitive name. The example uses Website. Do not substitute a Git repository UUID, deployment UUID, or another site's database.
4. Use thora_sites_database_inspect with schema:true to see existing tables instead of asking the user. Schema changes are tracked migrations only: call thora_sites_database_manage with action migrate, environment preview, a stable version such as 0001_create_signups and parameterized statements. Reuse the identical version and body after an uncertain result; changed contents for an applied version are rejected. Production refuses a migration until the identical version is in preview. Apply it to production, oldest first, only after the user says go ahead. Production writes accept only INSERT, UPDATE and DELETE. Do not run migrations in Docker builds, startup or readiness probes.
5. Add server code that reads the runtime binding. Push the intended source, then explicitly build the same Sites app; a Git push alone does not attach bindings or start a Sites build.
6. Test sign-in and reversible writes in the signed-in preview using synthetic data. Use thora_sites_database_manage with action query and environment preview for preview data; thora_sites_database_query always runs against the live production database and is not for preview testing. Prepare the production schema and attachments separately, request publication of the exact tested candidate, and verify its live behavior. Publication does not copy preview data over production.

{
  "appId": "<site UUID from thora_sites_hosting_read>"
}

{
  "appId": "<same Sites app UUID>",
  "databaseId": "<database.id returned by thora_sites_database_setup>",
  "environment": "preview",
  "action": "migrate",
  "input": {
    "version": "001_notes",
    "statements": [{
      "mode": "write",
      "sql": "CREATE TABLE IF NOT EXISTS notes (id text PRIMARY KEY, title text NOT NULL)",
      "params": []
    }]
  }
}

Query from your app server

Copy sites.mjs below and the optional db.mjs convenience wrapper into server-only modules. Replace Website with the selected database name. App authentication and per-record authorization belong in your own backend routes; the binding is not end-user identity. Never accept raw SQL, a database endpoint, or a token from browser input.

Resolve beginSiteRequest once per app request and share that context across its database and service calls. An environment change stops the operation; never refresh context and retry the same write against production. Use fixed SQL and $1, $2 parameters, explicit LIMIT values and stable-ID pagination. Inspect truncated. A timed-out write may have committed: use a separate recovery flow to inspect state by its stable identity.

// sites.mjs — server only. Never import from a browser module.
function safeError(code, status = 503) {
  const error = new Error(code);
  error.code = code;
  error.status = status;
  return error;
}
function binding(value) {
  if (!value || typeof value.endpoint !== 'string' ||
      typeof value.token !== 'string' || !value.token) {
    throw safeError('site_binding_missing');
  }
  return value;
}
async function post(endpoint, token, input) {
  const url = new URL(endpoint);
  if (url.protocol !== 'https:' || url.username || url.password || url.hash) {
    throw safeError('site_binding_invalid');
  }
  const body = JSON.stringify(input);
  if (new TextEncoder().encode(body).length > 65536) {
    throw safeError('site_request_too_large', 413);
  }
  const response = await fetch(url, {
    method: 'POST', redirect: 'error',
    signal: AbortSignal.timeout(15_000),
    headers: {authorization: 'Bearer ' + token, 'content-type': 'application/json'},
    body
  });
  const reader = response.body?.getReader();
  if (!reader) throw safeError('site_runtime_invalid_response');
  const chunks = []; let size = 0;
  try {
    while (true) {
      const chunk = await reader.read();
      if (chunk.done) break;
      size += chunk.value.length;
      if (size > 262144) throw safeError('site_runtime_invalid_response');
      chunks.push(chunk.value);
    }
  } finally { await reader.cancel().catch(() => {}); }
  const bytes = new Uint8Array(size); let offset = 0;
  for (const chunk of chunks) { bytes.set(chunk, offset); offset += chunk.length; }
  let result;
  try { result = JSON.parse(new TextDecoder().decode(bytes)); }
  catch { throw safeError('site_runtime_invalid_response'); }
  if (!response.ok) {
    const code = result?.error?.code;
    throw safeError(typeof code === 'string' && /^[a-zA-Z0-9_]{1,100}$/.test(code)
      ? code : 'site_runtime_request_failed', response.status);
  }
  if (!result || typeof result !== 'object' || Array.isArray(result)) {
    throw safeError('site_runtime_invalid_response');
  }
  return result;
}

// Invoke once after authentication for each app request or worker iteration.
// Do not cache this context across requests or refresh it during an operation.
export async function beginSiteRequest(databaseName = 'Website') {
  let services, database;
  try {
    services = process.env.THORA_SERVICES
      ? binding(JSON.parse(process.env.THORA_SERVICES)) : null;
    const databases = JSON.parse(process.env.THORA_DATABASES || '[]');
    if (!Array.isArray(databases)) throw new Error();
    database = databases.find(item => item.name === databaseName);
    if (database) binding(database);
  } catch { throw safeError('site_binding_invalid'); }
  let context;
  try {
  if (services) {
    context = await post(services.endpoint, services.token,
      {service: 'context', action: 'read', input: {}});
  } else {
    if (!database || database.protocolVersion !== 2 || !database.contextEndpoint) {
      throw safeError('site_binding_upgrade_required');
    }
    context = await post(database.contextEndpoint, database.token, {});
  }
  } catch (error) {
    throw error?.code ? error : safeError('site_runtime_context_unavailable');
  }
  const environment = context.environment;
  if (environment !== 'preview' && environment !== 'production') {
    throw safeError('site_runtime_invalid_context');
  }
  let stopped = false;
  async function guarded(work) {
    if (stopped) throw safeError('site_request_context_ended', 409);
    try { return await work(); }
    catch (error) {
      stopped = true; // No retry or cross-environment continuation after failure.
      throw error?.code ? error : safeError('site_runtime_request_uncertain');
    }
  }
  async function service(name, action, input = {}) {
    return guarded(async () => {
      if (!services) throw safeError('site_services_binding_missing');
      const response = await post(services.endpoint, services.token,
        {environment, service: name, action, input});
      if (response.environment !== environment) throw safeError('site_environment_changed', 409);
      return response.result;
    });
  }
  async function data(input, batch = false) {
    return guarded(async () => {
      if (!database || database.protocolVersion !== 2) {
        throw safeError('site_binding_upgrade_required');
      }
      const endpoint = batch ? database.batchEndpoint : database.endpoint;
      if (!endpoint) throw safeError('database_binding_missing');
      return post(endpoint, database.token, {...input, environment});
    });
  }
  return Object.freeze({
    environment,
    deploymentId: context.result?.deploymentId,
    secret: async name => (await service('secrets', 'get', {name})).value,
    storage: (action, input) => service('storage', action, input),
    jobs: (action, input) => service('jobs', action, input),
    dossier: (action = 'people', input = {}) => service('dossier', action, input),
    sendEmail: input => service('email', 'send', input),
    databaseQuery: (sql, params = [], mode = 'read') => data({sql, params, mode}),
    databaseBatch: statements => {
      if (!Array.isArray(statements) || statements.length < 1 || statements.length > 20) {
        throw safeError('invalid_database_batch', 400);
      }
      return data({statements}, true); // {atomic: true, results: [{rows,rowCount,truncated}]}
    }
  });
}
// Treat runtime bindings and secret values as credentials. Never log them.
// Use fixed SQL, app authorization, validation, and per-user rate limits.
// Inspect uncertain writes using their stable identity in a separate recovery flow.

// db.mjs — import only from server routes, never browser modules.
import {beginSiteRequest} from './sites.mjs';

// Convenience for a ONE-query request. Existing three-argument calls work.
// For multiple operations, create one context and pass it as the fourth argument,
// or call app.databaseQuery directly. Never refresh context halfway through work.
export async function databaseQuery(sql, params = [], mode = 'read', context) {
  const app = context || await beginSiteRequest('Website');
  return app.databaseQuery(sql, params, mode); // {rows, rowCount, truncated}
}

// Inside an authenticated server route, after app authorization:
// const app = await beginSiteRequest('Website');
// const result = await databaseQuery(
//   'INSERT INTO notes (id, title) VALUES ($1, $2) RETURNING id, title',
//   [noteId, title], 'write', app);
// Preview writes stay in the isolated preview database.
// Environment changes end the operation; neither this wrapper nor sites.mjs retries.

Minimal runnable Node backend

For a Sites target that supports the configured Docker container, put sites.mjs, db.mjs, server.mjs and Dockerfile in the repository, use the app's configured port (the sample listens on 8080, the port of sites created with the Sites workflow tools; a connected repository uses the port chosen at connection), and build it. This small example tests the injected binding; it does not serve your frontend or implement customer-data authorization. Integrate the helper with your real server routes and build/serve the frontend in the same app as appropriate.

The server must listen on 0.0.0.0 at the configured port. It runs without root, on a read-only filesystem, and without persistent container volumes. Keep durable app data in attached services. The root readiness route stays healthy without writing to the database.

// server.mjs — minimal connectivity probe; no private rows exposed.
import {createServer} from 'node:http';
import {databaseQuery} from './db.mjs';

createServer(async (request, response) => {
  response.setHeader('content-type', 'application/json');
  response.setHeader('cache-control', 'no-store');
  if (request.method === 'GET' && request.url === '/') {
    response.end(JSON.stringify({ok: true})); // readiness probe must not write
    return;
  }
  if (request.method === 'GET' && request.url === '/api/database-health') {
    try {
      const result = await databaseQuery('SELECT 1 AS connected');
      response.end(JSON.stringify({ok: result.rows[0]?.connected === 1}));
    } catch {
      response.statusCode = 503;
      response.end(JSON.stringify({error: 'database_unavailable'}));
    }
    return;
  }
  response.statusCode = 404;
  response.end(JSON.stringify({error: 'not_found'}));
}).listen(8080, '0.0.0.0'); // the app's configured port: 8080 for sites created with the Sites workflow tools
// Add authentication, authorization, validation, and request-rate limits before
// implementing customer-data routes. This probe is not a complete app backend.

FROM node:24-alpine
WORKDIR /app
COPY --chown=10001:10001 sites.mjs db.mjs server.mjs ./
USER 10001:10001
EXPOSE 8080
CMD ["node", "server.mjs"]
# Sites app port: 8080 (thora_sites_site_read returns port; a repository connected with an explicit port uses that one).
# No secret ENV/ARG, migration-at-startup, or VOLUME.
# Adapt the image to build/serve your actual frontend and backend together.

Writable preview data stays separate from production

Current bindings route preview requests to an isolated preview database and live requests to production. Legacy databases get their isolated preview copy automatically at the next preview build; it starts empty, so re-apply tracked migrations to preview. Draft previews from the workflow receive the same preview database and service bindings as any preview build. Use synthetic preview data; no automatic production-data clone is implied. Building candidates may only read for readiness. Replaced deployments lose access.

At request start, read the current context through THORA_SERVICES or the database contextEndpoint. Pin its environment through every operation. If promotion happens during the request, later calls reject the stale environment instead of sending preview-derived work to production. Start a fresh context only for a new authenticated request or worker iteration. Publishing promotes the reviewed code with production bindings and never replaces production data with preview data.

Open the dashboard-issued signed-in preview link, probe /api/database-health, and test your authenticated forms and writes with synthetic accounts. Validate production migrations and attachments separately before requesting publication.

SQL and traffic bounds

• A query accepts one statement, up to 8 KiB SQL and 32 scalar parameters with 8 KiB combined parameter data. An atomic batch accepts 1–20 statements within 64 KiB total; all commit or all roll back. Open transactions across separate requests are unsupported.
• Read mode accepts SELECT or WITH and enforces a read-only transaction. Supported writes can use bounded RETURNING results. Table/index changes remain restricted by the gateway. Do not assume arbitrary functions, extension installation or role management is available; inspect current compatibility and capability responses.
• Query results contain rows, rowCount and truncated, with at most 100 rows, 64 KiB total and 16 KiB per row. A batch returns atomic: true and a results array. RETURNING does not remove result bounds; use pagination for lists.
• SQL timeouts, lock waits, concurrency and request admission remain operational safeguards. Current capacity is configurable; do not hard-code old request allowances or equate billing being disabled with unlimited traffic. Add application-level limits, bounded pagination and backoff for safe reads; never automatically retry uncertain writes.
• Database counts and resource capacity remain separate from billing. Read the current allowance and ready states rather than assuming additional capacity or live rollout.

Troubleshoot the connection without exposing secrets

• database_binding_missing or site_binding_upgrade_required: check the exact name, ready state and protocolVersion, then rebuild after setup. Never print process.env or binding JSON.
• sites_database_preview_setup_required: the preview copy is still being prepared. Call thora_sites_database_inspect and follow its nextAction; do not create another database.
• sites_database_preview_migration_required: apply the identical migration version to preview and test it before production.
• sites_database_preview_read_only means an older token/build cannot write preview data; rebuild with current bindings.
• site_environment_changed or sites_database_environment_changed: end the current request or job. Do not refresh context and continue old work against the new environment.
• sites_database_deployment_inactive: test the current deployment; reread Sites state and its signed-in preview link.
• sites_database_server_only: move SQL calls from browser JavaScript to your server. Do not remove browser headers as a workaround.
• sites_database_token_required: use this deployment's injected binding. Do not copy old tokens or request a password.
• sites_database_table_not_found: inspect migrations in the explicit environment and apply the missing authorized version.
• 429/busy: reduce request rate/concurrency and honor Retry-After. For an uncertain write, inspect state before retrying.
• Controller, database, capacity or recovery errors: read current state once and report the safe code and support reference. Do not create replacements or discard data as an outage workaround.

Separate service: managed Compute/Labs database

The Labs database is a bounded PostgreSQL service installed in one eligible managed Compute guest. The management interface selects the customer resource and idempotency key only; it does not accept SQL, roles, packages, paths, network values, limits, credentials, or an approval flag.

• Guest-loopback application access.
• 12 application connections and a 640 MiB memory ceiling.
• Three application backups and 2 GiB of retained dump data.
• At most one organization database reservation in the current Labs contract.
• Credentials and connection strings are never returned by management reads or agent tools.

Provision through a proposal

1. Read database capability and eligible Compute targets.
2. Choose the intended public Compute resource ID.
3. Create one provision proposal with a stable idempotency key.
4. Have a direct organization administrator review it in the dashboard.
5. Wait for terminal database state without requesting a second reservation.
6. Confirm the application can use its injected credential without exposing it.

Require application-consistent evidence

Disk capture and database backup are different evidence. A complete restore proves a named application backup can be restored into the new Compute generation, credentials rotate, the database returns ready, and the application reads the expected data.

• Do not claim application consistency from a disk snapshot alone.
• Do not restore over an active source generation.
• Keep the source or retained recovery asset until the isolated restore passes.
• Never print restored credentials or database contents as evidence.