Updated `core-db-init/` init sequence (Phase 1 + Phase 2)

1. `01_schema.sql` (Phase 1 core + nutrition + raw)
2. `02_seed_persons.sql`
3. `03_schema_doc.sql`
4. `04_schema_comms.sql`
5. `05_schema_finance.sql`
6. `06_schema_supplement.sql`
7. `07_schema_health.sql`
8. `08_schema_wellbeing.sql`
9. `09_schema_ai_session.sql`

---

```sql
-- 03_schema_doc.sql
-- Documents + chunks + embedding metadata (canonical text + rebuildable semantic index pointers)

CREATE SCHEMA IF NOT EXISTS doc;

-- ---------- Shared utility: updated_at ----------
-- Phase 1 tables used updated_at defaults but did not auto-bump on UPDATE.
-- This trigger is safe to add now and apply broadly.
CREATE OR REPLACE FUNCTION core.set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

-- Apply updated_at triggers to Phase 1 tables that have updated_at
DROP TRIGGER IF EXISTS trg_core_person_set_updated_at ON core.person;
CREATE TRIGGER trg_core_person_set_updated_at
BEFORE UPDATE ON core.person
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

DROP TRIGGER IF EXISTS trg_core_event_set_updated_at ON core.event;
CREATE TRIGGER trg_core_event_set_updated_at
BEFORE UPDATE ON core.event
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

DROP TRIGGER IF EXISTS trg_nutrition_recipe_set_updated_at ON nutrition.recipe;
CREATE TRIGGER trg_nutrition_recipe_set_updated_at
BEFORE UPDATE ON nutrition.recipe
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- ---------- Documents ----------
-- A document is any durable unstructured artifact: email, PDF, transcript, journal entry, etc.
-- raw_object_key points at SeaweedFS (S3) when there is a raw binary artifact.
-- content_text is optional extracted/plain text; canonical retrieval is via doc.chunk.
CREATE TABLE IF NOT EXISTS doc.document (
  document_id       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  person_id         uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,
  event_id          uuid REFERENCES core.event(event_id) ON DELETE SET NULL,

  visibility_scope  text NOT NULL DEFAULT 'private'
                    CHECK (visibility_scope IN ('private', 'shared')),

  document_type     text NOT NULL,  -- e.g. email.eml, pdf, journal, transcript, ai_session, meeting_note
  title             text,

  source_system     text,           -- gmail, fireflies, manual, markbot, etc.
  source_ref        text,           -- provider-specific stable id (gmail message id, file id, etc.)

  raw_object_key    text,           -- SeaweedFS S3 key (NULL for born-digital text-only docs)
  raw_mime_type     text,
  raw_sha256_hex    text,           -- hash of raw artifact bytes when known

  content_text      text,           -- optional extracted text (canonical retrieval uses doc.chunk)
  content_language  text,

  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS doc_document_person_created_idx
  ON doc.document (person_id, created_at DESC);

CREATE INDEX IF NOT EXISTS doc_document_type_created_idx
  ON doc.document (document_type, created_at DESC);

CREATE INDEX IF NOT EXISTS doc_document_event_idx
  ON doc.document (event_id);

-- Prevent accidental dupes when a provider gives a stable id
CREATE UNIQUE INDEX IF NOT EXISTS doc_document_source_uq
  ON doc.document (source_system, source_ref)
  WHERE source_system IS NOT NULL AND source_ref IS NOT NULL;

DROP TRIGGER IF EXISTS trg_doc_document_set_updated_at ON doc.document;
CREATE TRIGGER trg_doc_document_set_updated_at
BEFORE UPDATE ON doc.document
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- ---------- Chunking profiles + runs ----------
-- Chunking can change over time; keep profiles and runs versioned and rebuildable.
CREATE TABLE IF NOT EXISTS doc.chunking_profile (
  chunking_profile_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name                text NOT NULL,
  config_json         jsonb NOT NULL DEFAULT '{}'::jsonb,  -- e.g. max_tokens, overlap, separators
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS doc_chunking_profile_name_uq
  ON doc.chunking_profile (name);

DROP TRIGGER IF EXISTS trg_doc_chunking_profile_set_updated_at ON doc.chunking_profile;
CREATE TRIGGER trg_doc_chunking_profile_set_updated_at
BEFORE UPDATE ON doc.chunking_profile
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- One document can have multiple chunk runs. Keep exactly one active run per document.
CREATE TABLE IF NOT EXISTS doc.chunk_set (
  chunk_set_id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id          uuid NOT NULL REFERENCES doc.document(document_id) ON DELETE CASCADE,
  person_id            uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,
  chunking_profile_id  uuid NOT NULL REFERENCES doc.chunking_profile(chunking_profile_id) ON DELETE RESTRICT,
  is_active            boolean NOT NULL DEFAULT true,
  note                 text,
  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS doc_chunk_set_document_idx
  ON doc.chunk_set (document_id, created_at DESC);

CREATE UNIQUE INDEX IF NOT EXISTS doc_chunk_set_one_active_uq
  ON doc.chunk_set (document_id)
  WHERE is_active;

DROP TRIGGER IF EXISTS trg_doc_chunk_set_set_updated_at ON doc.chunk_set;
CREATE TRIGGER trg_doc_chunk_set_set_updated_at
BEFORE UPDATE ON doc.chunk_set
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- Auto-fill/validate person_id from the owning document for RLS friendliness.
CREATE OR REPLACE FUNCTION doc.set_chunk_set_person_id()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_person_id uuid;
BEGIN
  SELECT d.person_id INTO v_person_id
  FROM doc.document d
  WHERE d.document_id = NEW.document_id;

  IF v_person_id IS NULL THEN
    RAISE EXCEPTION 'doc.document % not found for chunk_set', NEW.document_id;
  END IF;

  IF NEW.person_id IS NULL THEN
    NEW.person_id := v_person_id;
  ELSIF NEW.person_id <> v_person_id THEN
    RAISE EXCEPTION 'chunk_set person_id % does not match document person_id %', NEW.person_id, v_person_id;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_doc_chunk_set_person_id ON doc.chunk_set;
CREATE TRIGGER trg_doc_chunk_set_person_id
BEFORE INSERT OR UPDATE ON doc.chunk_set
FOR EACH ROW
EXECUTE FUNCTION doc.set_chunk_set_person_id();

-- ---------- Chunks ----------
-- Canonical chunk text lives here (Postgres).
CREATE TABLE IF NOT EXISTS doc.chunk (
  chunk_id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  chunk_set_id      uuid NOT NULL REFERENCES doc.chunk_set(chunk_set_id) ON DELETE CASCADE,
  person_id         uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,

  chunk_index       integer NOT NULL,          -- 0..N within a chunk_set
  char_start        integer,                   -- optional offsets into content_text when applicable
  char_end          integer,
  token_count       integer,

  chunk_text        text NOT NULL,
  chunk_sha256_hex  text,                      -- optional idempotency/dedup hash of chunk_text

  chunk_tsv         tsvector GENERATED ALWAYS AS
                    (to_tsvector('english', coalesce(chunk_text, ''))) STORED,

  created_at        timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS doc_chunk_set_index_uq
  ON doc.chunk (chunk_set_id, chunk_index);

CREATE INDEX IF NOT EXISTS doc_chunk_person_created_idx
  ON doc.chunk (person_id, created_at DESC);

CREATE INDEX IF NOT EXISTS doc_chunk_tsv_gin_idx
  ON doc.chunk USING GIN (chunk_tsv);

-- Auto-fill person_id from chunk_set + compute chunk hash when not supplied.
CREATE OR REPLACE FUNCTION doc.set_chunk_person_id_and_hash()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_person_id uuid;
BEGIN
  SELECT cs.person_id INTO v_person_id
  FROM doc.chunk_set cs
  WHERE cs.chunk_set_id = NEW.chunk_set_id;

  IF v_person_id IS NULL THEN
    RAISE EXCEPTION 'doc.chunk_set % not found for chunk', NEW.chunk_set_id;
  END IF;

  IF NEW.person_id IS NULL THEN
    NEW.person_id := v_person_id;
  ELSIF NEW.person_id <> v_person_id THEN
    RAISE EXCEPTION 'chunk person_id % does not match chunk_set person_id %', NEW.person_id, v_person_id;
  END IF;

  IF NEW.chunk_sha256_hex IS NULL THEN
    NEW.chunk_sha256_hex := encode(digest(convert_to(NEW.chunk_text, 'UTF8'), 'sha256'), 'hex');
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_doc_chunk_person_hash ON doc.chunk;
CREATE TRIGGER trg_doc_chunk_person_hash
BEFORE INSERT OR UPDATE ON doc.chunk
FOR EACH ROW
EXECUTE FUNCTION doc.set_chunk_person_id_and_hash();

-- ---------- Embedding metadata ----------
-- Weaviate is the serving index. Postgres stores embedding model metadata + per-chunk embedding references.
CREATE TABLE IF NOT EXISTS doc.embedding_model (
  embedding_model_id  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  model_name          text NOT NULL, -- e.g. text-embedding-3-large, bge-large-en-v1.5, etc.
  model_version       text NOT NULL DEFAULT '1',
  provider            text,          -- openai, local, etc.
  dimension           integer NOT NULL CHECK (dimension > 0),

  distance_metric     text NOT NULL DEFAULT 'cosine'
                      CHECK (distance_metric IN ('cosine', 'dot', 'l2')),

  note                text,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS doc_embedding_model_name_version_uq
  ON doc.embedding_model (model_name, model_version);

DROP TRIGGER IF EXISTS trg_doc_embedding_model_set_updated_at ON doc.embedding_model;
CREATE TRIGGER trg_doc_embedding_model_set_updated_at
BEFORE UPDATE ON doc.embedding_model
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- One chunk can be embedded by multiple models, and optionally indexed in multiple serving indexes.
CREATE TABLE IF NOT EXISTS doc.chunk_embedding (
  chunk_id            uuid NOT NULL REFERENCES doc.chunk(chunk_id) ON DELETE CASCADE,
  person_id           uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,
  embedding_model_id  uuid NOT NULL REFERENCES doc.embedding_model(embedding_model_id) ON DELETE RESTRICT,

  serving_index       text NOT NULL DEFAULT 'weaviate', -- weaviate, qdrant, pgvector, etc.
  serving_object_id   text,                             -- external object id in the serving index

  embedded_at         timestamptz NOT NULL DEFAULT now(),

  status              text NOT NULL DEFAULT 'active'
                      CHECK (status IN ('active', 'stale', 'failed')),
  error_text          text,

  PRIMARY KEY (chunk_id, embedding_model_id, serving_index)
);

CREATE UNIQUE INDEX IF NOT EXISTS doc_chunk_embedding_serving_object_uq
  ON doc.chunk_embedding (serving_index, serving_object_id)
  WHERE serving_object_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS doc_chunk_embedding_model_time_idx
  ON doc.chunk_embedding (embedding_model_id, embedded_at DESC);

CREATE INDEX IF NOT EXISTS doc_chunk_embedding_person_time_idx
  ON doc.chunk_embedding (person_id, embedded_at DESC);

-- Auto-fill/validate person_id from chunk for RLS friendliness.
CREATE OR REPLACE FUNCTION doc.set_chunk_embedding_person_id()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_person_id uuid;
BEGIN
  SELECT c.person_id INTO v_person_id
  FROM doc.chunk c
  WHERE c.chunk_id = NEW.chunk_id;

  IF v_person_id IS NULL THEN
    RAISE EXCEPTION 'doc.chunk % not found for chunk_embedding', NEW.chunk_id;
  END IF;

  IF NEW.person_id IS NULL THEN
    NEW.person_id := v_person_id;
  ELSIF NEW.person_id <> v_person_id THEN
    RAISE EXCEPTION 'chunk_embedding person_id % does not match chunk person_id %', NEW.person_id, v_person_id;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_doc_chunk_embedding_person_id ON doc.chunk_embedding;
CREATE TRIGGER trg_doc_chunk_embedding_person_id
BEFORE INSERT OR UPDATE ON doc.chunk_embedding
FOR EACH ROW
EXECUTE FUNCTION doc.set_chunk_embedding_person_id();
```

```sql
-- 04_schema_comms.sql
-- Email metadata on top of doc.document (raw .eml stored in SeaweedFS)

CREATE SCHEMA IF NOT EXISTS comms;

-- ---------- Email accounts ----------
CREATE TABLE IF NOT EXISTS comms.email_account (
  email_account_id  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  person_id         uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,

  email_address     citext NOT NULL,              -- e.g. mark@forgeco.com
  provider          text NOT NULL DEFAULT 'gmail', -- future-proofing
  is_active         boolean NOT NULL DEFAULT true,

  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS comms_email_account_address_uq
  ON comms.email_account (email_address);

CREATE INDEX IF NOT EXISTS comms_email_account_person_idx
  ON comms.email_account (person_id);

DROP TRIGGER IF EXISTS trg_comms_email_account_set_updated_at ON comms.email_account;
CREATE TRIGGER trg_comms_email_account_set_updated_at
BEFORE UPDATE ON comms.email_account
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- ---------- Threads ----------
CREATE TABLE IF NOT EXISTS comms.email_thread (
  email_thread_id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  person_id            uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,
  email_account_id     uuid NOT NULL REFERENCES comms.email_account(email_account_id) ON DELETE CASCADE,

  provider_thread_id   text NOT NULL,  -- Gmail thread id
  subject              text,
  first_message_at     timestamptz,
  last_message_at      timestamptz,
  snippet              text,

  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS comms_email_thread_provider_uq
  ON comms.email_thread (email_account_id, provider_thread_id);

CREATE INDEX IF NOT EXISTS comms_email_thread_person_last_idx
  ON comms.email_thread (person_id, last_message_at DESC);

DROP TRIGGER IF EXISTS trg_comms_email_thread_set_updated_at ON comms.email_thread;
CREATE TRIGGER trg_comms_email_thread_set_updated_at
BEFORE UPDATE ON comms.email_thread
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- Auto-fill/validate person_id from email_account
CREATE OR REPLACE FUNCTION comms.set_email_thread_person_id()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_person_id uuid;
BEGIN
  SELECT ea.person_id INTO v_person_id
  FROM comms.email_account ea
  WHERE ea.email_account_id = NEW.email_account_id;

  IF v_person_id IS NULL THEN
    RAISE EXCEPTION 'comms.email_account % not found for email_thread', NEW.email_account_id;
  END IF;

  IF NEW.person_id IS NULL THEN
    NEW.person_id := v_person_id;
  ELSIF NEW.person_id <> v_person_id THEN
    RAISE EXCEPTION 'email_thread person_id % does not match email_account person_id %', NEW.person_id, v_person_id;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_comms_email_thread_person_id ON comms.email_thread;
CREATE TRIGGER trg_comms_email_thread_person_id
BEFORE INSERT OR UPDATE ON comms.email_thread
FOR EACH ROW
EXECUTE FUNCTION comms.set_email_thread_person_id();

-- ---------- Email addresses (normalized) ----------
CREATE TABLE IF NOT EXISTS comms.email_address (
  email_address_id  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  address           citext NOT NULL,     -- user@domain.com
  display_name      text,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS comms_email_address_uq
  ON comms.email_address (address);

DROP TRIGGER IF EXISTS trg_comms_email_address_set_updated_at ON comms.email_address;
CREATE TRIGGER trg_comms_email_address_set_updated_at
BEFORE UPDATE ON comms.email_address
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- ---------- Messages ----------
-- Each message:
--  - has a core.event (event_type like "email.message") for timeline + correlations
--  - has a doc.document containing raw .eml object key + optional extracted text
CREATE TABLE IF NOT EXISTS comms.email_message (
  email_message_id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  person_id             uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,
  email_account_id      uuid NOT NULL REFERENCES comms.email_account(email_account_id) ON DELETE CASCADE,
  email_thread_id       uuid REFERENCES comms.email_thread(email_thread_id) ON DELETE SET NULL,

  event_id              uuid NOT NULL UNIQUE REFERENCES core.event(event_id) ON DELETE CASCADE,
  document_id           uuid NOT NULL UNIQUE REFERENCES doc.document(document_id) ON DELETE CASCADE,

  provider_message_id   text NOT NULL,   -- Gmail message id
  internet_message_id   text,            -- Message-ID header (if available)
  in_reply_to           text,

  subject               text,
  sent_at               timestamptz NOT NULL,  -- should match core.event.occurred_at for this message
  direction             text NOT NULL DEFAULT 'inbound'
                        CHECK (direction IN ('inbound', 'outbound', 'draft')),

  has_attachments       boolean NOT NULL DEFAULT false,
  size_bytes            integer,

  labels_json           jsonb,           -- optional: raw label list for quick debugging
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS comms_email_message_provider_uq
  ON comms.email_message (email_account_id, provider_message_id);

CREATE INDEX IF NOT EXISTS comms_email_message_person_sent_idx
  ON comms.email_message (person_id, sent_at DESC);

CREATE INDEX IF NOT EXISTS comms_email_message_thread_idx
  ON comms.email_message (email_thread_id, sent_at DESC);

DROP TRIGGER IF EXISTS trg_comms_email_message_set_updated_at ON comms.email_message;
CREATE TRIGGER trg_comms_email_message_set_updated_at
BEFORE UPDATE ON comms.email_message
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- Validate person_id consistency across account/event/document; default sent_at from event if omitted.
CREATE OR REPLACE FUNCTION comms.validate_email_message_links()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_account_person uuid;
  v_event_person   uuid;
  v_event_time     timestamptz;
  v_doc_person     uuid;
BEGIN
  SELECT person_id INTO v_account_person
  FROM comms.email_account
  WHERE email_account_id = NEW.email_account_id;

  IF v_account_person IS NULL THEN
    RAISE EXCEPTION 'comms.email_account % not found for email_message', NEW.email_account_id;
  END IF;

  IF NEW.person_id IS NULL THEN
    NEW.person_id := v_account_person;
  ELSIF NEW.person_id <> v_account_person THEN
    RAISE EXCEPTION 'email_message person_id % does not match email_account person_id %', NEW.person_id, v_account_person;
  END IF;

  SELECT person_id, occurred_at INTO v_event_person, v_event_time
  FROM core.event
  WHERE event_id = NEW.event_id;

  IF v_event_person IS NULL THEN
    RAISE EXCEPTION 'core.event % not found for email_message', NEW.event_id;
  END IF;

  IF v_event_person <> NEW.person_id THEN
    RAISE EXCEPTION 'email_message person_id % does not match core.event person_id %', NEW.person_id, v_event_person;
  END IF;

  IF NEW.sent_at IS NULL THEN
    NEW.sent_at := v_event_time;
  ELSIF NEW.sent_at <> v_event_time THEN
    RAISE EXCEPTION 'email_message.sent_at % must match core.event.occurred_at %', NEW.sent_at, v_event_time;
  END IF;

  SELECT person_id INTO v_doc_person
  FROM doc.document
  WHERE document_id = NEW.document_id;

  IF v_doc_person IS NULL THEN
    RAISE EXCEPTION 'doc.document % not found for email_message', NEW.document_id;
  END IF;

  IF v_doc_person <> NEW.person_id THEN
    RAISE EXCEPTION 'email_message person_id % does not match doc.document person_id %', NEW.person_id, v_doc_person;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_comms_email_message_validate_links ON comms.email_message;
CREATE TRIGGER trg_comms_email_message_validate_links
BEFORE INSERT OR UPDATE ON comms.email_message
FOR EACH ROW
EXECUTE FUNCTION comms.validate_email_message_links();

-- ---------- Message participants (from/to/cc/bcc/reply_to) ----------
CREATE TABLE IF NOT EXISTS comms.email_message_recipient (
  email_message_id   uuid NOT NULL REFERENCES comms.email_message(email_message_id) ON DELETE CASCADE,
  email_address_id   uuid NOT NULL REFERENCES comms.email_address(email_address_id) ON DELETE RESTRICT,
  recipient_type     text NOT NULL CHECK (recipient_type IN ('from', 'to', 'cc', 'bcc', 'reply_to')),
  sort_order         integer NOT NULL DEFAULT 0,
  PRIMARY KEY (email_message_id, email_address_id, recipient_type)
);

CREATE INDEX IF NOT EXISTS comms_email_message_recipient_addr_idx
  ON comms.email_message_recipient (email_address_id);

-- ---------- Labels (normalized) ----------
CREATE TABLE IF NOT EXISTS comms.email_label (
  email_label_id     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  person_id          uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,
  email_account_id   uuid NOT NULL REFERENCES comms.email_account(email_account_id) ON DELETE CASCADE,
  label_name         text NOT NULL, -- Gmail label name
  label_type         text,          -- system/user (optional)
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS comms_email_label_uq
  ON comms.email_label (email_account_id, label_name);

DROP TRIGGER IF EXISTS trg_comms_email_label_set_updated_at ON comms.email_label;
CREATE TRIGGER trg_comms_email_label_set_updated_at
BEFORE UPDATE ON comms.email_label
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

CREATE OR REPLACE FUNCTION comms.set_email_label_person_id()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_person_id uuid;
BEGIN
  SELECT person_id INTO v_person_id
  FROM comms.email_account
  WHERE email_account_id = NEW.email_account_id;

  IF v_person_id IS NULL THEN
    RAISE EXCEPTION 'comms.email_account % not found for email_label', NEW.email_account_id;
  END IF;

  IF NEW.person_id IS NULL THEN
    NEW.person_id := v_person_id;
  ELSIF NEW.person_id <> v_person_id THEN
    RAISE EXCEPTION 'email_label person_id % does not match email_account person_id %', NEW.person_id, v_person_id;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_comms_email_label_person_id ON comms.email_label;
CREATE TRIGGER trg_comms_email_label_person_id
BEFORE INSERT OR UPDATE ON comms.email_label
FOR EACH ROW
EXECUTE FUNCTION comms.set_email_label_person_id();

CREATE TABLE IF NOT EXISTS comms.email_message_label (
  email_message_id  uuid NOT NULL REFERENCES comms.email_message(email_message_id) ON DELETE CASCADE,
  email_label_id    uuid NOT NULL REFERENCES comms.email_label(email_label_id) ON DELETE CASCADE,
  PRIMARY KEY (email_message_id, email_label_id)
);

-- ---------- Attachments ----------
-- Each attachment is also a doc.document (raw_object_key points to the stored file in SeaweedFS).
CREATE TABLE IF NOT EXISTS comms.email_attachment (
  email_attachment_id     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email_message_id        uuid NOT NULL REFERENCES comms.email_message(email_message_id) ON DELETE CASCADE,
  document_id             uuid NOT NULL REFERENCES doc.document(document_id) ON DELETE RESTRICT,

  filename                text,
  mime_type               text,
  size_bytes              bigint,
  sha256_hex              text,

  provider_attachment_id  text, -- Gmail attachment id (if available)
  created_at              timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS comms_email_attachment_provider_uq
  ON comms.email_attachment (email_message_id, provider_attachment_id)
  WHERE provider_attachment_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS comms_email_attachment_message_idx
  ON comms.email_attachment (email_message_id);
```

```sql
-- 05_schema_finance.sql
-- Financial transactions (personal + business separated via finance.book)

CREATE SCHEMA IF NOT EXISTS finance;

-- ---------- Books (separation boundary: personal vs business) ----------
CREATE TABLE IF NOT EXISTS finance.book (
  book_id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_person_id  uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,
  book_name        text NOT NULL,  -- e.g. "Kasdorf Household", "Forgeco"
  book_type        text NOT NULL CHECK (book_type IN ('personal', 'business')),
  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS finance_book_owner_name_uq
  ON finance.book (owner_person_id, book_name);

DROP TRIGGER IF EXISTS trg_finance_book_set_updated_at ON finance.book;
CREATE TRIGGER trg_finance_book_set_updated_at
BEFORE UPDATE ON finance.book
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- ---------- Accounts ----------
CREATE TABLE IF NOT EXISTS finance.account (
  account_id       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  book_id          uuid NOT NULL REFERENCES finance.book(book_id) ON DELETE CASCADE,

  account_name     text NOT NULL,
  account_type     text NOT NULL CHECK (account_type IN (
                    'checking','savings','credit_card','cash','investment','loan','other'
                  )),
  institution_name text,
  currency_code    char(3) NOT NULL DEFAULT 'USD',
  external_ref     text,        -- QBO account id / bank id / etc.
  is_active        boolean NOT NULL DEFAULT true,

  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS finance_account_book_name_uq
  ON finance.account (book_id, account_name);

CREATE INDEX IF NOT EXISTS finance_account_book_idx
  ON finance.account (book_id);

DROP TRIGGER IF EXISTS trg_finance_account_set_updated_at ON finance.account;
CREATE TRIGGER trg_finance_account_set_updated_at
BEFORE UPDATE ON finance.account
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- ---------- Merchants (global canonicalization) ----------
CREATE TABLE IF NOT EXISTS finance.merchant (
  merchant_id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  canonical_name   text NOT NULL,
  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS finance_merchant_name_uq
  ON finance.merchant (canonical_name);

DROP TRIGGER IF EXISTS trg_finance_merchant_set_updated_at ON finance.merchant;
CREATE TRIGGER trg_finance_merchant_set_updated_at
BEFORE UPDATE ON finance.merchant
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- ---------- Categories (book-scoped) ----------
CREATE TABLE IF NOT EXISTS finance.category (
  category_id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  book_id            uuid NOT NULL REFERENCES finance.book(book_id) ON DELETE CASCADE,
  category_name      text NOT NULL,
  parent_category_id uuid REFERENCES finance.category(category_id) ON DELETE SET NULL,

  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS finance_category_book_name_uq
  ON finance.category (book_id, category_name);

CREATE INDEX IF NOT EXISTS finance_category_book_idx
  ON finance.category (book_id);

DROP TRIGGER IF EXISTS trg_finance_category_set_updated_at ON finance.category;
CREATE TRIGGER trg_finance_category_set_updated_at
BEFORE UPDATE ON finance.category
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- ---------- Transactions ----------
-- Each transaction MUST have a core.event for timeline + cross-correlation.
-- core.event.occurred_at is the canonical transaction time anchor (posted/transaction date as you choose).
CREATE TABLE IF NOT EXISTS finance.transaction (
  transaction_id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  book_id             uuid NOT NULL REFERENCES finance.book(book_id) ON DELETE CASCADE,
  person_id           uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,

  event_id            uuid NOT NULL UNIQUE REFERENCES core.event(event_id) ON DELETE CASCADE,

  account_id          uuid REFERENCES finance.account(account_id) ON DELETE SET NULL,
  merchant_id         uuid REFERENCES finance.merchant(merchant_id) ON DELETE SET NULL,

  merchant_name_raw   text, -- raw merchant/payee string from source
  description         text,

  amount              numeric(14,2) NOT NULL,   -- sign convention decided by ingestion (see Decisions)
  currency_code       char(3) NOT NULL DEFAULT 'USD',

  category_id         uuid REFERENCES finance.category(category_id) ON DELETE SET NULL, -- optional when using splits

  pending             boolean NOT NULL DEFAULT false,
  cleared             boolean NOT NULL DEFAULT false,

  source_system       text,   -- quickbooks, bank_csv, manual, amazon, etc.
  source_ref          text,   -- provider-specific transaction id
  payload_id          uuid REFERENCES raw.external_payload(payload_id) ON DELETE SET NULL,

  raw_object_key      text,   -- optional: statement/receipt object key in SeaweedFS
  receipt_document_id uuid REFERENCES doc.document(document_id) ON DELETE SET NULL,

  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS finance_tx_book_idx
  ON finance.transaction (book_id);

CREATE INDEX IF NOT EXISTS finance_tx_person_idx
  ON finance.transaction (person_id);

CREATE INDEX IF NOT EXISTS finance_tx_account_idx
  ON finance.transaction (account_id);

CREATE INDEX IF NOT EXISTS finance_tx_merchant_idx
  ON finance.transaction (merchant_id);

CREATE UNIQUE INDEX IF NOT EXISTS finance_tx_source_uq
  ON finance.transaction (source_system, source_ref)
  WHERE source_system IS NOT NULL AND source_ref IS NOT NULL;

DROP TRIGGER IF EXISTS trg_finance_transaction_set_updated_at ON finance.transaction;
CREATE TRIGGER trg_finance_transaction_set_updated_at
BEFORE UPDATE ON finance.transaction
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- Validate person_id matches core.event.person_id; validate account/book consistency when provided.
CREATE OR REPLACE FUNCTION finance.validate_transaction_links()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_event_person uuid;
  v_account_book uuid;
BEGIN
  SELECT person_id INTO v_event_person
  FROM core.event
  WHERE event_id = NEW.event_id;

  IF v_event_person IS NULL THEN
    RAISE EXCEPTION 'core.event % not found for finance.transaction', NEW.event_id;
  END IF;

  IF v_event_person <> NEW.person_id THEN
    RAISE EXCEPTION 'finance.transaction person_id % does not match core.event person_id %', NEW.person_id, v_event_person;
  END IF;

  IF NEW.account_id IS NOT NULL THEN
    SELECT book_id INTO v_account_book
    FROM finance.account
    WHERE account_id = NEW.account_id;

    IF v_account_book IS NULL THEN
      RAISE EXCEPTION 'finance.account % not found for finance.transaction', NEW.account_id;
    END IF;

    IF v_account_book <> NEW.book_id THEN
      RAISE EXCEPTION 'finance.transaction book_id % does not match finance.account book_id %', NEW.book_id, v_account_book;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_finance_transaction_validate_links ON finance.transaction;
CREATE TRIGGER trg_finance_transaction_validate_links
BEFORE INSERT OR UPDATE ON finance.transaction
FOR EACH ROW
EXECUTE FUNCTION finance.validate_transaction_links();

-- ---------- Optional splits / line items ----------
-- Use when a single transaction spans multiple categories (common with QBO lines and Amazon orders).
CREATE TABLE IF NOT EXISTS finance.transaction_line (
  transaction_line_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  transaction_id      uuid NOT NULL REFERENCES finance.transaction(transaction_id) ON DELETE CASCADE,

  book_id             uuid NOT NULL REFERENCES finance.book(book_id) ON DELETE CASCADE,
  person_id           uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,

  line_number         integer NOT NULL DEFAULT 0,
  amount              numeric(14,2) NOT NULL,
  category_id         uuid REFERENCES finance.category(category_id) ON DELETE SET NULL,
  memo                text,

  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),

  UNIQUE (transaction_id, line_number)
);

CREATE INDEX IF NOT EXISTS finance_tx_line_tx_idx
  ON finance.transaction_line (transaction_id);

DROP TRIGGER IF EXISTS trg_finance_transaction_line_set_updated_at ON finance.transaction_line;
CREATE TRIGGER trg_finance_transaction_line_set_updated_at
BEFORE UPDATE ON finance.transaction_line
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- Auto-fill book_id/person_id from parent transaction for RLS friendliness.
CREATE OR REPLACE FUNCTION finance.set_transaction_line_scope()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_book_id   uuid;
  v_person_id uuid;
BEGIN
  SELECT t.book_id, t.person_id INTO v_book_id, v_person_id
  FROM finance.transaction t
  WHERE t.transaction_id = NEW.transaction_id;

  IF v_book_id IS NULL THEN
    RAISE EXCEPTION 'finance.transaction % not found for finance.transaction_line', NEW.transaction_id;
  END IF;

  IF NEW.book_id IS NULL THEN
    NEW.book_id := v_book_id;
  ELSIF NEW.book_id <> v_book_id THEN
    RAISE EXCEPTION 'transaction_line book_id % does not match transaction book_id %', NEW.book_id, v_book_id;
  END IF;

  IF NEW.person_id IS NULL THEN
    NEW.person_id := v_person_id;
  ELSIF NEW.person_id <> v_person_id THEN
    RAISE EXCEPTION 'transaction_line person_id % does not match transaction person_id %', NEW.person_id, v_person_id;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_finance_transaction_line_scope ON finance.transaction_line;
CREATE TRIGGER trg_finance_transaction_line_scope
BEFORE INSERT OR UPDATE ON finance.transaction_line
FOR EACH ROW
EXECUTE FUNCTION finance.set_transaction_line_scope();
```

```sql
-- 06_schema_supplement.sql
-- Supplements + stimulants + nootropics + prescriptions (logged as intakes with exact timestamps via core.event)

CREATE SCHEMA IF NOT EXISTS supplement;

-- ---------- Compounds ----------
CREATE TABLE IF NOT EXISTS supplement.compound (
  compound_id     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  canonical_name  text NOT NULL, -- e.g. "Zinc", "Caffeine", "Modafinil"
  notes           text,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS supplement_compound_name_uq
  ON supplement.compound (canonical_name);

DROP TRIGGER IF EXISTS trg_supplement_compound_set_updated_at ON supplement.compound;
CREATE TRIGGER trg_supplement_compound_set_updated_at
BEFORE UPDATE ON supplement.compound
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- ---------- Products ----------
-- A product is a specific supplement/prescription SKU or a generalized thing you log (e.g., "Coffee Beans").
CREATE TABLE IF NOT EXISTS supplement.product (
  product_id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  person_id             uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,

  visibility_scope      text NOT NULL DEFAULT 'private'
                        CHECK (visibility_scope IN ('private', 'shared')),

  name                  text NOT NULL,
  brand                 text,
  form                  text NOT NULL DEFAULT 'other'
                        CHECK (form IN ('capsule','tablet','powder','liquid','gum','spray','other')),

  product_type          text NOT NULL DEFAULT 'supplement'
                        CHECK (product_type IN ('supplement','stimulant','nootropic','caffeine','prescription')),

  standard_dose_amount  numeric,
  standard_dose_unit    text,

  notes                 text,
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now()
);

-- Prevent obvious dupes while staying flexible (case-insensitive-ish uniqueness)
CREATE UNIQUE INDEX IF NOT EXISTS supplement_product_dedupe_uq
  ON supplement.product (person_id, lower(coalesce(brand, '')), lower(name), form);

CREATE INDEX IF NOT EXISTS supplement_product_person_idx
  ON supplement.product (person_id, created_at DESC);

DROP TRIGGER IF EXISTS trg_supplement_product_set_updated_at ON supplement.product;
CREATE TRIGGER trg_supplement_product_set_updated_at
BEFORE UPDATE ON supplement.product
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- ---------- Product composition ----------
CREATE TABLE IF NOT EXISTS supplement.product_compound (
  product_id          uuid NOT NULL REFERENCES supplement.product(product_id) ON DELETE CASCADE,
  compound_id         uuid NOT NULL REFERENCES supplement.compound(compound_id) ON DELETE RESTRICT,
  amount_per_serving  numeric,
  unit                text,
  is_primary          boolean NOT NULL DEFAULT false,
  PRIMARY KEY (product_id, compound_id)
);

CREATE INDEX IF NOT EXISTS supplement_product_compound_compound_idx
  ON supplement.product_compound (compound_id);

-- ---------- Intake log ----------
-- Each intake has a core.event (event_type like "supplement.intake") with occurred_at as the exact timestamp.
CREATE TABLE IF NOT EXISTS supplement.intake (
  intake_id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id         uuid NOT NULL UNIQUE REFERENCES core.event(event_id) ON DELETE CASCADE,
  person_id        uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,

  product_id       uuid NOT NULL REFERENCES supplement.product(product_id) ON DELETE RESTRICT,

  dose_amount      numeric NOT NULL,
  dose_unit        text NOT NULL, -- mg, g, IU, capsules, etc.

  time_bucket      text CHECK (time_bucket IN ('AM','PM','night')), -- optional: explicit AM/PM for prescriptions
  route            text NOT NULL DEFAULT 'oral',

  notes            text,
  note_document_id uuid REFERENCES doc.document(document_id) ON DELETE SET NULL,

  source_system    text, -- manual, automation, etc.
  source_ref       text,

  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS supplement_intake_person_idx
  ON supplement.intake (person_id, created_at DESC);

CREATE INDEX IF NOT EXISTS supplement_intake_product_idx
  ON supplement.intake (product_id);

CREATE UNIQUE INDEX IF NOT EXISTS supplement_intake_source_uq
  ON supplement.intake (source_system, source_ref)
  WHERE source_system IS NOT NULL AND source_ref IS NOT NULL;

DROP TRIGGER IF EXISTS trg_supplement_intake_set_updated_at ON supplement.intake;
CREATE TRIGGER trg_supplement_intake_set_updated_at
BEFORE UPDATE ON supplement.intake
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- Validate person_id matches core.event.person_id; validate note_document person_id when present.
CREATE OR REPLACE FUNCTION supplement.validate_intake_links()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_event_person uuid;
  v_doc_person   uuid;
BEGIN
  SELECT person_id INTO v_event_person
  FROM core.event
  WHERE event_id = NEW.event_id;

  IF v_event_person IS NULL THEN
    RAISE EXCEPTION 'core.event % not found for supplement.intake', NEW.event_id;
  END IF;

  IF v_event_person <> NEW.person_id THEN
    RAISE EXCEPTION 'supplement.intake person_id % does not match core.event person_id %', NEW.person_id, v_event_person;
  END IF;

  IF NEW.note_document_id IS NOT NULL THEN
    SELECT person_id INTO v_doc_person
    FROM doc.document
    WHERE document_id = NEW.note_document_id;

    IF v_doc_person IS NULL THEN
      RAISE EXCEPTION 'doc.document % not found for supplement.intake.note_document_id', NEW.note_document_id;
    END IF;

    IF v_doc_person <> NEW.person_id THEN
      RAISE EXCEPTION 'supplement.intake person_id % does not match note document person_id %', NEW.person_id, v_doc_person;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_supplement_intake_validate_links ON supplement.intake;
CREATE TRIGGER trg_supplement_intake_validate_links
BEFORE INSERT OR UPDATE ON supplement.intake
FOR EACH ROW
EXECUTE FUNCTION supplement.validate_intake_links();

-- ---------- Caffeine: coffee beans logging ----------
-- This is a specialization that hangs off supplement.intake so it still participates in the timeline spine.
-- Use a dedicated product (product_type='caffeine') and store coffee-specific inputs + computed caffeine_mg.
CREATE TABLE IF NOT EXISTS supplement.caffeine_coffee_intake (
  caffeine_coffee_intake_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  intake_id                 uuid NOT NULL UNIQUE REFERENCES supplement.intake(intake_id) ON DELETE CASCADE,

  beans_grams               numeric NOT NULL, -- grams of coffee beans
  roast_type                text,             -- light/medium/dark (free text)
  brew_method               text,             -- espresso/drip/french_press/etc (free text)

  caffeine_mg               numeric NOT NULL, -- computed result stored for correlations
  calc_method               text,             -- optional: how you computed it

  created_at                timestamptz NOT NULL DEFAULT now(),
  updated_at                timestamptz NOT NULL DEFAULT now()
);

DROP TRIGGER IF EXISTS trg_supplement_caffeine_coffee_set_updated_at ON supplement.caffeine_coffee_intake;
CREATE TRIGGER trg_supplement_caffeine_coffee_set_updated_at
BEFORE UPDATE ON supplement.caffeine_coffee_intake
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();
```

```sql
-- 07_schema_health.sql
-- Lab panels + results; each lab result emits a core.observation for cross-correlation

CREATE SCHEMA IF NOT EXISTS health;

-- ---------- Lab panels ----------
-- A lab panel is a draw/collection event (core.event) with optional raw artifact (.pdf) stored in SeaweedFS.
CREATE TABLE IF NOT EXISTS health.lab_panel (
  lab_panel_id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id          uuid NOT NULL UNIQUE REFERENCES core.event(event_id) ON DELETE CASCADE,
  person_id         uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,

  lab_name          text NOT NULL,  -- Labcorp, Quest, etc.
  ordering_provider text,
  specimen_type     text,           -- blood/urine/etc (optional)

  raw_object_key    text,           -- PDF/CSV object key in SeaweedFS (optional)
  document_id       uuid REFERENCES doc.document(document_id) ON DELETE SET NULL,

  source_system     text,           -- portal_export, labcorp_api, manual, etc.
  source_ref        text,           -- provider stable id (if available)

  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS health_lab_panel_person_idx
  ON health.lab_panel (person_id, created_at DESC);

CREATE UNIQUE INDEX IF NOT EXISTS health_lab_panel_source_uq
  ON health.lab_panel (source_system, source_ref)
  WHERE source_system IS NOT NULL AND source_ref IS NOT NULL;

DROP TRIGGER IF EXISTS trg_health_lab_panel_set_updated_at ON health.lab_panel;
CREATE TRIGGER trg_health_lab_panel_set_updated_at
BEFORE UPDATE ON health.lab_panel
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- Validate person_id matches core.event.person_id; validate document person_id when present.
CREATE OR REPLACE FUNCTION health.validate_lab_panel_links()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_event_person uuid;
  v_doc_person   uuid;
BEGIN
  SELECT person_id INTO v_event_person
  FROM core.event
  WHERE event_id = NEW.event_id;

  IF v_event_person IS NULL THEN
    RAISE EXCEPTION 'core.event % not found for health.lab_panel', NEW.event_id;
  END IF;

  IF v_event_person <> NEW.person_id THEN
    RAISE EXCEPTION 'health.lab_panel person_id % does not match core.event person_id %', NEW.person_id, v_event_person;
  END IF;

  IF NEW.document_id IS NOT NULL THEN
    SELECT person_id INTO v_doc_person
    FROM doc.document
    WHERE document_id = NEW.document_id;

    IF v_doc_person IS NULL THEN
      RAISE EXCEPTION 'doc.document % not found for health.lab_panel.document_id', NEW.document_id;
    END IF;

    IF v_doc_person <> NEW.person_id THEN
      RAISE EXCEPTION 'health.lab_panel person_id % does not match doc.document person_id %', NEW.person_id, v_doc_person;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_health_lab_panel_validate_links ON health.lab_panel;
CREATE TRIGGER trg_health_lab_panel_validate_links
BEFORE INSERT OR UPDATE ON health.lab_panel
FOR EACH ROW
EXECUTE FUNCTION health.validate_lab_panel_links();

-- ---------- Lab results ----------
-- Each analyte result is normalized here AND emitted to core.observation.
CREATE TABLE IF NOT EXISTS health.lab_result (
  lab_result_id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  lab_panel_id         uuid NOT NULL REFERENCES health.lab_panel(lab_panel_id) ON DELETE CASCADE,

  observation_id       uuid UNIQUE REFERENCES core.observation(observation_id) ON DELETE SET NULL,

  analyte_name         text NOT NULL,  -- e.g. "LDL Chol Calc"
  analyte_code_system  text NOT NULL DEFAULT 'custom', -- loinc/custom/etc
  analyte_code         text,           -- e.g. LOINC code if you have it

  value_numeric        double precision,
  value_text           text,
  unit                 text,

  reference_low        double precision,
  reference_high       double precision,
  reference_text       text,

  flag                text CHECK (flag IN ('L','H','N','A')), -- Low/High/Normal/Abnormal
  comment             text,

  created_at           timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS health_lab_result_panel_idx
  ON health.lab_result (lab_panel_id);

CREATE INDEX IF NOT EXISTS health_lab_result_analyte_code_idx
  ON health.lab_result (analyte_code_system, analyte_code);

CREATE INDEX IF NOT EXISTS health_lab_result_analyte_name_idx
  ON health.lab_result (analyte_name);

-- ---------- Observation emission trigger ----------
-- This keeps core.observation populated for cross-domain correlations.
-- obs_type mapping:
--   if analyte_code present:  "lab.<code_system>:<code>"
--   else:                    "lab.<normalized_analyte_name>"
CREATE OR REPLACE FUNCTION health.sync_observation_from_lab_result()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_person_id   uuid;
  v_event_id    uuid;
  v_observed_at timestamptz;
  v_obs_type    text;
  v_json        jsonb;
BEGIN
  SELECT lp.person_id, lp.event_id INTO v_person_id, v_event_id
  FROM health.lab_panel lp
  WHERE lp.lab_panel_id = NEW.lab_panel_id;

  IF v_person_id IS NULL THEN
    RAISE EXCEPTION 'health.lab_panel % not found for health.lab_result', NEW.lab_panel_id;
  END IF;

  SELECT e.occurred_at INTO v_observed_at
  FROM core.event e
  WHERE e.event_id = v_event_id;

  IF v_observed_at IS NULL THEN
    RAISE EXCEPTION 'core.event % not found for health.lab_panel.event_id', v_event_id;
  END IF;

  IF NEW.analyte_code IS NOT NULL AND length(trim(NEW.analyte_code)) > 0 THEN
    v_obs_type := format('lab.%s:%s', NEW.analyte_code_system, NEW.analyte_code);
  ELSE
    v_obs_type := 'lab.' || lower(regexp_replace(trim(NEW.analyte_name), '[^a-zA-Z0-9]+', '_', 'g'));
  END IF;

  v_json := jsonb_build_object(
    'reference_low', NEW.reference_low,
    'reference_high', NEW.reference_high,
    'reference_text', NEW.reference_text,
    'flag', NEW.flag,
    'analyte_name', NEW.analyte_name,
    'analyte_code_system', NEW.analyte_code_system,
    'analyte_code', NEW.analyte_code
  );

  -- INSERT on first write; UPDATE the observation if lab_result is edited later.
  IF TG_OP = 'INSERT' THEN
    INSERT INTO core.observation (
      observation_id,
      person_id,
      event_id,
      observed_at,
      obs_type,
      numeric_value,
      text_value,
      unit,
      json_value,
      source_system,
      source_ref,
      created_at
    )
    VALUES (
      COALESCE(NEW.observation_id, gen_random_uuid()),
      v_person_id,
      v_event_id,
      v_observed_at,
      v_obs_type,
      NEW.value_numeric,
      NEW.value_text,
      NEW.unit,
      v_json,
      'lab',
      NEW.lab_result_id::text,
      now()
    )
    RETURNING observation_id INTO NEW.observation_id;

    RETURN NEW;
  END IF;

  IF TG_OP = 'UPDATE' THEN
    IF NEW.observation_id IS NULL THEN
      -- If someone nulls it out, re-create to preserve the contract.
      INSERT INTO core.observation (
        observation_id, person_id, event_id, observed_at, obs_type,
        numeric_value, text_value, unit, json_value, source_system, source_ref, created_at
      )
      VALUES (
        gen_random_uuid(),
        v_person_id,
        v_event_id,
        v_observed_at,
        v_obs_type,
        NEW.value_numeric,
        NEW.value_text,
        NEW.unit,
        v_json,
        'lab',
        NEW.lab_result_id::text,
        now()
      )
      RETURNING observation_id INTO NEW.observation_id;

      RETURN NEW;
    END IF;

    UPDATE core.observation
    SET
      person_id      = v_person_id,
      event_id       = v_event_id,
      observed_at    = v_observed_at,
      obs_type       = v_obs_type,
      numeric_value  = NEW.value_numeric,
      text_value     = NEW.value_text,
      unit           = NEW.unit,
      json_value     = v_json,
      source_system  = 'lab',
      source_ref     = NEW.lab_result_id::text
    WHERE observation_id = NEW.observation_id;

    RETURN NEW;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_health_lab_result_sync_observation_ins ON health.lab_result;
CREATE TRIGGER trg_health_lab_result_sync_observation_ins
BEFORE INSERT ON health.lab_result
FOR EACH ROW
EXECUTE FUNCTION health.sync_observation_from_lab_result();

DROP TRIGGER IF EXISTS trg_health_lab_result_sync_observation_upd ON health.lab_result;
CREATE TRIGGER trg_health_lab_result_sync_observation_upd
BEFORE UPDATE ON health.lab_result
FOR EACH ROW
EXECUTE FUNCTION health.sync_observation_from_lab_result();

-- Optional cleanup: delete the derived observation if a lab result is deleted.
CREATE OR REPLACE FUNCTION health.delete_observation_on_lab_result_delete()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF OLD.observation_id IS NOT NULL THEN
    DELETE FROM core.observation WHERE observation_id = OLD.observation_id;
  END IF;
  RETURN OLD;
END;
$$;

DROP TRIGGER IF EXISTS trg_health_lab_result_delete_observation ON health.lab_result;
CREATE TRIGGER trg_health_lab_result_delete_observation
AFTER DELETE ON health.lab_result
FOR EACH ROW
EXECUTE FUNCTION health.delete_observation_on_lab_result_delete();
```

```sql
-- 08_schema_wellbeing.sql
-- Mood + subjective wellbeing check-ins (timestamped; free text note links to doc.document for chunking/embeddings)

CREATE SCHEMA IF NOT EXISTS wellbeing;

CREATE TABLE IF NOT EXISTS wellbeing.checkin (
  checkin_id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id          uuid NOT NULL UNIQUE REFERENCES core.event(event_id) ON DELETE CASCADE,
  person_id         uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,

  energy            smallint CHECK (energy BETWEEN 1 AND 10),
  mood              smallint CHECK (mood BETWEEN 1 AND 10),
  focus             smallint CHECK (focus BETWEEN 1 AND 10),
  stress            smallint CHECK (stress BETWEEN 1 AND 10),

  note_document_id  uuid REFERENCES doc.document(document_id) ON DELETE SET NULL, -- store note text in doc.document.content_text

  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS wellbeing_checkin_person_idx
  ON wellbeing.checkin (person_id, created_at DESC);

CREATE INDEX IF NOT EXISTS wellbeing_checkin_event_idx
  ON wellbeing.checkin (event_id);

DROP TRIGGER IF EXISTS trg_wellbeing_checkin_set_updated_at ON wellbeing.checkin;
CREATE TRIGGER trg_wellbeing_checkin_set_updated_at
BEFORE UPDATE ON wellbeing.checkin
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- Validate person_id matches core.event.person_id; validate note doc person_id when present.
CREATE OR REPLACE FUNCTION wellbeing.validate_checkin_links()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_event_person uuid;
  v_doc_person   uuid;
BEGIN
  SELECT person_id INTO v_event_person
  FROM core.event
  WHERE event_id = NEW.event_id;

  IF v_event_person IS NULL THEN
    RAISE EXCEPTION 'core.event % not found for wellbeing.checkin', NEW.event_id;
  END IF;

  IF v_event_person <> NEW.person_id THEN
    RAISE EXCEPTION 'wellbeing.checkin person_id % does not match core.event person_id %', NEW.person_id, v_event_person;
  END IF;

  IF NEW.note_document_id IS NOT NULL THEN
    SELECT person_id INTO v_doc_person
    FROM doc.document
    WHERE document_id = NEW.note_document_id;

    IF v_doc_person IS NULL THEN
      RAISE EXCEPTION 'doc.document % not found for wellbeing.checkin.note_document_id', NEW.note_document_id;
    END IF;

    IF v_doc_person <> NEW.person_id THEN
      RAISE EXCEPTION 'wellbeing.checkin person_id % does not match note document person_id %', NEW.person_id, v_doc_person;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_wellbeing_checkin_validate_links ON wellbeing.checkin;
CREATE TRIGGER trg_wellbeing_checkin_validate_links
BEFORE INSERT OR UPDATE ON wellbeing.checkin
FOR EACH ROW
EXECUTE FUNCTION wellbeing.validate_checkin_links();
```

```sql
-- 09_schema_ai_session.sql
-- AI conversation sessions + message logs (canonical), with doc.document linkage for raw transcript storage + chunking/embeddings

CREATE SCHEMA IF NOT EXISTS ai_session;

-- ---------- Sessions ----------
-- Session is anchored by core.event:
--   core.event.occurred_at = started_at
--   core.event.ended_at    = ended_at
CREATE TABLE IF NOT EXISTS ai_session.session (
  session_id       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id         uuid NOT NULL UNIQUE REFERENCES core.event(event_id) ON DELETE CASCADE,
  person_id        uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,

  started_at       timestamptz NOT NULL,
  ended_at         timestamptz,

  message_count    integer NOT NULL DEFAULT 0,

  channel          text,   -- chatgpt, local_llm, api, etc.
  model_name       text,   -- e.g. qwen2.5, gpt-*, etc.
  metadata_json    jsonb,  -- anything else useful (system prompt hash, gateway ids, etc.)

  document_id      uuid REFERENCES doc.document(document_id) ON DELETE SET NULL, -- raw session log/transcript stored as doc.document (optional)

  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS ai_session_session_person_started_idx
  ON ai_session.session (person_id, started_at DESC);

DROP TRIGGER IF EXISTS trg_ai_session_session_set_updated_at ON ai_session.session;
CREATE TRIGGER trg_ai_session_session_set_updated_at
BEFORE UPDATE ON ai_session.session
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- Enforce consistency between session.started_at/ended_at and core.event.occurred_at/ended_at.
-- Also validate person_id across event/document.
CREATE OR REPLACE FUNCTION ai_session.validate_session_links()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_event_person uuid;
  v_event_start  timestamptz;
  v_event_end    timestamptz;
  v_doc_person   uuid;
BEGIN
  SELECT person_id, occurred_at, ended_at INTO v_event_person, v_event_start, v_event_end
  FROM core.event
  WHERE event_id = NEW.event_id;

  IF v_event_person IS NULL THEN
    RAISE EXCEPTION 'core.event % not found for ai_session.session', NEW.event_id;
  END IF;

  IF v_event_person <> NEW.person_id THEN
    RAISE EXCEPTION 'ai_session.session person_id % does not match core.event person_id %', NEW.person_id, v_event_person;
  END IF;

  IF NEW.started_at <> v_event_start THEN
    RAISE EXCEPTION 'ai_session.session.started_at % must match core.event.occurred_at %', NEW.started_at, v_event_start;
  END IF;

  IF COALESCE(NEW.ended_at, 'epoch'::timestamptz) <> COALESCE(v_event_end, 'epoch'::timestamptz) THEN
    RAISE EXCEPTION 'ai_session.session.ended_at % must match core.event.ended_at %', NEW.ended_at, v_event_end;
  END IF;

  IF NEW.document_id IS NOT NULL THEN
    SELECT person_id INTO v_doc_person
    FROM doc.document
    WHERE document_id = NEW.document_id;

    IF v_doc_person IS NULL THEN
      RAISE EXCEPTION 'doc.document % not found for ai_session.session.document_id', NEW.document_id;
    END IF;

    IF v_doc_person <> NEW.person_id THEN
      RAISE EXCEPTION 'ai_session.session person_id % does not match doc.document person_id %', NEW.person_id, v_doc_person;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_ai_session_session_validate_links ON ai_session.session;
CREATE TRIGGER trg_ai_session_session_validate_links
BEFORE INSERT OR UPDATE ON ai_session.session
FOR EACH ROW
EXECUTE FUNCTION ai_session.validate_session_links();

-- ---------- Messages ----------
CREATE TABLE IF NOT EXISTS ai_session.message (
  message_id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id        uuid NOT NULL REFERENCES ai_session.session(session_id) ON DELETE CASCADE,

  sequence_num      integer NOT NULL DEFAULT 0, -- monotonically increasing within a session
  role              text NOT NULL CHECK (role IN ('system','user','assistant','tool')),

  occurred_at       timestamptz NOT NULL,
  content_text      text NOT NULL,

  prompt_tokens     integer, -- token counts if available
  completion_tokens integer,
  total_tokens      integer,

  tool_name         text,   -- when role='tool' (or assistant tool-call metadata)
  tool_json         jsonb,  -- tool args/result payloads when applicable

  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now(),

  UNIQUE (session_id, sequence_num)
);

CREATE INDEX IF NOT EXISTS ai_session_message_session_time_idx
  ON ai_session.message (session_id, occurred_at);

CREATE INDEX IF NOT EXISTS ai_session_message_role_idx
  ON ai_session.message (role);

DROP TRIGGER IF EXISTS trg_ai_session_message_set_updated_at ON ai_session.message;
CREATE TRIGGER trg_ai_session_message_set_updated_at
BEFORE UPDATE ON ai_session.message
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- Keep message_count reasonably accurate if you use direct SQL inserts (API can also manage this).
CREATE OR REPLACE FUNCTION ai_session.bump_message_count()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE ai_session.session
  SET message_count = message_count + 1
  WHERE session_id = NEW.session_id;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_ai_session_message_bump_count ON ai_session.message;
CREATE TRIGGER trg_ai_session_message_bump_count
AFTER INSERT ON ai_session.message
FOR EACH ROW
EXECUTE FUNCTION ai_session.bump_message_count();
```

---

## Decisions to make (painful to reverse later)

1. **Finance amount sign convention**

* Decide now: do you store *spend as negative* (common in ledgers) or store *absolute value + separate direction*?
* Current schema assumes **signed `amount`**. If you want direction instead, change now before ingestion.

2. **Lab analyte coding standard**

* If you can get **LOINC** codes (or any stable analyte IDs), do it early.
* The schema supports `analyte_code_system` + `analyte_code`, and `core.observation.obs_type` becomes `lab.<system>:<code>` when present. This becomes your long-term join key for “LDL” across sources.

3. **Document “canonical text” policy**

* Do you want `doc.document.content_text` to be *authoritative* (full extracted text), or do you treat it as optional and rely on chunks?
* This impacts re-chunking + rebuild workflows. Current design: **chunks are canonical for retrieval**, content_text is optional.

4. **Visibility model**

* Today: `visibility_scope IN ('private','shared')`.
* If you anticipate richer access control (e.g., “shared but redacted”, “private to wife”), expand this now (new enum values are easy; changing semantics later is hard).

5. **Email identity normalization**

* Current design normalizes addresses in `comms.email_address`.
* Decide whether you also want a first-class `entity/person` mapping for frequent correspondents (can be added later, but doing it early improves long-term relationship queries).

If you want, I can also generate **seed scripts** for Phase 2 (e.g., create finance books “Kasdorf Household” + “Forgeco”, seed email accounts, seed common categories) in a `10_seed_phase2.sql`—but I kept this response strictly to the files you requested (03–09).