0001_schema.sql

-- Core tables
CREATE TYPE instrument_kind AS ENUM('equity','note','safe','loan','other');
CREATE TYPE event_type AS ENUM('investment','distribution','repayment','conversion','info');

CREATE TABLE IF NOT EXISTS companies(
  company_pk      BIGSERIAL PRIMARY KEY,
  company_id      TEXT UNIQUE NOT NULL,
  legal_name      TEXT NOT NULL,
  description     TEXT,
  created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS company_aliases(
  company_pk BIGINT REFERENCES companies(company_pk) ON DELETE CASCADE,
  alias      TEXT NOT NULL,
  UNIQUE(company_pk, alias)
);

CREATE TABLE IF NOT EXISTS instruments(
  instrument_id  BIGSERIAL PRIMARY KEY,
  company_pk     BIGINT REFERENCES companies(company_pk) ON DELETE CASCADE,
  kind           instrument_kind NOT NULL,
  name           TEXT,
  terms_json     JSONB
);

CREATE TABLE IF NOT EXISTS events(
  event_id      BIGSERIAL PRIMARY KEY,
  company_pk    BIGINT REFERENCES companies(company_pk) ON DELETE CASCADE,
  instrument_id BIGINT REFERENCES instruments(instrument_id),
  event_date    DATE NOT NULL,
  type          event_type NOT NULL,
  amount        NUMERIC(15,2),
  subtype       TEXT,
  note          TEXT,
  party_in      TEXT,
  party_out     TEXT,
  created_at    TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS updates(
  update_id     UUID PRIMARY KEY,
  company_pk    BIGINT REFERENCES companies(company_pk) ON DELETE CASCADE,
  date          DATE NOT NULL,
  source_type   TEXT NOT NULL,
  source_ref    TEXT,
  update_type   TEXT NOT NULL,
  headline      TEXT NOT NULL,
  body_text     TEXT,
  structured_data JSONB,
  created_at    TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS documents(
  doc_id        TEXT PRIMARY KEY,
  company_pk    BIGINT REFERENCES companies(company_pk) ON DELETE CASCADE,
  title         TEXT,
  kind          TEXT,
  blob_url      TEXT NOT NULL,
  sha256        TEXT NOT NULL,
  pages         INT,
  embeddings_id TEXT,
  uploaded_at   TIMESTAMPTZ DEFAULT now()
);

-- Dossier history
CREATE TABLE IF NOT EXISTS dossier_version(
  company_pk BIGINT PRIMARY KEY REFERENCES companies(company_pk) ON DELETE CASCADE,
  version    INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS dossier_history(
  history_id BIGSERIAL PRIMARY KEY,
  company_pk BIGINT REFERENCES companies(company_pk) ON DELETE CASCADE,
  version    INT NOT NULL,
  snapshot   JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);