Genace
Sign inGet started

PostgreSQL generation job store: keep application ownership and revisions

A multi-user application needs a durable mapping between its users and the remote jobs created with its server key. PostgreSQL can hold that mapping and coordinate short updates from several collectors. The table in this guide belongs to your application. It is not a description of Genace’s internal job schema or permission model.

Model ownership before worker coordination

Store a local operation ID, tenant identity, specification fingerprint, remote job ID, and local revision. Add separate columns for rendering state and delivery state so a storage problem does not masquerade as another render failure. Derive the tenant from the authenticated session instead of trusting a field in the browser’s JSON.

CREATE TABLE app_media_operations (
  operation_id text PRIMARY KEY,
  tenant_id text NOT NULL,
  specification_hash text NOT NULL,
  remote_job_id text,
  render_state text,
  delivery_state text NOT NULL DEFAULT 'not_started',
  revision bigint NOT NULL DEFAULT 0,
  updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX app_media_operations_tenant_idx
  ON app_media_operations (tenant_id, operation_id);

Apply schema changes through your normal migration process. Do not run example DDL against a production database while reading a tutorial. Decide whether a remote job should be unique across local operations and enforce that policy explicitly when required.

Commit accepted references promptly

After an upstream POST returns top-level job_id, update the existing operation in a short transaction. A submission-failure envelope can instead provide error.job_id; preserve it for reconciliation. Do not hold row locks while waiting on an HTTP request, because a rendering or networking delay should not block unrelated database transactions.

The upstream GET snapshot uses id. Validate it matches the expected saved job before storing state. A malformed response or mismatched ID is a protocol incident, not permission to overwrite the operation’s remote reference.

Use conditional updates for collectors

An observer can save a snapshot only if the operation revision and expected remote ID still match. Increment the revision with the update and check affected row count. If another worker already advanced the operation, reload instead of blindly overwriting it with a stale pending response.

For work claiming, use short row-lock transactions or conditional updates appropriate to your workload. Keep accepted rendering states and local scheduling phases distinct. PostgreSQL coordination can fence local writes, but it cannot prevent a stale process from having already sent an external POST.

Authorize reads through the mapping

Before returning status to a browser, load the operation under its tenant condition. Only then request the remote job using your server credential. Genace checks the account associated with that credential; it does not know which of your local tenants created each job.

Store only necessary snapshot data and restrict access to prompts and output references. A database that excludes API keys can still contain sensitive assets and should receive appropriate backup and retention controls.

Verify concurrent recovery

Run two collectors that observe different snapshots of the same job and confirm a stale update cannot replace a newer terminal result. Restart workers after acceptance and verify they resume observation. Test tenant isolation, an unknown job ID, and a delivery retry after success. Confirm every failure branch leaves enough durable evidence to recover without another creation request.

See worker leases, tenant authorization, and unique operation keys.

Continue with production operations

Where these facts come from