PostgreSQL Cluster-Safe Schema

Single-primary and physical HA compatibility are v2 requirements. Distributed-by-team execution remains a future capability-gated adapter, not a claim of shipped cluster support.

This page is the single source of truth for Dense-Mem's cluster-safe PostgreSQL key, constraint, table-placement, query-routing, queue, and topology-specific RLS shape. Semantic field meaning remains in Data Model And Storage, Entity identity in Entity Identity And Resolution, Relationship identity/lifecycle in Relationship Model And Lifecycle, and principal/authorization semantics in Request Lifecycle And Security. Exact/ANN topology and index behavior remains in ANN, HNSW, And Halfvec.

Outcome And Boundary

The V2 schema runs unchanged as ordinary tables on one PostgreSQL primary and through a physical-HA writer endpoint. Its tenant-owned rows can later be distributed and colocated by team_id without changing semantic identities, rewriting row ownership, or adding a missing tenant column to populated tables.

Cluster-safe does not mean cluster-enabled:

cluster-safe schema
  = keys, constraints, queries, jobs, and RLS preserve one-team locality
​
supported distributed adapter
  = one named product/version also passes DDL, RLS, pgvector, backup,
    rebalance, failover, and exact-versus-ANN integration gates

The initial implementation must not import a distributed-database SDK into the domain layer. PostgreSQL adapters expose the same team-scoped repository ports whether the configured writer endpoint is standalone, HA-managed, or a future distributed coordinator.

Logical multi-primary writes are outside this contract. Physical HA may move the writer role, and a future distributed topology may have many shard writers, but one team routes to one authoritative writer for a transaction.

Before And After

PostgreSQL logical schema
├── coordinator control
│   ├── authentication / SSO / secret references
│   ├── global configuration
│   └── migration and topology control
├── shared reference
│   ├── predicate and model/search definitions
│   └── immutable active-version metadata
└── tenant-owned by team_id
    ├── non-secret team/profile integrity projections
    ├── semantic state and append-only history
    ├── placement/review/import queues
    ├── search documents and embedding jobs
    └── feedback, dream/community, and tenant audit

Delivery Split And Lift

%%{init: {"flowchart": {"curve": "linear"}}}%%
flowchart LR
  Contract["V2 now<br/>tenant keys, FKs, queries, jobs, RLS"] --> Single["Ship and measure<br/>single primary + physical HA"]
  Single --> Qualify["Later<br/>select and qualify one distributed product/version"]
  Qualify --> Place["Add adapter placement DDL<br/>and topology operations"]

The required V2 implementation boundary is the first three rows. It includes topology-neutral repository ports and configuration, but no vendor distribution functions or SDK. The later distributed adapter may add physical placement and routing; it may not weaken or reinterpret the logical contract.

One-Team Transaction Invariant

Every normal memory operation has one authenticated team before it reaches a repository. Its semantic reads and writes stay inside that team.

%%{init: {"flowchart": {"curve": "linear"}}}%%
flowchart LR
  Principal["Authenticated principal"] --> Team["Fixed team_id"]
  Team --> Tx["One PostgreSQL transaction"]
  Tx --> Rows["Only rows carrying that team_id"]
  Rows --> Local["Single primary now<br/>one colocated shard later"]

Normal paths include remember intake, placement, review resolution, Entity correction, Relationship lifecycle, recall, trace, feedback, dreaming, community work, imports, and tenant audit. They never open a transaction that mutates semantic rows for two teams.

Cross-team control operations enumerate teams from the control plane and invoke one independently committed, idempotent per-team operation. They do not create distributed semantic transactions.

Table Placement Classes

The logical schema classifies each table before a distributed adapter maps it to product-specific placement.

Tenant-owned families

The following logical families carry non-null team_id from creation:

• semantic_team_refs and semantic_profile_refs, which project only stable control-plane ownership IDs for tenant-local integrity
• ingests, evidence fragments, placement runs/items, and review tasks
• Entity records, Entity names, Entity resolution/correction events
• typed Value records
• Relationship observations, verification events, current records, evidence support/decision rows, transitions, and cross-references
• Hypotheses and their review/run rows
• search documents and embedding jobs
• recall events/feedback and tenant-scoped evaluation run records
• memory-pack import runs/change ledgers
• team community/dream schedules and results
• team audit and operation events

No tenant-owned table uses nullable team_id. An event with no team belongs in a separate system/control table rather than a tenant table with team_id=NULL.

Shared-reference families

Small immutable/versioned definitions required inside team-local queries may be replicated by a future adapter:

• predicate registry versions and immutable definitions
• embedding contracts
• search-index and global/default ranking profile definitions
• verifier/reviewer closed-contract definitions and hashes
• closed enum/reference definitions that cannot differ by shard

Reference data activation follows a cluster-wide barrier. A predicate, embedding, search-index, or ranking version cannot become active until every required database node can read the same immutable version.

Team-specific profile-selection/override rows remain tenant-owned and carry team_id; only their immutable shared definitions are reference data.

Coordinator-control families

Records needed before an authenticated team_id exists remain in the control plane:

• authoritative teams and team_profiles, including credential and SSO fields
• API-key hash/prefix lookup and revocation directory
• SSO providers, identities, sessions, and entitlement mappings
• encrypted-secret references
• global app_config and topology declarations
• schema migrations, bridge migration runs, team placement epochs, and cluster build/activation records
• global scheduler leadership and redacted system audit

Authentication resolves key/session to team, profile, role, and scopes before opening a tenant semantic transaction. The hot semantic query does not join a distributed memory table to the global key or SSO tables.

Existing V1/V2 control credentials remain in the current PostgreSQL database. The V2 semantic migration does not copy or reissue them merely to prepare for future sharding.

The existing teams and team_profiles rows remain the only identity, authorization, and credential authority. V2 adds only these non-secret integrity projections:

CREATE TABLE semantic_team_refs (
  team_id uuid PRIMARY KEY
);
​
CREATE TABLE semantic_profile_refs (
  team_id uuid NOT NULL,
  profile_id uuid NOT NULL,
  PRIMARY KEY (team_id, profile_id),
  FOREIGN KEY (team_id)
    REFERENCES semantic_team_refs (team_id)
);

On one primary, team/profile creation writes the authority and its integrity projection in the same transaction. A future distributed adapter routes the projection to the team-owning shard and must record it ready before enabling semantic writes for that team/profile. Projection rows carry no hashes, prefixes, scopes, roles, SSO claims, or mutable entitlement decisions. They are retained while semantic history can reference them; runtime authorization always reads the control authority. Readiness reconciliation rejects an orphan projection, a missing projection, or a (team_id, profile_id) pair that does not match the control authority; the semantic adapter never repairs one by inference.

Tenant-Owned Key Contract

Every tenant-owned row has:

team_id       UUID NOT NULL
record_id     opaque UUID or other bounded opaque ID generated before insert

Its relational identity starts with team_id:

PRIMARY KEY (team_id, record_id)

The opaque record ID remains the public handle, but it is never sufficient for a database lookup. The authenticated team is always the first key:

SELECT ...
FROM relationship_records
WHERE team_id = $1
  AND relationship_id = $2;

Repository signatures make omission difficult:

GetRelationship(ctx context.Context, teamID, relationshipID string)
ListEntityRelationships(ctx context.Context, teamID, entityID string, limit int)

There is no unscoped GetRelationship(ctx, relationshipID) implementation for normal application code.

Unique constraints

Every tenant-owned unique constraint contains team_id, even when IDs are random enough to be globally collision-resistant:

UNIQUE (team_id, owner_profile_id, relationship_identity_hash)
UNIQUE (team_id, value_type, canonical_value_hash)
UNIQUE (team_id, ingest_id, evidence_index)

Detailed Relationship and Value identity fields remain owned by their semantic pages. This rule only adds the tenant prefix.

A genuinely global uniqueness requirement belongs in a coordinator-control or shared-reference table. API-key prefixes and provider names must not force a cross-shard unique constraint on semantic tables.

Foreign keys

Every foreign key between tenant-owned tables carries the same team_id on both sides:

FOREIGN KEY (team_id, subject_entity_id)
  REFERENCES entity_records (team_id, entity_id)
​
FOREIGN KEY (team_id, relationship_id)
  REFERENCES relationship_records (team_id, relationship_id)

This makes a cross-team reference structurally impossible, not merely hidden by RLS. Cross-profile references are allowed only when both Relationships share the same composite team key.

Profile ownership uses a composite reference to the non-secret integrity projection:

FOREIGN KEY (team_id, owner_profile_id)
  REFERENCES semantic_profile_refs (team_id, profile_id)

The projection preserves the existing team/profile IDs without replicating the secret-bearing team_profiles table. A future distributed adapter colocates it with the semantic rows by team_id and must prove its distributed foreign-key behavior; it cannot silently drop ownership or same-team integrity or promote this projection into a second profile authority.

Representative Semantic Shape

This fragment demonstrates tenant keys and references only. Canonical semantic fields and lifecycle constraints remain in their owning pages.

CREATE TABLE entity_records (
  team_id uuid NOT NULL,
  entity_id uuid NOT NULL,
  entity_kind text NOT NULL,
  version bigint NOT NULL,
  PRIMARY KEY (team_id, entity_id)
);
​
CREATE TABLE relationship_records (
  team_id uuid NOT NULL,
  relationship_id uuid NOT NULL,
  owner_profile_id uuid NOT NULL,
  subject_entity_id uuid NOT NULL,
  object_entity_id uuid,
  object_value_id uuid,
  predicate_key text NOT NULL,
  version bigint NOT NULL,
  PRIMARY KEY (team_id, relationship_id),
  FOREIGN KEY (team_id, subject_entity_id)
    REFERENCES entity_records (team_id, entity_id),
  FOREIGN KEY (team_id, object_entity_id)
    REFERENCES entity_records (team_id, entity_id),
  CHECK ((object_entity_id IS NULL) <> (object_value_id IS NULL))
);
​
CREATE INDEX relationship_team_subject_idx
  ON relationship_records (team_id, subject_entity_id, predicate_key);

The real schema adds the composite Value and profile references, owner-scoped semantic uniqueness, polarity/scope/time identity, append-only ledgers, partial eligibility indexes, and RLS from their canonical contracts.

Search And Embedding Shape

Search rows remain team-owned even though embedding/search-index profiles are shared reference data:

CREATE TABLE search_documents (
  team_id uuid NOT NULL,
  source_kind text NOT NULL,
  source_id uuid NOT NULL,
  embedding_contract_id uuid NOT NULL,
  embedding_dimensions integer NOT NULL,
  source_version bigint NOT NULL,
  document_version bigint NOT NULL,
  search_state text NOT NULL,
  embedding vector,
  PRIMARY KEY (
    team_id,
    source_kind,
    source_id,
    embedding_contract_id
  ),
  CHECK (
    embedding IS NULL
    OR vector_dims(embedding) = embedding_dimensions
  )
);

Every exact/ANN query includes team_id. A future row-sharded adapter routes it to the team-owning shard before the shard-local pgvector index runs. HNSW expression, filtering, and index-profile rules belong in ANN, HNSW, And Halfvec.

Embedding work is also tenant-local:

PRIMARY KEY (team_id, embedding_job_id)
UNIQUE (
  team_id,
  source_kind,
  source_id,
  source_version,
  embedding_contract_id
)

An embedding worker never needs to scan or lock every team's queue in one transaction. The worker first picks a ready team, then claims a bounded batch for that team with FOR UPDATE SKIP LOCKED. A provider batch never mixes teams, which keeps request accounting, failure isolation, and future team-shard routing tenant-local.

Index Contract

Tenant-owned B-tree, GIN, and adjacency indexes lead with team_id when the query is team-scoped:

(team_id, record_id)
(team_id, status, created_at)
(team_id, subject_entity_id, predicate_key)
(team_id, object_entity_id, predicate_key)
(team_id, owner_profile_id, updated_at)

This is useful on one primary and lets a future coordinator prune/route work. Do not add one physical partition or partial index per small team; that creates catalog/planner overhead and is not a substitute for a measured distribution strategy.

HNSW is the exception to the ordinary composite-index shape because pgvector's ANN index is over the vector expression. team_id remains in the query/RLS, and the distributed topology reduces the candidate universe to one owning shard. Exact/ANN filtering risks and large-team placement remain owned by the ANN page.

Join And Repository Contract

Every join between tenant-owned tables includes equality on team_id:

SELECT relationship.relationship_id, subject.entity_id
FROM relationship_records AS relationship
JOIN entity_records AS subject
  ON subject.team_id = relationship.team_id
 AND subject.entity_id = relationship.subject_entity_id
WHERE relationship.team_id = $1
  AND relationship.relationship_id = $2;

Requirements:

1. request/application code fixes team_id before repository entry
2. repository SQL uses an explicit team predicate even when RLS also applies
3. every tenant join repeats the team equality
4. ID lists remain paired with one team; a list from one team cannot hydrate against another
5. pagination cursors include team scope indirectly through authenticated route and are rejected under a different team
6. no latency-sensitive semantic path executes cross-team aggregation

Cross-team management reports use coordinator/control queries or explicit bounded per-team fan-out. They are not recall or placement repository methods.

RLS And Worker Context

RLS is defense in depth, not the shard router. Tenant-owned tables use forced RLS plus explicit predicates:

ALTER TABLE relationship_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE relationship_records FORCE ROW LEVEL SECURITY;
​
CREATE POLICY relationship_team_policy ON relationship_records
USING (
  team_id = nullif(
    current_setting('app.current_team_id', true),
    ''
  )::uuid
)
WITH CHECK (
  team_id = nullif(
    current_setting('app.current_team_id', true),
    ''
  )::uuid
);

A missing/invalid team setting fails closed. Pooled connections set it with transaction-local set_config, never session-global state.

Normal internal workers also run with one explicit team_id. An unrestricted system mode is reserved for migrations and narrow audited control operations; it is not the default placement, embedding, dream, community, or cleanup mode.

A future distributed adapter must prove that roles, policies, set_config, and FORCE ROW LEVEL SECURITY behave on owning workers. If its coordinator cannot guarantee that contract, the adapter is unsupported even when queries appear to route correctly.

Durable Queue And Scheduler Shape

Queue truth remains PostgreSQL. Every tenant work row and idempotency key carries team_id:

%%{init: {"flowchart": {"curve": "linear"}}}%%
flowchart LR
  Control["Global scheduler<br/>or wake signal"] --> Team["Select bounded team/shard"]
  Team --> Claim["Claim team-local work<br/>FOR UPDATE SKIP LOCKED"]
  Claim --> Run["Run provider/domain work"]
  Run --> Commit["Commit one-team result"]

The scheduler first selects a bounded team from rebuildable per-team work summaries/signals, then calls a repository claim that requires that team_id. The team-local queue index starts with (team_id, status, available_at). A future distributed adapter performs the same claim on one shard/team routing context at a time. It does not promise a globally exact oldest-job ordering across shards.

Small coordinator summaries or Redis signals may indicate which shard has work, but they are derived hints. Losing them cannot lose, complete, or reassign the durable PostgreSQL job.

Fairness is enforced through bounded per-team concurrency, round-robin/deficit scheduling, oldest-age alerts, and hard global worker caps. One noisy team cannot consume every placement or embedding worker indefinitely.

Version And Activation Barrier

Schema, predicate, embedding, search-index, ranking, and verifier versions are immutable records with one explicit active pointer per applicable scope.

%%{init: {"flowchart": {"curve": "linear"}}}%%
flowchart LR
  Build["Create immutable version"] --> Apply["Apply to primary/reference<br/>or every owning shard"]
  Apply --> Verify["Verify constraints, RLS,<br/>plans and checksums"]
  Verify --> Ready{"Every required node ready?"}
  Ready -- No --> Old["Keep old active version"]
  Ready -- Yes --> Activate["Atomically change active pointer"]

An HA standby may serve qualified reads only after replaying the active pointer and required schema/index state. A distributed profile cannot activate when one shard reports an absent or incompatible version.

Single, HA, And Distributed Mapping

The schema does not use product-specific distribution functions in baseline migrations. A named distributed adapter owns additive placement DDL after it passes qualification. This keeps ordinary PostgreSQL and physical HA as first- class deployments.

That adapter also owns a coordinator-control placement record containing the team, opaque target placement, epoch, and transition state. Routing and shard movement are fenced by the epoch; application repositories never guess a shard or retry a write against another placement.

Migration From Existing PostgreSQL

V2.1.x preserves the existing teams, profiles, API keys, SSO, and control rows. New semantic/workflow/search tables are created cluster-safe from their first release.

For every legacy corpus item, the bridge already knows team and original owner. The official write path supplies that team_id to every new tenant row; no semantic row may rely on a later tenant backfill.

Migration hard gates include:

zero NULL team_id in tenant-owned tables
zero tenant PK/unique constraints missing team_id
zero tenant foreign keys missing matching team_id
zero cross-team endpoint/support/cross-reference rows
zero normal repository queries that hydrate by ID without team_id
every tenant queue/idempotency key includes team_id
every required team/profile preserved
every preserved team/profile has its non-secret integrity projection
zero orphan or team-mismatched integrity projections

Distributed conversion is not part of the legacy Neo4j bridge by default. An installation may migrate to V2 on one primary/HA, validate it, and later run a separate qualified database-topology migration without reinterpreting knowledge through the AI verifier.

Failure Semantics

Risks And Mitigations

Acceptance Invariants

1. Every tenant-owned V2 row has non-null team_id from its first insert.
2. Every tenant-owned primary key, unique constraint, foreign key, hot join, and normal lookup contains team_id.
3. Composite foreign keys make cross-team semantic references impossible.
4. Authentication resolves team/profile before a semantic repository call.
5. API key/SSO/control lookup never requires semantic-shard fan-out.
6. Normal semantic transactions read/write exactly one team.
7. One placement epoch routes that team transaction to one authoritative writer; stale routes fail instead of falling back.
8. Normal workers claim and commit with explicit team context.
9. Cross-team administration decomposes into bounded idempotent per-team work.
10. RLS is forced and fails closed in addition to explicit query predicates.
11. One-primary and physical-HA deployments need no product-specific sharding extension.
12. A distributed adapter is supported only after named product/version gates prove key/FK, RLS, pgvector, queue, migration, backup, rebalance, and failover behavior.
13. Existing teams, profiles, keys, SSO, and control records remain stable through V2 semantic migration.
14. Non-secret integrity projections validate team/profile ownership but never authorize a request or duplicate credential/entitlement authority.
15. Future distribution by team_id requires placement DDL and data movement, but not semantic ID changes, AI re-verification, or tenant backfill.

Primary References

• PostgreSQL constraints
• PostgreSQL row security
• Citus distributed/reference/local table concepts
• Citus multi-tenant data model

These references describe capabilities and constraints. Dense-Mem does not claim Citus or another sharding product is supported until its exact selected version passes this page's adapter gates.