triqura-ecd/supabase/migrations/20260720000002_create_person_client_domain.sql

-- ============================================================================
-- PERSON / CLIENT DOMAIN
-- ============================================================================
-- Created: 2026-07-20
-- Source: docs/datamodel/deelmodellen/model-aanmelding.md §2.1-2.9, §2.14, §2.16
--   (PERSOON, ADRES, CONTACTGEGEVEN, CLIENT, CLIENTRELATIE, VERWIJZER,
--    PRAKTIJK_INSTELLING, CLIENT_HUISARTS, VERZEKERING, TOESTEMMING,
--    CLIENTPORTAAL_ACCOUNT) — the AANMELDING/VERWIJZING/ZORGEPISODE part of
--    that document is superseded by the referral/intake domains that follow
--    this migration, not translated here.
--
-- Naming: English identifiers per besluit B20. Dutch field names in the
-- source document are translated 1:1 in meaning; nothing renamed to a
-- different concept.
--
-- Privacy: raw BSN is never stored, only a SHA-256 hash (bsn_hash) — this
-- follows the platform-wide privacy rule (root CLAUDE.md), which is a
-- stricter requirement than the source document's "versleuteld (pgcrypto)"
-- wording. Reversible BSN storage for declaratie/Vecozo purposes, if ever
-- needed, is a separate, explicitly-scoped decision — not implemented here.
-- ============================================================================

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ============================================================================
-- person
-- ============================================================================
CREATE TABLE person (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  family_name       TEXT NOT NULL,
  name_prefix       TEXT,
  given_names       TEXT,
  initials          TEXT,
  preferred_name    TEXT,
  birth_date        DATE NOT NULL,
  gender            TEXT NOT NULL,           -- waardelijst geslacht
  gender_identity   TEXT,                    -- waardelijst genderidentiteit
  bsn_hash          TEXT,                    -- SHA-256, optioneel op PERSOON (rolgebonden eis via nudge)
  deceased          BOOLEAN NOT NULL DEFAULT false,
  deceased_at       DATE,

  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at        TIMESTAMPTZ,

  CONSTRAINT person_family_name_not_empty CHECK (length(trim(family_name)) > 0),
  CONSTRAINT person_deceased_at_requires_flag CHECK (deceased_at IS NULL OR deceased)
);

CREATE UNIQUE INDEX idx_person_bsn_hash ON person(bsn_hash) WHERE bsn_hash IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX idx_person_name ON person(family_name, given_names) WHERE deleted_at IS NULL;

COMMENT ON TABLE person IS 'Natuurlijke persoon, onafhankelijk van rollen (cliënt, contactpersoon, vertegenwoordiger, medewerker). model-aanmelding.md §2.1.';
COMMENT ON COLUMN person.bsn_hash IS 'SHA-256 hash van het BSN; geen raw BSN opgeslagen (platform-privacyregel). Naam+geboortedatum-duplicaten zijn een nudge, geen constraint.';

ALTER TABLE person ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON person FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_person_updated_at BEFORE UPDATE ON person FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- address
-- ============================================================================
CREATE TABLE address (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  person_id     UUID NOT NULL REFERENCES person(id) ON DELETE CASCADE,
  address_type  TEXT NOT NULL,               -- waardelijst adrestype
  street        TEXT NOT NULL,
  house_number  TEXT NOT NULL,
  house_number_addition TEXT,
  postal_code   TEXT NOT NULL,
  city          TEXT NOT NULL,
  country       TEXT NOT NULL DEFAULT 'NL',
  valid_from    DATE,
  valid_to      DATE,

  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at    TIMESTAMPTZ
);

-- max één actueel (valid_to leeg) adres per persoon per adrestype
CREATE UNIQUE INDEX idx_address_current_per_type ON address(person_id, address_type)
  WHERE valid_to IS NULL AND deleted_at IS NULL;
CREATE INDEX idx_address_person ON address(person_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE address IS 'model-aanmelding.md §2.2 — 0..n adressen per persoon (zib Patient).';

ALTER TABLE address ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON address FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_address_updated_at BEFORE UPDATE ON address FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- contact_detail
-- ============================================================================
CREATE TABLE contact_detail (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  person_id        UUID NOT NULL REFERENCES person(id) ON DELETE CASCADE,
  contact_type     TEXT NOT NULL,            -- waardelijst contactgegeven_type (telefoon/e-mail)
  contact_subtype  TEXT,                     -- waardelijst contactgegeven_soort
  value            TEXT NOT NULL,
  is_preferred     BOOLEAN NOT NULL DEFAULT false,

  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at       TIMESTAMPTZ
);

-- max één voorkeursgegeven per persoon per contacttype
CREATE UNIQUE INDEX idx_contact_detail_preferred ON contact_detail(person_id, contact_type)
  WHERE is_preferred AND deleted_at IS NULL;
CREATE INDEX idx_contact_detail_person ON contact_detail(person_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE contact_detail IS 'model-aanmelding.md §2.3 — telefoonnummers en e-mailadressen per persoon.';

ALTER TABLE contact_detail ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON contact_detail FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_contact_detail_updated_at BEFORE UPDATE ON contact_detail FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- client (rol op person)
-- ============================================================================
CREATE TABLE client (
  id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  person_id          UUID NOT NULL REFERENCES person(id) ON DELETE RESTRICT,
  client_number      BIGINT NOT NULL,
  client_since       DATE NOT NULL,
  gp_situation       TEXT NOT NULL DEFAULT 'onbekend', -- waardelijst huisarts_situatie

  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at         TIMESTAMPTZ
);

-- max één CLIENT-rol per PERSOON
CREATE UNIQUE INDEX idx_client_person ON client(person_id) WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX idx_client_number ON client(client_number) WHERE deleted_at IS NULL;

COMMENT ON TABLE client IS 'model-aanmelding.md §2.4 — instellingsgebonden rol op PERSOON. Nudge (geen constraint): cliënt zonder bsn_hash (Wabvpz).';

ALTER TABLE client ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON client FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_client_updated_at BEFORE UPDATE ON client FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- client_relation (contactpersoon / wettelijk vertegenwoordiger)
-- ============================================================================
CREATE TABLE client_relation (
  id                        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id                 UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
  person_id                 UUID NOT NULL REFERENCES person(id) ON DELETE RESTRICT,
  relation_type              TEXT,            -- waardelijst relatie (partner, ouder, kind, ...)
  relation_role              TEXT NOT NULL,   -- waardelijst relatierol
  representation_basis       TEXT,            -- waardelijst vertegenwoordigingsgrond (alleen bij rol wettelijk_vertegenwoordiger)
  valid_from                 DATE NOT NULL,
  valid_to                   DATE,
  notes                      TEXT,

  created_at                 TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                 TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at                 TIMESTAMPTZ
);

-- max één actuele rij per (client, persoon, rol)
CREATE UNIQUE INDEX idx_client_relation_current ON client_relation(client_id, person_id, relation_role)
  WHERE valid_to IS NULL AND deleted_at IS NULL;
CREATE INDEX idx_client_relation_client ON client_relation(client_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE client_relation IS 'model-aanmelding.md §2.5 — contactpersonen/vertegenwoordigers, twee assen relatie x rol (zib Contactpersoon). Leeftijdsafhankelijke rechten (12/16 WGBO) zijn autorisatie, geen schema.';

ALTER TABLE client_relation ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON client_relation FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_client_relation_updated_at BEFORE UPDATE ON client_relation FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Zelfrelatie (persoon = cliënt-persoon) niet toegestaan. CHECK-constraints
-- kunnen geen subquery bevatten in Postgres, dus dit is een trigger i.p.v.
-- een CHECK — functioneel identiek aan wat model-aanmelding.md §2.5 vraagt.
CREATE OR REPLACE FUNCTION prevent_client_relation_self_reference()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.person_id = (SELECT person_id FROM client WHERE client.id = NEW.client_id) THEN
    RAISE EXCEPTION 'client_relation: person_id mag niet gelijk zijn aan de cliënt-persoon zelf';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER client_relation_no_self_reference
  BEFORE INSERT OR UPDATE ON client_relation
  FOR EACH ROW EXECUTE FUNCTION prevent_client_relation_self_reference();

-- ============================================================================
-- practice_organization (moet vóór referrer bestaan i.v.m. FK)
-- ============================================================================
CREATE TABLE practice_organization (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          TEXT NOT NULL,
  practice_type TEXT,                        -- waardelijst praktijk_soort
  agb_code      TEXT,
  address_text  TEXT,
  phone         TEXT,

  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at    TIMESTAMPTZ
);

CREATE UNIQUE INDEX idx_practice_organization_agb ON practice_organization(agb_code) WHERE agb_code IS NOT NULL AND deleted_at IS NULL;

COMMENT ON TABLE practice_organization IS 'model-aanmelding.md §2.7 — organisatie waaraan verwijzers/huisartsen verbonden zijn.';

ALTER TABLE practice_organization ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON practice_organization FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_practice_organization_updated_at BEFORE UPDATE ON practice_organization FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- referrer
-- ============================================================================
CREATE TABLE referrer (
  id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name                    TEXT NOT NULL,
  referrer_type           TEXT NOT NULL,     -- waardelijst verwijzertype (extra.agb_verplicht bepaalt nudge)
  agb_code                TEXT,
  practice_organization_id UUID REFERENCES practice_organization(id) ON DELETE SET NULL,
  phone                   TEXT,
  email                   TEXT,

  created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at              TIMESTAMPTZ
);

CREATE UNIQUE INDEX idx_referrer_agb ON referrer(agb_code) WHERE agb_code IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX idx_referrer_practice ON referrer(practice_organization_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE referrer IS 'model-aanmelding.md §2.6 — persoon/functionaris die verwijst, los van practice_organization. Kan zonder praktijk bestaan (gemeente-ambtenaar). Ook referent voor de vaste huisarts.';

ALTER TABLE referrer ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON referrer FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_referrer_updated_at BEFORE UPDATE ON referrer FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- client_general_practitioner (vaste huisarts)
-- ============================================================================
CREATE TABLE client_general_practitioner (
  id                        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id                 UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
  referrer_id                UUID REFERENCES referrer(id) ON DELETE SET NULL,
  practice_organization_id   UUID REFERENCES practice_organization(id) ON DELETE SET NULL,
  valid_from                 DATE NOT NULL,
  valid_to                   DATE,

  created_at                 TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                 TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at                 TIMESTAMPTZ,

  CONSTRAINT client_gp_at_least_one_ref CHECK (referrer_id IS NOT NULL OR practice_organization_id IS NOT NULL)
);

-- max één actuele registratie per cliënt
CREATE UNIQUE INDEX idx_client_gp_current ON client_general_practitioner(client_id)
  WHERE valid_to IS NULL AND deleted_at IS NULL;

COMMENT ON TABLE client_general_practitioner IS 'model-aanmelding.md §2.8 — vaste huisarts, los van de incidentele verwijzer. "Geen huisarts"/"geen toestemming" staat op client.gp_situation, niet hier.';

ALTER TABLE client_general_practitioner ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON client_general_practitioner FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_client_gp_updated_at BEFORE UPDATE ON client_general_practitioner FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- insurance
-- ============================================================================
CREATE TABLE insurance (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id             UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
  uzovi_code            TEXT NOT NULL,
  insurer_name          TEXT,
  policy_number         TEXT,
  valid_from            DATE NOT NULL,
  valid_to              DATE,
  cov_checked_at        DATE,

  created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at            TIMESTAMPTZ
);

-- max één actuele verzekering per cliënt
CREATE UNIQUE INDEX idx_insurance_current ON insurance(client_id) WHERE valid_to IS NULL AND deleted_at IS NULL;

COMMENT ON TABLE insurance IS 'model-aanmelding.md §2.9 — Zvw-verzekeringsgegevens. Nudge: Zvw-aanmelding zonder actuele COV-controle. Wlz/forensisch: declaratie-ronde.';

ALTER TABLE insurance ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON insurance FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_insurance_updated_at BEFORE UPDATE ON insurance FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- consent
-- ============================================================================
-- referral_case_id/clinical_care_episode_id FKs are added by later migrations
-- (create_referral_domain, create_intake_treatment_advice_domain) once those
-- tables exist — see ALTER TABLE at the bottom of create_referral_domain.
CREATE TABLE consent (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id         UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
  consent_type      TEXT NOT NULL,           -- waardelijst toestemming_type (extra.grondslag)
  status            TEXT NOT NULL,           -- waardelijst toestemming_status
  consent_date      DATE NOT NULL,
  method            TEXT,                    -- waardelijst toestemming_wijze
  recorded_by       UUID REFERENCES practitioners(id),
  valid_to          DATE,
  revoked_at        DATE,
  scope_referral_case_id UUID,               -- optioneel; default cliëntbreed
  notes             TEXT,

  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at        TIMESTAMPTZ
);

-- max één actuele (niet-ingetrokken) rij per (client, type, scope)
CREATE UNIQUE INDEX idx_consent_current ON consent(client_id, consent_type, COALESCE(scope_referral_case_id, '00000000-0000-0000-0000-000000000000'))
  WHERE status <> 'ingetrokken' AND deleted_at IS NULL;
CREATE INDEX idx_consent_client ON consent(client_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE consent IS 'model-aanmelding.md §2.14 — generieke WGBO/AVG-toestemming. Cliëntakkoord op het behandelplan is een apart feit bij het behandelplan, geen consent-rij. scope_referral_case_id FK volgt in create_referral_domain.sql.';

ALTER TABLE consent ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON consent FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_consent_updated_at BEFORE UPDATE ON consent FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- client_portal_account
-- ============================================================================
CREATE TABLE client_portal_account (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id   UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,

  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at  TIMESTAMPTZ
);

CREATE UNIQUE INDEX idx_client_portal_account_client ON client_portal_account(client_id) WHERE deleted_at IS NULL;

COMMENT ON TABLE client_portal_account IS 'model-aanmelding.md §2.16 — relatie-placeholder (Wabvpz elektronische inzage). Authenticatiedetails: auth/ADM-ronde.';

ALTER TABLE client_portal_account ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all for authenticated users" ON client_portal_account FOR ALL USING (auth.role() = 'authenticated');
CREATE TRIGGER update_client_portal_account_updated_at BEFORE UPDATE ON client_portal_account FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();