Proposed SQL schemas

The open Platform engine stores its knowledge base in PostgreSQL with the pgvector extension. The schemas below are a proposed, illustrative shape of that store — the entity table, its typed relations, the server-side history, and the transaction bookkeeping. They are meant to explain the model, not to pin an exact migration.

Extensions

CREATE EXTENSION IF NOT EXISTS vector;     -- pgvector
CREATE EXTENSION IF NOT EXISTS pgcrypto;   -- gen_random_uuid()

Entities

One row per entity. The id is the stable, human-readable identifier; the embedding is a 1024-dimension vector for semantic search; tags and metadata are flexible JSON/array columns.

CREATE TABLE entity (
  entity_id    text PRIMARY KEY,                 -- e.g. CMP-KVD-CLI
  entity_type  text NOT NULL,                    -- PRJ, CMP, IF, REQ, ...
  project_id   text NOT NULL,
  component_id text,
  title        text NOT NULL,
  content      text NOT NULL DEFAULT '',
  status       text NOT NULL DEFAULT 'active',
  archived     boolean NOT NULL DEFAULT false,
  tags         text[] NOT NULL DEFAULT '',
  metadata     jsonb  NOT NULL DEFAULT '',
  embedding    vector(1024),                     -- L2-normalized
  version      integer NOT NULL DEFAULT 1,
  txn_id       text,                             -- non-null while draft
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now()
);

-- Approximate-nearest-neighbour index for cosine similarity.
CREATE INDEX entity_embedding_idx
  ON entity USING hnsw (embedding vector_cosine_ops);

CREATE INDEX entity_project_idx ON entity (project_id, entity_type);
CREATE INDEX entity_tags_idx    ON entity USING gin (tags);

Relations

Typed, directed edges between entities — part_of, fulfills, derives_from, affects, and so on. Relation targets are validated against the entity table.

CREATE TABLE relation (
  id            bigserial PRIMARY KEY,
  source_id     text NOT NULL REFERENCES entity (entity_id),
  target_id     text NOT NULL REFERENCES entity (entity_id),
  relation_type text NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (source_id, target_id, relation_type)
);

CREATE INDEX relation_source_idx ON relation (source_id);
CREATE INDEX relation_target_idx ON relation (target_id);

History (typed changelog)

Append-only. The engine writes one row per create/update, capturing who changed what and why. Clients never write here directly.

CREATE TABLE entity_history (
  id             bigserial PRIMARY KEY,
  entity_id      text NOT NULL REFERENCES entity (entity_id),
  author         text NOT NULL,
  trigger        text,                  -- often the txn id, or 'direct'
  change_summary text NOT NULL,
  version_before integer,
  version_after  integer,
  created_at     timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX entity_history_entity_idx ON entity_history (entity_id, created_at);

Transactions

Bookkeeping for the draft/activate/cancel lifecycle. While a transaction is open, the entities it created keep their txn_id and stay in draft; activating it clears the id and promotes them.

CREATE TABLE txn (
  txn_id       text PRIMARY KEY,        -- TXN-KVD-YYYYMMDD-NNN
  project_id   text NOT NULL,
  component_id text,
  type         text NOT NULL,
  status       text NOT NULL DEFAULT 'open',  -- open | completed | cancelled
  trigger      text,
  started_by   text,
  created_at   timestamptz NOT NULL DEFAULT now(),
  closed_at    timestamptz
);