Data Model And Storage

This page is the single source of truth for Dense-Mem's target storage architecture. It defines the PostgreSQL and pgvector design for semantic memory v2.

Tenant-owned primary/unique keys, composite foreign keys, table-placement classes, team-local repository queries, RLS worker context, and future sharding readiness belong only in PostgreSQL Cluster-Safe Schema.

PostgreSQL is the only required database. It owns durable knowledge, workflow, security state, graph-shaped reads, full-text search, and vector search. pgvector is a PostgreSQL extension, not another source of truth. Redis is an optional coordination layer and stores no memory.

Store Responsibilities

%%{init: {"flowchart": {"curve": "linear"}}}%%
flowchart TB
  Service["Dense-Mem services"] --> PG[("PostgreSQL<br/>only durable store")]
  PG --> Ledger["Evidence and append-only history"]
  PG --> Current["Entity and Relationship current state"]
  PG --> Graph["Semantic nodes and edges<br/>through SQL/API views"]
  PG --> Search["Full-text and pgvector search"]
  PG --> Control["Identity, security, workflow, audit"]
  Service -. "optional" .-> Redis[("Redis<br/>coordination only")]
  Redis --> Ephemeral["Rate limits, leases,<br/>SSE and short-lived signals"]

One process may use in-memory coordination. Multiple service instances require Redis or another distributed implementation of the same coordination interfaces. PostgreSQL remains the source of truth in either mode.

Logical Planes

%%{init: {"flowchart": {"curve": "linear"}}}%%
flowchart LR
  Evidence["Evidence<br/>ingests and fragments"] --> Observation["Observations<br/>extracted statements"]
  Observation --> Decision["Decisions<br/>verification and review"]
  Decision --> Current["Current knowledge<br/>Entities and Relationships"]
  Current --> Read["Direct read model<br/>SemanticEdge and search"]
  Decision --> History["Append-only history<br/>support and transitions"]

These are data responsibilities, not separate databases. Their canonical Relationship terminology and lifecycle are defined in Relationship Model And Lifecycle.

Target PostgreSQL Model

The names below are the target logical schema. Physical migrations may use equivalent names, but must preserve each responsibility.

%%{init: {"flowchart": {"curve": "linear"}}}%%
flowchart LR
  Team["Team"] --> Ingest["KnowledgeIngest"]
  Team --> Profile["Profile"]
  Profile --> Source["Owned EvidenceSource"]
  Source --> Revision["EvidenceSourceRevision"]
  Team --> Entity["EntityRecord"]
  Team --> Value["ValueRecord"]
  Profile --> Relationship["Owned RelationshipRecord"]
  Team --> Hypothesis["Hypothesis"]

  Ingest --> Fragment["EvidenceFragment"]
  Revision --> Fragment
  Ingest --> Placement["PlacementRun / Item"]
  Entity --> Name["EntityName"]
  Entity --> Resolution["EntityResolutionEvent"]
  Entity --> Correction["EntityCorrectionEvent"]

  Fragment --> Observation["RelationshipObservation"]
  Fragment --> Security["EvidenceSecurityEvent"]
  Observation --> Verification["VerificationEvent"]
  Relationship --> Observation
  Relationship --> Support["RelationshipEvidenceSupport"]
  Fragment --> Support
  Support --> SupportDecision["RelationshipSupportDecisionEvent"]
  Relationship --> Transition["RelationshipTransitionEvent"]
  Relationship --> Cross["RelationshipCrossReference"]
  Relationship --> Review["ReviewTask"]

  Entity --> Search["SearchDocument"]
  Relationship --> Search
  Fragment --> Search
  Search --> Job["EmbeddingJob"]

Knowledge and workflow tables

The exact Relationship identity, fields, and lifecycle belong only in Relationship Model And Lifecycle. Entity creation, reuse, ambiguity, merge, and split belong only in Entity Identity And Resolution.

Control-plane tables

%%{init: {"flowchart": {"curve": "linear"}}}%%
flowchart LR
  Identity["teams, profiles, SSO"] --> Control["Control plane"]
  Security["API keys, key activity, audit"] --> Control
  Config["configuration and model contracts"] --> Control
  Quality["feedback and evaluation runs"] --> Control
  Operations["usage, operation logs,<br/>scheduler runs"] --> Control

Security and control records remain durable PostgreSQL rows. Tenant-owned control/workflow rows use the same team contract as knowledge; authentication discovery and other records needed before team resolution stay coordinator- local as classified in PostgreSQL Cluster-Safe Schema. Redis never substitutes for either class.

Semantic Nodes And Edges

The graph is a domain view over relational rows:

%%{init: {"flowchart": {"curve": "linear"}}}%%
flowchart LR
  Subject["Entity row<br/>semantic node"] ==>|"eligible Relationship row<br/>SemanticEdge"| Object["Entity or Value row<br/>semantic node"]

• Entity and typed Value are the only semantic-node kinds.
• An active validated_claim or fact Relationship is exposed as one SemanticEdge.
• Candidate Relationships, names, evidence, observations, verification events, reviews, transitions, and Hypotheses are not nodes or active edges.
• An alias is an entity_names row, not a Relationship.

The edge is an API/query shape, not another stored copy. For example:

CREATE VIEW semantic_edges AS
SELECT relationship_id, team_id, owner_profile_id, semantic_group_key,
       subject_entity_id, predicate_key,
       object_entity_id, object_value_id, polarity, valid_from, valid_to,
       tier, version
FROM relationship_records
WHERE status = 'active'
  AND tier IN ('validated_claim', 'fact');

The migration may implement this as a view, a reusable query, or a security-barrier view. It must not materialize a second authoritative edge.

Bounded graph expansion

Traversal is implemented with indexed adjacency queries or bounded recursive CTEs. The hot recall path expands only configured seeds, fan-out, and depth; it does not run arbitrary unbounded graph queries.

Representative indexes are:

CREATE INDEX relationship_active_subject_idx
  ON relationship_records (team_id, subject_entity_id, predicate_key)
  WHERE status = 'active'
    AND tier IN ('validated_claim', 'fact');
​
CREATE INDEX relationship_active_object_entity_idx
  ON relationship_records (team_id, object_entity_id, predicate_key)
  WHERE status = 'active'
    AND tier IN ('validated_claim', 'fact')
    AND object_entity_id IS NOT NULL;

The concrete schema must also enforce exactly one object kind and the canonical Relationship-identity uniqueness rules.

Search In PostgreSQL

search_documents contains compact, reconstructible text for eligible Entities, Relationships, and evidence fragments. The source records remain authoritative. A search hit is only a candidate ID and never evidence.

%%{init: {"flowchart": {"curve": "linear"}}}%%
flowchart LR
  Source["Authorized source rows"] --> Document["SearchDocument"]
  Document --> FTS["tsvector / GIN"]
  Document --> Vector["pgvector exact / ANN"]
  FTS --> Union["Candidate ID union"]
  Vector --> Union
  Union --> Filter["team, status, tier,<br/>time and access filters"]
  Filter --> Hydrate["Hydrate source rows"]
  Hydrate --> Rank["Rank and bounded expansion"]

Search documents may retain a denormalized display string, but cannot own semantic state. Deleting and rebuilding all search documents must not lose knowledge.

Embedding/distance meaning, RRF, and final reranking belong only in Vector Search And Ranking. Exact/ANN execution, HNSW, halfvec, and index topology belong only in ANN, HNSW, And Halfvec.

Embedding contract, search index, and freshness

The embedding provider adapter, model, dimensions, normalization, and document format form one versioned embedding contract. Vector rows reference that contract and carry a database-enforced matching dimension. A different active contract is never compared with those rows.

A separate search-index profile references the embedding contract and owns the distance/operator class, indexed cast, HNSW construction parameters, physical index state, and active pointer. A ranking profile owns query-time exact/ANN thresholds and scan budgets. This separation allows reindexing without provider calls and query tuning without reindexing. Field ownership and change impact belong in ANN, HNSW, And Halfvec.

Each source aggregate exposes:

search_state: not_required | pending | current | failed
search_document_version
embedding_contract_version

search_state describes derived retrieval readiness, not whether the knowledge write committed. A Relationship can be authoritative and immediately available to exact/full-text/adjacency reads while its new embedding is pending.

Write and embedding sequence

%%{init: {"sequence": {"rightAngles": true}}}%%
sequenceDiagram
  autonumber
  participant W as Placement worker
  participant P as PostgreSQL
  participant E as Embedding worker

  W->>P: Begin transaction
  W->>P: Append evidence, decisions, support, and transitions
  W->>P: Update Entity / Relationship current state
  W->>P: Upsert search document with search_state=pending
  W->>P: Insert or coalesce embedding job
  W->>P: Commit
  P-->>W: Authoritative write complete

  E->>P: Claim job batch with SKIP LOCKED leases
  E->>E: Build ordered batch embeddings under declared contract
  E->>P: Update vectors only if source versions still match
  E->>P: Mark matching documents current and finish jobs

An older embedding job cannot overwrite a newer source version. A provider failure records failed plus retry state; it does not roll back committed knowledge or get hidden behind an in-memory fallback.

Embedding workers prefer bounded batching before worker parallelism. A batch claim selects queued jobs for one team/work profile with FOR UPDATE SKIP LOCKED, marks them processing with renewable leases, calls the provider's ordered batch embedding API, and completes vector/document/job state in one transaction. If a batch fails, the worker recursively splits the batch to isolate bad or oversized documents instead of retrying every good document together. Upserting an unchanged search document must not clear an existing current vector or enqueue another embedding job.

Vector storage consequence

Every search document carries team_id, source/document version, embedding contract version, dimensions, and freshness state. Exact and approximate paths must select candidate IDs under the same actor/team eligibility. Physical index strategy, tenant filtering, partitioning/sharding, and single/cluster behavior belong only in ANN, HNSW, And Halfvec. Relational tenant keys and query locality belong in PostgreSQL Cluster-Safe Schema. The release gate belongs in Evaluation.

Atomicity And Append-Only History

One PostgreSQL transaction commits each accepted semantic change:

1. the ingest and exact evidence
2. schema-valid verifier decisions
3. Entity/Value resolution events
4. Relationship observations, immutable support links, and support decisions
5. Relationship current-state changes and transition events
6. review work, search-document intent, and durable embedding jobs

If that transaction fails, none of the semantic change commits. Embedding generation remains asynchronous because it is derived and can be retried.

A source-revision activation is a separate authoritative transaction. It validates immutable source ownership and the exact prior-revision token, appends the new revision, advances the current pointer, stages new fragments and their scan assessment, revokes supports from the previous revision, recomputes every affected Relationship tier/status and transition, supersedes outstanding prior revision placement/search intent, and updates current search intent. The new evidence may remain queued or quarantined after commit; the prior revision is already ineffective. Placement and search writes require a matching current source revision so an in-flight stale worker cannot restore it.

Source activation discovers affected Relationships through indexed fragment/support dependencies. The service precomputes a server-defined hard fanout bound and aborts before changing the current pointer when the bound would be exceeded. It never batches one source activation into partially visible semantic states.

Evidence source revisions, evidence fragments, security events, material verification events, support links, support decision events, resolution events, correction events, and Relationship transitions are append-only. Corrections append decisions and update current-state rows in the same transaction; they do not rewrite the original evidence.

Database permissions, constraints, or triggers must prevent application roles from mutating records designated append-only.

Entity And Relationship Constraints

entity_records.entity_id is opaque and stable. It is neither display text nor a hash of a name. (team_id, normalized_name) is a lookup key, never a uniqueness rule, because homonyms are valid.

Repeated accepted evidence from one profile attaches to that owner's canonical Relationship identity. It does not insert a parallel semantic edge for the same owner. Equivalent Relationships from another profile remain separately owned and are grouped by semantic_group_key during recall. Compact current counts may live on the Relationship row; exact evidence and verifier history remain normalized and are fetched only for trace.

Representative constraints include:

Entity identity       UNIQUE (team_id, entity_id)
Value identity        UNIQUE (team_id, value_id)
Value canonical       UNIQUE NULLS NOT DISTINCT (team_id, value_type, canonical_value, unit, normalization_version)
Stable external ID    UNIQUE (team_id, namespace, external_id)
Source identity       UNIQUE (team_id, source_owner_profile_id, source_key)
Source revision       UNIQUE (team_id, source_id, source_revision)
Support identity      UNIQUE (team_id, relationship_id, fragment_id, start_offset, end_offset)
Relationship identity UNIQUE NULLS NOT DISTINCT (team_id, owner_profile_id, subject_entity_id, predicate_key, object_entity_id, object_value_id, polarity, valid_from, valid_to, scope_key)
Cross-reference       UNIQUE (team_id, author_profile_id, source_relationship_id, target_relationship_id, kind, verification_event_id)

The Relationship uniqueness key is defined in Relationship Model And Lifecycle. Cross-reference constraints additionally require both Relationships to share the row's team and the source Relationship owner to equal author_profile_id. An Evidence source has one immutable owner and one current-revision pointer. Advancement uses compare-and-set against previous_source_revision; explicit fragment supersession may name only fragments owned by that source principal.

Predicate Registry Storage

Predicate definitions are versioned PostgreSQL records. The registry owns canonical keys, aliases, allowed endpoint kinds, relationship_kind, current_cardinality, lifecycle state, and version.

It does not contain policy_family and cannot be changed by the verifier. The canonical contract and unknown-predicate behavior are defined in Relationship Model And Lifecycle.

Tenant And Actor Isolation

The team is the durable visibility boundary. A team profile is an authenticated actor with roles and scopes inside that team. Same-team reads may see every profile's eligible knowledge; ordinary lifecycle/support mutations require owner_profile_id = app.current_profile_id.

%%{init: {"flowchart": {"curve": "linear"}}}%%
flowchart LR
  Key["Bearer API key"] --> Profile["Team profile"]
  Profile --> Team["team_id"]
  Team --> Tx["PostgreSQL transaction"]
  Tx --> RLS["row-level security"]
  RLS --> Ledger["ledger and current state"]
  RLS --> Search["full-text and vector candidates"]
  Team -. "namespace only" .-> Cache["Redis keys"]

Application queries run in explicit transactions with transaction-local values such as:

app.current_team_id = <authenticated team UUID>
app.current_profile_id = <authenticated profile UUID>
app.tx_mode = team

System workers use a separate, non-request-selectable transaction mode. Payloads and headers cannot replace the authenticated team. RLS applies to search documents and vector queries as well as source tables.

System migration workers may preserve another profile as owner only through a separate migration transaction mode that records the migration run and original v1 author. Normal request payloads cannot select owner_profile_id.

Redis namespacing prevents accidental key collisions; it is not an authorization boundary. Authorization occurs before a coordination operation.

Redis Availability

%%{init: {"flowchart": {"curve": "linear"}}}%%
flowchart TD
  Start["Service startup"] --> Multi{"Multiple instances or<br/>distributed contract required?"}
  Multi -- Yes --> Reachable{"Redis or equivalent<br/>reachable?"}
  Reachable -- Yes --> Distributed["Use distributed coordination"]
  Reachable -- No --> NotReady["Fail readiness / startup"]
  Multi -- No --> Configured{"Redis configured?"}
  Configured -- Yes --> Distributed
  Configured -- No --> Local["Use in-process coordination"]

Redis may hold rate-limit counters, short leases, cleanup signals, and SSE concurrency state. Losing Redis may reset or reacquire those operations, but must never lose or change memory.

Redis-backed and process-local coordination are alternative implementations of one contract; a replica must not apply both independent counters or leases. When Redis is selected, every replica uses it for the distributed operation. Small process-local caches of immutable/versioned predicate or configuration lookups may still exist, but they cannot cache canonical Entity/Relationship writes or bypass PostgreSQL version and authorization checks.

Backup, Recovery, And Maintenance

PostgreSQL backups plus write-ahead-log archiving are the complete durable recovery source. pgvector columns and indexes participate in the same database backup and recovery process. Redis is excluded from memory recovery.

Recovery order:

1. restore PostgreSQL, WAL, and encryption material
2. validate migrations, extensions, RLS, embedding contracts, and active search-index profiles
3. verify append-only and semantic uniqueness constraints
4. resume or rebuild search documents and vectors when required
5. compare approximate Recall@K with exact filtered search
6. restore distributed coordination before enabling multiple instances
7. enable traffic after write, recall, and tenant-isolation smoke tests pass

Operational maintenance includes PostgreSQL vacuum/analyze, vector-index health, job backlog, failed embeddings, query latency, and backup restore drills. Runbook commands belong in Operations Runbook, not here.

Legacy Migration Boundary

Normal target runtime does not depend on Neo4j. The temporary 2.1.x bridge reads current non-superseded Neo4j knowledge as a versioned corpus and submits it through the official v2 write pipeline. It never directly converts graph rows into canonical tables.

Migration must:

1. preserve old identifiers in explicit mapping tables
2. preserve every team and original owner profile
3. stage corpus evidence, run structured extraction and the strict verifier, then let normal policy reconstruct Entity/Value/Relationship state
4. preserve provenance, temporal bounds, polarity, source groups, and migration lineage without copying a v1 tier
5. exclude superseded v1 knowledge from the corpus while retaining its IDs, counts, reason, and checksums in the migration report
6. build new v2 search documents/vectors instead of copying legacy vectors
7. hold ambiguous identities, unknown predicates, and unsupported candidates for review instead of activating them
8. cut over automatically only after terminal-outcome, author, ownership, tenant-isolation, trace, vector, and recall gates pass

Migration execution, checkpoints, and rollback belong in Release Process. This page owns only the target shape.

Risks And Mitigations

Storage Invariants

1. PostgreSQL contains every durable knowledge and control record.
2. pgvector is an indexed column capability inside that source of truth.
3. Redis and process memory contain no canonical memory.
4. Entity and typed Value are the only semantic-node kinds.
5. One eligible profile-owned Relationship is returned as at most one SemanticEdge.
6. Candidates and Hypotheses are not active edges.
7. Search hits and graph context never count as source evidence.
8. Append-only evidence and decision history cannot be rewritten by normal application code.
9. Every source and search query is actor-authorized and team-scoped.
10. Embedding mismatch or failure is explicit and cannot corrupt semantic state.
11. Losing all derived search documents does not lose knowledge.
12. Embedding contracts and search-index profiles have separate versions and rebuild lifecycles.
13. Normal target runtime remains functional without Neo4j; 2.2.0+ contains no connection path.
14. Same-team reads can see eligible knowledge from every profile, while normal mutations require the caller to own the target record.
15. One Evidence source has one immutable owner and one current revision.
16. A source revision advances only when its expected prior token matches.
17. Every revision of one Evidence source retains one server-derived source group for independence counting.
18. Source activation, prior-support revocation, affected lifecycle transitions, and search intent commit atomically or not at all.
19. Evidence security history is append-only, and quarantined evidence has no semantic or search document.
20. A non-current source revision cannot commit placement or search state after a newer revision activates.