POSTGRES_PROFILE.md

PolyAPI Postgres persistence profile 0.1

Status: draft and normative for store ... postgres declarations in PolyAPI language 0.2 and polyapi.ir/v2. This profile defines the shared database contract; ORM-specific code is an adapter to it, never the schema authority.

Supported slice

The prototype implements the profile in generated Go, Python, Rust, and Java backends.

Target	ORM profile	Status
Go	GORM with the official Postgres driver (pgx underneath)	implemented
Python	SQLAlchemy 2.x ORM with the psycopg 3 dialect	implemented
Rust	SeaORM with sqlx-postgres, Tokio, and generated entity models	implemented
Java	Hibernate/JDBC with the PostgreSQL JDBC driver	implemented

The choices follow the supported Postgres paths documented by GORM, SQLAlchemy, SeaORM, and Hibernate ORM.

The language-neutral packaging and UDS runtime requirements for these targets are specified separately in BACKEND_PROFILE.md.

Store declaration

store quote_store postgres {
  table "polyapi_quote_records";
  key request_id;
  input QuoteRequest;
  value QuoteResponse;
  sink quote_storage;
  replay_labels [stored];
  conflict idempotency_conflict;
  unavailable storage_unavailable;
}

key names a required string field on the input record. The referenced sink must have capability database.persist and exactly two parameters named input and value with the store's declared types. replay_labels are applied to every result because a persist may return a database row written by a previous request. Conflict and unavailable codes are stable API error codes.

The endpoint operation is effectful and returns the authoritative stored value:

persist stored_response = quote_store(input: checked_request, value: response);

Idempotency contract

For one store and key:

1. The first valid request inserts its normalized input and output atomically.
2. The same key plus a JSONB-equal normalized input returns the exact stored output and does not mutate the row.
3. The same key plus a different normalized input fails with HTTP 409 and the declared conflict code.
4. A database/connectivity/ORM failure fails closed with HTTP 503 and the declared unavailable code. There is no in-memory fallback.
5. A stored value that fails its declared sanitizer is treated as corruption and fails closed with HTTP 500.

Postgres JSONB structural equality, not a language runtime's JSON serializer or hash, decides whether inputs match. Object key order and insignificant JSON formatting therefore do not create false conflicts. The generated SHA-256 input_hash is an audit/observability field only; it is not the authority for idempotency decisions.

Both adapters use INSERT ... ON CONFLICT DO NOTHING, commit, then select by key and JSONB-equal input. PostgreSQL serializes conflicting inserts on the primary key, so concurrent first writers converge on one immutable row. The loser either replays that row or receives a conflict.

Every target adapter must implement that same algorithm. The terms "commit, then select" describe the observable transaction boundary: an insert attempt is completed before the authoritative row is read. A target must not implement an ORM save, merge, upsert-update, read-before-write, or process-local lock in its place.

Rust/SeaORM mapping

The Rust adapter generates SeaORM entities for the migration-owned table. idempotency_key and input_hash map to String, input and value map to serde_json::Value through the Postgres JSON type, and created_at maps to an offset-aware timestamp. Schema creation is disabled.

The insert uses SeaQuery/SeaORM's Postgres conflict builder with DO NOTHING. The key, normalized input, and value are bound parameters. The subsequent select binds the key and input JSONB equality value; request data is never SQL text or an identifier. SQLx/SeaORM errors are mapped to the store's stable conflict, unavailable, or corruption behavior without leaking driver text.

Java/Hibernate mapping

The Java adapter generates a Jakarta Persistence entity for the migration-owned table. idempotency_key and input_hash map to String; input and value map to Jackson JsonNode values using Hibernate's JSON JDBC type; and created_at maps to OffsetDateTime. Hibernate schema generation, validation that mutates schema, and automatic update are disabled.

Because the insert is deliberately Postgres-specific, Hibernate may use a compiler-generated native mutation query for INSERT ... ON CONFLICT DO NOTHING. All values are named bound parameters and all identifiers originate from validated IR declarations. The subsequent native or criteria query binds the key and JSONB-equality input. persist, merge, or an ORM-generated update is forbidden for this immutable operation. JDBC/Hibernate exceptions are mapped to stable store errors without leaking SQL or driver text.

Authoritative schema and migrations

The postgres compiler target emits postgres/migrations/001_polyapi.sql beneath the caller's explicit output directory. This checked-in SQL is the only schema owner for an application. Runtime GORM, SQLAlchemy, SeaORM, and Hibernate adapters must not run auto-migration or schema synchronization.

Each store table has this logical envelope:

Column	Contract
idempotency_key text	primary key; equal to the configured input key field
input jsonb	normalized, schema-valid input
value jsonb	exact schema-valid response to replay
input_hash text	database-generated lowercase SHA-256 of canonical JSONB text
created_at timestamptz	database-generated creation time

Generated immutable validator functions enforce record shape, required fields, list cardinality, primitive types, integer bounds, and field constraints for direct SQL writers. The table also checks key/input consistency and, when the value has the same string field, key/value consistency. A trigger rejects UPDATE and DELETE; retries never overwrite the first result.

Compose creates a separate polyapi_app login before migration, applies the migration as the database owner, then grants the application only SELECT and INSERT on generated store tables. The generated Go and Python processes never receive the owner credential. Compose applies the migration only while initializing a fresh Postgres volume; for the PostgreSQL 18 image the named volume is mounted at /var/lib/postgresql so the image can manage its major-version-specific data directory. Production deployment needs separately provisioned migration and application roles plus a single explicit migration job before new application instances start; application startup is not a migration owner.

Security flow

Persistence is both a sink and a possible source:

• before insert, the verifier checks the store's database.persist sink policy against the input and value security states;
• after persistence, the result inherits input/value labels, gains every replay_labels entry, and loses contextual proofs;
• an endpoint must run any sanitizer needed to re-establish proofs required by its next sink, such as http.response.

ORM query parameters are always bound values. Table/column names originate only from compiler-validated declarations and are never request controlled. Generated adapters do not log SQL or database URLs, and the public introspection endpoints do not expose credentials.

Runtime configuration and transport

DATABASE_URL is the single credential-bearing setting used by every backend. POLYAPI_DATABASE_CONNECT_TIMEOUT bounds startup connection checks. The URL is passed directly to GORM; Python rewrites postgres:// or postgresql:// to the explicit postgresql+psycopg:// SQLAlchemy dialect form without changing the credential payload.

The public proxy still speaks to generated API backends only over private Unix domain sockets. Postgres connectivity is a separate provider boundary and may use TCP or a database Unix socket according to DATABASE_URL.

Required conformance tests

A conforming implemented target must pass, against a fresh migrated database:

• first write, same-language replay, and cross-language replay;
• same key/different input conflict in both routing directions;
• concurrent identical and conflicting first writers;
• database-unavailable fail-closed behavior;
• direct SQL rejection for malformed input/value and update/delete attempts;
• schema/hash/key consistency inspection after successful writes.

The auth v3 example exercises the same generated Postgres function surface from all four language adapters. The quote v2 example remains intentionally scoped to its Go/Python differential corpus.