Postgres schema

CREATE TABLE entities (
    entity_id       TEXT PRIMARY KEY,
    entity_type     TEXT NOT NULL,
    status          TEXT NOT NULL CHECK (status IN ('provisional', 'canonical', 'merged')),
    authority       TEXT,
    authority_id    TEXT,
    confidence      FLOAT NOT NULL DEFAULT 0.5,
    evidence_count  INT NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE surface_forms (
    entity_id   TEXT NOT NULL REFERENCES entities(entity_id),
    surface     TEXT NOT NULL,
    normalized  TEXT NOT NULL,
    PRIMARY KEY (entity_id, normalized)
);

CREATE TABLE entity_embeddings (
    entity_id       TEXT PRIMARY KEY REFERENCES entities(entity_id),
    embedding       VECTOR(1536),
    embedding_model TEXT NOT NULL
);

CREATE INDEX ON entity_embeddings USING ivfflat (embedding vector_cosine_ops);

CREATE TABLE merge_log (
    merge_id        SERIAL PRIMARY KEY,
    survivor_id     TEXT NOT NULL REFERENCES entities(entity_id),
    absorbed_id     TEXT NOT NULL,
    merged_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    reason          TEXT
);

CREATE TABLE promotion_log (
    promotion_id    SERIAL PRIMARY KEY,
    entity_id       TEXT NOT NULL REFERENCES entities(entity_id),
    from_status     TEXT NOT NULL,
    to_status       TEXT NOT NULL,
    authority       TEXT,
    authority_id    TEXT,
    promoted_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);