`db/migrations/0014_biometrics.sql`

38 lines
-- 0014_biometrics.sql
-- Per-person biometric / ID profile — for kids AND adults. Stable physical & identity
-- characteristics (not time-series; that's health_reading). Doubles as an emergency /
-- child-ID record: DOB, sex, height, weight, blood type, eye/hair color, distinguishing
-- marks, allergies, conditions, emergency contact, and whether fingerprints/photo are on
-- file. Sensitive PII — same posture as the medication tables (local, audited).
-- One row per person. Idempotent. Safe to re-run.

BEGIN;

CREATE TABLE IF NOT EXISTS person_biometric (
  id                   text PRIMARY KEY,
  user_id              text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  person_id            text NOT NULL REFERENCES person(id) ON DELETE CASCADE,
  date_of_birth        date,
  sex                  text,                 -- m|f|x
  height_in            numeric(5,1),
  weight_lb            numeric(6,1),
  blood_type           text,                 -- O+, A-, etc.
  eye_color            text,
  hair_color           text,
  distinguishing_marks text,                 -- scars, birthmarks, tattoos
  allergies            text,
  conditions           text,                 -- chronic conditions relevant in an emergency
  emergency_contact    text,
  emergency_notes      text,
  photo_path           text,                 -- optional reference to an uploaded photo
  prints_on_file       boolean NOT NULL DEFAULT false,  -- child-ID: fingerprints kept somewhere
  prints_location      text,                 -- where (safe, doctor, police kit)
  metadata_jsonb       jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, person_id)
);
CREATE INDEX IF NOT EXISTS person_biometric_user_idx ON person_biometric (user_id);

COMMIT;