RFC-0051 · PostgreSQL Storage Adapter
Versioned draft snapshot
RFCs, reference catalogs, and evidence reflect Spec 3b2e3e7a114fb826b1445d39a48756b8fff629cd. Counts and pending/status notes belong to that snapshot. Read the dated project update for later progress.
Source repositories are private during staging. Links marked “repository access required” need access to the linked repository.
| Status | Draft corpus. Reference catalogs and RDF projections have generation drift checks; Gnomon proofs and projection semantics remain unverified for release. |
|---|
View the generated reference catalog →
Read design questions and decisions →
Reference catalog: the diagnostics, linked-RFC graph, defined terms, and normative-rule index for this RFC are in the reference catalog, generated from the structured Lean module. This document carries the prose: design rationale, entity-shape definitions in context, and migration notes.
1. Purpose
RFC-0037 defined the abstract GraphBackend service contract — eight services, query-as-data, transaction primitive — without specifying any concrete implementation. This RFC specifies the PostgreSQL implementation: which schemas, which tables, the RLS contract, the GUC session-context binding, the audit trigger framework, the reserved-namespaces enforcement, the dedicated-PG-roles model, and the Gnomon-type → Postgres-type mapping.
This RFC is one of multiple possible adapter implementations. Future RFCs MAY specify SQLite, DuckDB, in-memory, or distributed-KV adapters — they implement the same RFC-0037 contract differently. PostgreSQL is the canonical adapter for hosted Gnomon deployments and the one against which all integration tests run.
This RFC supersedes the assumed Postgres specifics that RFC-0037 hand-waves at, and is the foundation for the platform module catalog (RFC-0052), the view-mapping layer (RFC-0053), and the DDL emission pipeline (RFC-0054).
2. Definitions
| Term | Meaning |
|---|---|
| Adapter | The Postgres-specific implementation of RFC-0037’s GraphBackend interfaces. |
| Platform schema | One of the six PostgreSQL schemas this adapter installs at bootstrap: graph, audit, auth, ids, queries, webhook. |
| Module schema | A PostgreSQL schema named after a module’s namespace (savvi.studio.team → savvi_studio_team), used as the container for the module’s view-mapping outputs (RFC-0053). |
| GUC | A PostgreSQL “Grand Unified Configuration” parameter — session-bound key-value state. Used to carry runtime context (principal, tenant, admin flag, template marker). |
| Bootstrap.sql | The committed SQL artifact (KP24) that creates the minimal Phase-1 schema needed before the install pipeline can run. |
| Reserved namespace | An ltree path under which only privileged paths may create gnomons (graph.*, core.*). |
3. PostgreSQL Extensions
The adapter requires the following PostgreSQL extensions to be available:
| Extension | Purpose |
|---|---|
ltree | The kind/predicate path system (RFC-0036 §3.2). Load-bearing — the adapter cannot function without it. |
pgcrypto | SHA-256 hashing for argHash (RFC-0047, RFC-0050 §4.2), HMAC for cursor tokens (RFC-0038 §5.2), session signing keys. |
uuid-ossp | UUIDv1, v3, v4, and v5 generation for legacy identifiers. It does not provide UUIDv7. |
hstore | Internal use by some legacy tables; non-load-bearing for the data model but expected by ids schema. |
The adapter MUST refuse to boot if any required extension is unavailable (M51010). Extensions MUST be installed by a privileged operator before the adapter’s first boot — they are NOT installable from within the adapter’s transaction.
4. Platform Schemas
The adapter installs six platform PostgreSQL schemas. Each is owned by a dedicated PG role (KP28).
| PG schema | Owner role | Module | Purpose |
|---|---|---|---|
graph | studio_graph_owner | db.graph | Resource, statement, predicate, predicate_policy, permission_cache, external_link tables; CRUD/traversal/collection/permission functions |
audit | studio_audit_owner | db.audit | audit.events table + install_audit_trigger(regclass) framework |
auth | studio_auth_owner | db.auth | Sessions, secrets, JWT verification, key rotation |
ids | (no specific owner; functions are SECURITY DEFINER) | db.ids | Distributed ID generators, generator_metadata, domain types |
queries | studio_queries_owner | db.queries | Shared query subsystem |
webhook | studio_webhook_owner | db.webhook | Webhook providers, endpoints, event mappings |
The db.* modules that own these schemas are catalogued in RFC-0052.
4.1 Schema-namespace correspondence
The adapter establishes a 1:1 correspondence between Gnomon namespaces and PG schemas for application module views:
Gnomon namespace PG schema
savvi.studio.team ↔ savvi_studio_team
savvi.studio.product ↔ savvi_studio_product
ext.clickup ↔ ext_clickup
Dots become underscores; the schema is created at install time when the first view-mapping output (RFC-0053) for the module is emitted. Empty modules (no entity types) get no PG schema.
4.2 Schema ownership
Module schemas are owned by role_<module_namespace_underscored> (e.g., role_savvi_studio_team). The install engine (RFC-0041) SET ROLE to the module’s role before executing module-scoped DDL. This is the SH3 enforcement (KP28) for module isolation:
SET ROLE role_savvi_studio_team;
-- All DDL emitted by this module's compile pipeline
RESET ROLE;
A module’s role can write to its own schema and read from graph.* (RLS-filtered) but cannot write to other modules’ schemas. Cross-module reads via shared views go through graph.* and the RLS layer — never via direct schema access.
4.3 Development fallback
In development, dedicated roles per module are operationally heavy. The adapter MAY use a single studio_app_connector role + SET LOCAL gnomon.current_module = '<namespace>' GUC + RLS/trigger checks (KP28 fallback). Production MUST use dedicated roles.
5. The graph.resource and graph.statement Tables
Verbatim from the legacy db/migrations/000_initial_schema/050_graph.sql:
CREATE TABLE graph.resource (
id BIGINT PRIMARY KEY DEFAULT ids.gen_resource_id(),
kind LTREE NOT NULL,
external_id TEXT,
data JSONB NOT NULL DEFAULT '{}'::JSONB,
creator_id BIGINT REFERENCES graph.resource (id) ON DELETE SET NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
deleted_at TIMESTAMPTZ,
CONSTRAINT unique_resource UNIQUE (kind, external_id)
);
CREATE INDEX idx_resource_kind ON graph.resource USING gist (kind);
CREATE INDEX idx_resource_external_id ON graph.resource (external_id) WHERE external_id IS NOT NULL;
CREATE INDEX idx_resource_data ON graph.resource USING gin (data);
CREATE INDEX idx_resource_creator ON graph.resource (creator_id) WHERE creator_id IS NOT NULL;
CREATE INDEX idx_resource_active ON graph.resource (id) WHERE deleted_at IS NULL;
CREATE TABLE graph.statement (
id BIGINT PRIMARY KEY DEFAULT ids.gen_statement_id(),
subject_id BIGINT NOT NULL REFERENCES graph.resource (id) ON DELETE CASCADE,
predicate_id BIGINT NOT NULL REFERENCES graph.resource (id) ON DELETE RESTRICT,
object_id BIGINT NOT NULL REFERENCES graph.resource (id) ON DELETE CASCADE,
data JSONB NOT NULL DEFAULT '{}'::JSONB,
creator_id BIGINT REFERENCES graph.resource (id) ON DELETE SET NULL,
valid_from TIMESTAMPTZ NOT NULL DEFAULT now(),
valid_to TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT unique_statement UNIQUE (subject_id, predicate_id, object_id)
);
These tables are normative for the PostgreSQL adapter. The Gnomon data model (RFC-0036 §4) maps directly:
| RFC-0036 field | PG column | Notes |
|---|---|---|
id | id BIGINT | Snowflake ID from ids.gen_resource_id() / ids.gen_statement_id() |
kind | kind LTREE | Indexed via gist |
externalId | external_id TEXT | Indexed when present |
data | data JSONB | Indexed via gin for arbitrary key queries |
creatorId | creator_id BIGINT | FK to graph.resource |
createdAt | created_at TIMESTAMPTZ | Default now() |
updatedAt | updated_at TIMESTAMPTZ | Maintained by trigger |
deletedAt | deleted_at TIMESTAMPTZ | Null = active |
subjectId/predicateId/objectId | corresponding *_id BIGINT | FK with ON DELETE CASCADE for subject/object, ON DELETE RESTRICT for predicate |
validFrom/validTo | valid_from/valid_to TIMESTAMPTZ | Edge-level bitemporal |
6. Distributed ID Generation
6.1 The ids schema
ids.gen_resource_id() and ids.gen_statement_id() are Snowflake-style 64-bit ID generators implemented in plpgsql:
id = (timestamp_bits * 2^(machine_bits + sequence_bits))
+ (machine_id * 2^sequence_bits)
+ sequence
Default config (legacy parity):
| Component | Bits | Range |
|---|---|---|
epoch_ms | — | Custom epoch ms-since-Unix-epoch |
timestamp_bits | 41 | ~69 years from epoch |
machine_bits | 10 | 1024 machines |
sequence_bits | 12 | 4096 ids per ms per machine |
Sum of timestamp + machine + sequence MUST equal 63 (one bit reserved for sign — IDs are positive BIGINT).
6.2 Per-kind generators
A new generator can be created via ids.create_generator(name, config). The legacy db.graph declares resource_id and statement_id; new generators MAY be declared by other db.* modules for their own ID streams (e.g., audit_event_id).
6.3 ID ranges and module isolation
The install engine (RFC-0041) MAY allocate ID ranges per module to enable offline plan computation: each module’s machine_id is set to a deterministic value derived from the module’s manifest hash. This is optional; the default single-machine-id is correct for non-distributed deployments.
7. RLS Contract
7.1 Session GUCs
The adapter lists five PostgreSQL GUC parameters used by the runtime and install pipeline:
| GUC | Type | Set by | Purpose |
|---|---|---|---|
app.current_user_id | BIGINT | Runtime at request entry (RFC-0048) | Bound principal id; RLS reads from this |
app.current_user_type | LTREE | Runtime at request entry | Bound principal kind (e.g., identity.user) |
app.is_admin | BOOLEAN | Runtime at request entry | True if session is admin-elevated |
gnomon.current_module | TEXT | Install engine per-module | Active install-module namespace (KP28 fallback) |
gnomon.template_marker | TEXT | Template executor (RFC-0050 §3.3) | Active template invocation marker |
The adapter exposes graph.set_session_user(user_id, user_type, is_admin) to set the first three atomically. The runtime calls this at the start of every request transaction.
7.2 Permission evaluation
graph.has_permission(subject_id, subject_type, resource_id, resource_kind, required_bits) is the single permission oracle. Implementation:
1. If is_admin_session() → return true.
2. If subject_id is the resource's creator → grant write/admin permissions per workspace policy.
3. For each predicate-policy whose scope matches (resource_kind, resource_id):
find statements where (subject_id, policy.predicate, ...) holds (matchMode-aware)
accumulate policy.permission_bits
4. Return (accumulated_bits & required_bits) == required_bits.
This is the runtime form of the RFC-0042 §4.2 evaluation algorithm. It MUST match the abstract algorithm exactly; divergence is a defect (M51020).
7.3 RLS policies on graph.resource and graph.statement
ALTER TABLE graph.resource ENABLE ROW LEVEL SECURITY;
ALTER TABLE graph.statement ENABLE ROW LEVEL SECURITY;
CREATE POLICY resource_select ON graph.resource
FOR SELECT USING (
deleted_at IS NULL
AND graph.has_permission(graph.current_user_id(), graph.current_user_type(),
id, kind, 1::SMALLINT)
);
CREATE POLICY resource_insert ON graph.resource
FOR INSERT WITH CHECK (
graph.is_admin_session()
OR current_setting('gnomon.template_marker', true) IS NOT NULL
OR (NOT (kind <@ 'graph'::ltree) AND NOT (kind <@ 'core'::ltree))
);
CREATE POLICY resource_update ON graph.resource
FOR UPDATE USING (
graph.has_permission(graph.current_user_id(), graph.current_user_type(),
id, kind, 3::SMALLINT)
);
CREATE POLICY resource_delete ON graph.resource
FOR DELETE USING (
graph.has_permission(graph.current_user_id(), graph.current_user_type(),
id, kind, 3::SMALLINT)
);
Statement policies follow the same pattern, evaluating has_permission on the underlying subject/object resources.
7.4 Kind immutability trigger (SH1)
Per kernel-primitives.md §SH1:
CREATE TRIGGER resource_enforce_kind_immutability
BEFORE UPDATE ON graph.resource
FOR EACH ROW EXECUTE FUNCTION graph.enforce_kind_immutability();
The function rejects any UPDATE that changes kind. Kind immutability is load-bearing for permission caching, RLS, and traversal correctness.
8. Reserved Namespaces
graph.reserved_namespaces carries ltree paths that may not be modified by user code:
graph.* — graph platform schema
core.* — Gnomon structural meta-gnomons
db.* — platform module schemas
gnomon.* — runtime control namespaces
graph.is_reserved_type(kind) checks if a kind is reserved (subtree-match against the table). The trigger prevent_reserved_type_modification rejects UPDATE/DELETE on reserved-type rows.
The reserved-namespaces table is bootstrapped by bootstrap.sql and extended by the db.graph module install. Application modules MUST NOT add entries (M51030).
9. Audit Trigger Framework
9.1 The audit.events table
Every mutating write to a graph table produces a row in audit.events:
audit.events {
id BIGINT PRIMARY KEY DEFAULT ids.gen_audit_event_id(),
table_oid OID NOT NULL,
operation TEXT NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE')),
row_id BIGINT NOT NULL,
before_data JSONB,
after_data JSONB,
principal_id BIGINT,
tenant_id TEXT,
occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
}
9.2 audit.install_audit_trigger(table_oid)
The framework function. Called once per table at install time, it adds a trigger that emits to audit.events on every INSERT/UPDATE/DELETE. The trigger reads app.current_user_id and app.current_tenant_id GUCs to populate the audit row.
The platform schema tables (graph.resource, graph.statement, the predicate-policy tables, etc.) all carry audit triggers from bootstrap. New tables emitted by the DDL pipeline (RFC-0054) get audit triggers automatically when they declare the audit-trail aspect (db.pg.audit-trail).
9.3 Audit retention
Audit events are kept indefinitely by default; partitioning and retention are operator-configured via defaultConfig (RFC-0046). The adapter does NOT auto-purge audit events.
10. PG Type Mapping
The Gnomon type model (RFC-0006) maps to PostgreSQL types per db.pg’s 28-type catalog (q2 plan §sql-ast-track-findings.md §4):
| Gnomon type | PG type | Notes |
|---|---|---|
Text | TEXT | Default; no length limit |
Text(N) | VARCHAR(N) | When length is significant |
Identifier | TEXT | Branded text alias (RFC-0035 §6) |
Bigint | BIGINT | Used for IDs |
Integer | INTEGER | Default integer |
Number | NUMERIC | Arbitrary-precision decimal |
Float | DOUBLE PRECISION | IEEE 754 double |
Boolean | BOOLEAN | |
Instant, Timestamp | TIMESTAMPTZ | UTC by convention; runtime sets timezone = 'UTC' |
Date | DATE | |
Map<K, V> | JSONB | Always stored as JSONB |
List<T> | JSONB (default) or T[] | JSONB for indexability with gin; arrays for high-cardinality numeric lists |
Bytes | BYTEA | |
Uuid | UUID | |
Email, URL, Locale, CurrencyCode | TEXT | Branded text aliases (RFC-0035 §6) |
LtreePath | LTREE | Used for kind, predicate.path |
Money | NUMERIC(20, 4) | Precision is conventional; configurable per module |
The mapping is canonical for the Postgres adapter. Other adapters (SQLite, etc.) provide their own mapping in their own RFC.
11. RFC-0037 Service → PG Function Mapping
This table specifies how each RFC-0037 backend service operation lowers to a PostgreSQL function call:
11.1 ResourceService
| RFC-0037 query | PG call |
|---|---|
Get { id } | graph.get_resource(id) |
GetByExternalId { kind, externalId } | graph.get_resource_by_external_id(kind, externalId) |
Query { filters } | parameterized SELECT FROM graph.resource WHERE ... with cursor |
Create { input } | graph.create_resource(...) |
Update { id, input } | graph.update_resource(id, ...) |
Delete { id } | graph.delete_resource(id) (soft delete) |
11.2 StatementService
| RFC-0037 query | PG call |
|---|---|
Get { id } | graph.get_statement(id) |
Query { filters } | parameterized SELECT FROM graph.statement WHERE ... |
Create { input } | graph.create_statement(...) |
CreateMany { inputs } | graph.batch_create_statements(...) |
Update { id, input } | graph.update_statement(id, ...) |
Invalidate { id } | graph.invalidate_statement(id) |
11.3 PermissionService
| RFC-0037 query | PG call |
|---|---|
GetPermission { params } | graph.has_permission(...) |
GetSubjectPermissions { params } | graph.get_subject_permissions(...) |
11.4 CollectionService
The eight CollectionService operations map to graph.create_list, graph.list_append, graph.list_insert, graph.list_remove, graph.create_set, graph.set_add, etc. — see legacy 050_graph.sql for full signatures.
11.5 TraversalService
| RFC-0037 query | PG call |
|---|---|
GetOutgoing { resourceId, predicatePattern } | graph.get_outgoing(resource_id, predicate_pattern) |
GetIncoming { resourceId, predicatePattern } | graph.get_incoming(resource_id, predicate_pattern) |
Traverse { startId, options } | graph.traverse(start_id, options::JSONB) |
11.6 PredicateService
| RFC-0037 query | PG call |
|---|---|
GetId { predicateUri } | graph.get_predicate_id(predicate_uri) |
11.7 WorkspaceService
Workspace operations are read-only views over graph.resource filtered to kind <@ 'workspace'::ltree. The legacy adapter implements them as standalone PG functions; this RFC normatively maps them to projection-style read queries (RFC-0049) with materialize: live.
11.8 TransactionService
| RFC-0037 method | PG equivalent |
|---|---|
begin() | BEGIN (returns a session-scoped tx-id from pg_current_xact_id()) |
commit(id?) | COMMIT |
rollback(id?) | ROLLBACK |
transaction(callback) | savepoint-aware wrapper |
12. Bootstrap.sql Contract
12.1 Purpose
bootstrap.sql is a frozen, committed SQL artifact that creates the minimal Phase-1 schema needed before the install pipeline can run (KP15, KP24). On a fresh database, it’s the first SQL that executes; the install pipeline then runs and observes install(db.graph, bootstrap_db) = no_change.
12.2 Contents
bootstrap.sql contains:
CREATE EXTENSIONforltree,pgcrypto,uuid-ossp,hstore.- The six platform schemas + their owner roles.
- The
ids.gen_resource_id()andids.gen_statement_id()functions. graph.resource,graph.statement,graph.predicate_policy,graph.reserved_namespacestables (plus their indexes).graph.has_permission(),graph.set_session_user(),graph.is_admin_session()functions.- The bootstrap RLS policies.
- The audit framework (
audit.events,audit.install_audit_trigger()). - Initial reserved-namespace rows (
graph,core,db,gnomon). - The system installer principal record.
12.3 Generation
bootstrap.sql is generated from the db.graph, db.audit, and db.ids modules’ compiled output by the DDL emission pipeline (RFC-0054). It is committed to source like a lockfile.
CI MUST regenerate from current source and fail on diff (M51040). Operators reviewing graph-substrate changes see the SQL diff explicitly.
12.4 Versioning
bootstrap.sql is tagged with the formatVersion field (RFC-0002). Changes to the Phase-1 schema bump the format version. The runtime refuses to boot if the bootstrapped DB’s format version is incompatible (M51011).
13. Invariants
- The adapter requires
ltree,pgcrypto,uuid-ossp,hstoreextensions (§3, M51010). - The six platform schemas exist with their declared owner roles after bootstrap (§4).
graph.resourceandgraph.statementschemas match the legacy DDL exactly (§5).- IDs are generated by
ids.gen_*_id()Snowflake functions (§6). - Session GUCs (
app.current_user_id, etc.) are set at the start of every request transaction (§7.1). graph.has_permissionmatches RFC-0042 §4.2 evaluation algorithm exactly (§7.2, M51020).kindis immutable after creation; SH1 trigger enforces (§7.4).- RLS gates direct INSERT into
graph.*andcore.*kinds without a template marker (§7.3, ties to RFC-0050 §9.2). bootstrap.sqlis a generated artifact regenerated by CI and verified by diff (§12.3, M51040).- RFC-0037 service queries map deterministically to PG function calls per §11.
14. Compatibility and Migration
14.1 From the legacy schema
The legacy schema at /Volumes/Workspace/savvi-studio-full/db/migrations/000_initial_schema/ is a faithful subset of what this RFC normatively requires:
| Legacy file | This RFC |
|---|---|
000_prelude.sql | §3 (extensions) |
010_ids.sql | §6 (ID generators) |
020_audit.sql | §9 (audit framework) |
040_auth.sql | RFC-0048 (auth schema) — adapter consumes its tables |
050_graph.sql | §5, §7, §8 (graph schema, RLS, reserved namespaces) |
051_graph_policies.sql | §7 (policy tables — see RFC-0042) |
060_webhook.sql | RFC-0052 (webhook module) |
070_util.sql | RFC-0052 (db.util module) |
999_grants.sql | derived from §4.2 (role hierarchy) |
Existing deployments need no schema migration; the legacy schema satisfies this RFC.
14.2 New requirements
gnomon.template_markerGUC is new (§7.1). The runtime MUST be updated to set/clear it around template executions (RFC-0050 §3.3).- Updated
resource_insertpolicy to gate ontemplate_markerpresence (§7.3) — replacing the legacy permissive INSERT. - Format-versioned bootstrap.sql (§12.4) — legacy bootstrap is unversioned; this RFC requires the version field and CI diff check.
14.3 Multi-tenancy
The adapter supports single-tenant and multi-tenant deployments. In multi-tenant mode (GNOMON_TENANT_MODE=multi per RFC-0045), an app.current_tenant_id GUC scopes all queries; tenant-scoped RLS predicates are AND’d into the policies in §7.3.
The full tenancy model is the subject of a future RFC (multi-tenancy & isolation, mentioned in docs/spikes/proposal-runtime-and-persistence-rfcs.md (see spec source)).
Diagnostics
The diagnostic catalog for this RFC is authored in the structured Lean module and
rendered in reference catalog § Diagnostics. Inline mentions in
the prose above (e.g., “rejected with MNNxxx”) cross-link to the catalog
at render time.
Linked References
The cross-RFC dependency graph is rendered in reference catalog § Linked References. Out-of-KG pointers to legacy code or external resources, when present, remain in this document under “Source-tree references” below.