`db/schema.sql`

118 lines
-- AbramsOS — declarative current schema (v0.1, Phase 0)
-- Apply: psql -d abrams_os -f db/schema.sql
-- Idempotent. For deltas after this, use db/migrations/NNNN_*.sql.

BEGIN;

CREATE TABLE IF NOT EXISTS user_account (
  id              text PRIMARY KEY,
  email           text NOT NULL UNIQUE,
  display_name    text,
  locale          text DEFAULT 'en-US',
  timezone        text DEFAULT 'America/Los_Angeles',
  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS consent_grant (
  id              text PRIMARY KEY,
  user_id         text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  connector_type  text NOT NULL,            -- 'gmail', 'outlook', 'plaid', etc.
  scopes_json     jsonb NOT NULL,
  granted_at      timestamptz NOT NULL DEFAULT now(),
  revoked_at      timestamptz
);
CREATE INDEX IF NOT EXISTS consent_grant_user_idx ON consent_grant (user_id, connector_type);

CREATE TABLE IF NOT EXISTS connector_account (
  id                       text PRIMARY KEY,
  user_id                  text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  consent_grant_id         text NOT NULL REFERENCES consent_grant(id) ON DELETE CASCADE,
  provider                 text NOT NULL,                -- 'google'
  external_subject_id      text NOT NULL,                -- Google `sub` or email
  refresh_token_encrypted  bytea NOT NULL,               -- AES-256-GCM
  refresh_token_iv         bytea NOT NULL,
  refresh_token_tag        bytea NOT NULL,
  last_sync_at             timestamptz,
  created_at               timestamptz NOT NULL DEFAULT now(),
  UNIQUE (provider, external_subject_id)
);
CREATE INDEX IF NOT EXISTS connector_account_user_idx ON connector_account (user_id);

CREATE TABLE IF NOT EXISTS source_message (
  id              text PRIMARY KEY,
  connector_id    text NOT NULL REFERENCES connector_account(id) ON DELETE CASCADE,
  source_type     text NOT NULL,             -- 'gmail'
  external_id     text NOT NULL,             -- Gmail message id
  thread_id       text,
  received_at     timestamptz NOT NULL,
  subject         text,
  sender          text,
  recipient       text,
  payload_jsonb   jsonb NOT NULL,
  payload_hash    text NOT NULL,
  ingested_at     timestamptz NOT NULL DEFAULT now(),
  UNIQUE (connector_id, source_type, external_id)
);
CREATE INDEX IF NOT EXISTS source_message_received_idx ON source_message (received_at DESC);
CREATE INDEX IF NOT EXISTS source_message_connector_idx ON source_message (connector_id, received_at DESC);

CREATE TABLE IF NOT EXISTS document (
  id                  text PRIMARY KEY,
  source_message_id   text REFERENCES source_message(id) ON DELETE CASCADE,
  user_id             text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  kind                text NOT NULL,        -- 'receipt', 'warranty', 'invoice', 'attachment', 'screenshot'
  mime                text,
  hash                text NOT NULL,
  object_path         text,                 -- relative to uploads/
  parsed_status       text NOT NULL DEFAULT 'pending',
  created_at          timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS document_user_idx ON document (user_id, created_at DESC);

CREATE TABLE IF NOT EXISTS purchase (
  id                  text PRIMARY KEY,
  user_id             text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  source_message_id   text REFERENCES source_message(id) ON DELETE SET NULL,
  merchant_name       text NOT NULL,
  merchant_domain     text,
  order_number        text,
  purchase_date       timestamptz NOT NULL,
  total_amount        numeric(12,2),
  currency            text DEFAULT 'USD',
  confidence          numeric(3,2),         -- 0..1
  raw_extract         jsonb,
  created_at          timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS purchase_user_date_idx ON purchase (user_id, purchase_date DESC);
CREATE INDEX IF NOT EXISTS purchase_merchant_idx ON purchase (user_id, merchant_name);

CREATE TABLE IF NOT EXISTS purchase_item (
  id              text PRIMARY KEY,
  purchase_id     text NOT NULL REFERENCES purchase(id) ON DELETE CASCADE,
  category        text,
  brand           text,
  model           text,
  gtin            text,
  serial          text,
  quantity        integer DEFAULT 1,
  unit_price      numeric(12,2),
  raw_text        text,
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS purchase_item_purchase_idx ON purchase_item (purchase_id);

CREATE TABLE IF NOT EXISTS audit_log (
  id              bigserial PRIMARY KEY,
  occurred_at     timestamptz NOT NULL DEFAULT now(),
  actor_type      text NOT NULL,             -- 'user', 'system', 'agent'
  actor_id        text,
  object_type     text NOT NULL,             -- 'consent_grant', 'connector_account', 'source_message', 'purchase', etc.
  object_id       text,
  event_type      text NOT NULL,             -- 'oauth_grant', 'sync_started', 'message_persisted', 'purchase_extracted', 'connector_revoked'
  metadata_jsonb  jsonb NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX IF NOT EXISTS audit_log_object_idx ON audit_log (object_type, object_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS audit_log_event_idx ON audit_log (event_type, occurred_at DESC);

COMMIT;