Data Model

Visual Entity Relationship Diagram

Color coding indicates domain grouping. All foreign keys are shown as directed arrows. Dashed lines indicate optional (nullable) relationships.

Data Store Matrix

Settle uses three data stores with distinct responsibilities. No PII ever enters Redis. R2 objects are AES-256 encrypted at rest by Cloudflare.

Row Count Projections

Based on 500 Year 1 estates, 5,000 Year 2, and 50,000 Year 3. Each estate generates approximately 38 tasks, 15 notification records, 12 documents, and 3 benefits.

CREATE TABLE Statements

Full PostgreSQL DDL. Run these in order. Extensions must be enabled first. All tables use uuid primary keys via gen_random_uuid().

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";    -- column-level encryption, gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";   -- uuid_generate_v4() compat
CREATE EXTENSION IF NOT EXISTS "pg_trgm";     -- trigram indexes for name search

-- User roles (global, on the users table)
CREATE TYPE user_role AS ENUM (
  'executor', 'co_executor', 'family_viewer', 'attorney'
);

-- Estate lifecycle
CREATE TYPE estate_status AS ENUM (
  'intake', 'active', 'monitoring', 'closed'
);

-- Member role scoped to an estate (may differ from user.role)
CREATE TYPE member_role AS ENUM (
  'executor', 'co_executor', 'family_viewer', 'attorney'
);

-- Task phases map to UI sections
CREATE TYPE task_phase AS ENUM (
  'immediate', 'first_two_weeks', 'financial',
  'property_legal', 'benefits', 'final'
);

-- Task lifecycle
CREATE TYPE task_status AS ENUM (
  'upcoming', 'available', 'in_progress',
  'waiting', 'completed', 'skipped'
);

-- Institution classification
CREATE TYPE institution_type AS ENUM (
  'government', 'financial', 'insurance',
  'utility', 'subscription', 'legal'
);

-- Notification delivery method
CREATE TYPE notification_method AS ENUM (
  'api_call', 'physical_mail', 'email', 'guided'
);

-- Notification delivery status
CREATE TYPE notification_status AS ENUM (
  'pending', 'sent', 'delivered',
  'confirmed', 'failed', 'needs_followup'
);

-- Document categories
CREATE TYPE document_category AS ENUM (
  'death_certificate', 'will', 'insurance_policy',
  'bank_statement', 'tax_document', 'correspondence',
  'legal', 'military', 'other'
);

-- Benefit types
CREATE TYPE benefit_type AS ENUM (
  'social_security_survivor', 'unclaimed_property', 'va_burial',
  'pension', 'life_insurance', 'other'
);

-- Benefit confidence level
CREATE TYPE benefit_confidence AS ENUM (
  'confirmed', 'probable', 'possible'
);

-- Benefit claim status
CREATE TYPE benefit_status AS ENUM (
  'discovered', 'investigating', 'claim_filed',
  'received', 'denied'
);

-- State rule categories
CREATE TYPE state_rule_type AS ENUM (
  'probate_threshold', 'small_estate_affidavit',
  'filing_deadline', 'required_forms'
);

users

CREATE TABLE users (
  id              uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  email           text        NOT NULL UNIQUE,
  name            text        NOT NULL,
  phone           text,
  role            user_role   NOT NULL DEFAULT 'family_viewer',
  password_hash   text        NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now()
);

-- Auto-update updated_at on any row change
CREATE TRIGGER users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION moddatetime(updated_at);

estates

CREATE TABLE estates (
  id                   uuid          PRIMARY KEY DEFAULT gen_random_uuid(),
  created_by           uuid          NOT NULL REFERENCES users(id),
  status               estate_status NOT NULL DEFAULT 'intake',
  state_code           char(2)       NOT NULL,
  intake_completed_at  timestamptz,
  created_at           timestamptz   NOT NULL DEFAULT now(),
  updated_at           timestamptz   NOT NULL DEFAULT now(),

  CONSTRAINT estates_state_code_check
    CHECK (state_code ~ '^[A-Z]{2}$')
);

CREATE TRIGGER estates_updated_at
  BEFORE UPDATE ON estates
  FOR EACH ROW EXECUTE FUNCTION moddatetime(updated_at);

deceased_persons

CREATE TABLE deceased_persons (
  id                    uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  estate_id             uuid        NOT NULL UNIQUE REFERENCES estates(id) ON DELETE CASCADE,

  -- Identity
  full_legal_name       text        NOT NULL,
  date_of_birth         date        NOT NULL,
  date_of_death         date        NOT NULL,

  -- SSN stored as pgcrypto symmetric ciphertext
  -- Encrypt:   pgp_sym_encrypt('123-45-6789', current_setting('app.encryption_key'))
  -- Decrypt:   pgp_sym_decrypt(ssn_encrypted, current_setting('app.encryption_key'))
  ssn_encrypted         bytea,

  state_of_residence    char(2),
  military_service      boolean     NOT NULL DEFAULT false,
  military_branch       text,       -- Army, Navy, Air Force, Marines, Coast Guard, Space Force
  military_service_start date,
  military_service_end   date,

  -- JSONB array: [{company: "Acme Corp", start_year: 1985, end_year: 2010}, ...]
  employer_history      jsonb       NOT NULL DEFAULT '[]',

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

  CONSTRAINT death_after_birth
    CHECK (date_of_death > date_of_birth),
  CONSTRAINT military_dates_require_service
    CHECK (military_service = true OR (military_branch IS NULL AND military_service_start IS NULL)),
  CONSTRAINT state_code_format
    CHECK (state_of_residence IS NULL OR state_of_residence ~ '^[A-Z]{2}$')
);

CREATE TRIGGER deceased_persons_updated_at
  BEFORE UPDATE ON deceased_persons
  FOR EACH ROW EXECUTE FUNCTION moddatetime(updated_at);

estate_members

CREATE TABLE estate_members (
  id          uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  estate_id   uuid        NOT NULL REFERENCES estates(id) ON DELETE CASCADE,
  user_id     uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role        member_role NOT NULL,
  invited_at  timestamptz NOT NULL DEFAULT now(),
  accepted_at timestamptz,
  created_at  timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT estate_members_unique
    UNIQUE (estate_id, user_id)
);

tasks

CREATE TABLE tasks (
  id                   uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  estate_id            uuid        NOT NULL REFERENCES estates(id) ON DELETE CASCADE,
  phase                task_phase  NOT NULL,
  sequence_order       integer     NOT NULL DEFAULT 0,
  title                text        NOT NULL,
  description          text        NOT NULL DEFAULT '',
  why_this_matters     text        NOT NULL DEFAULT '',
  time_estimate_minutes integer,
  status               task_status NOT NULL DEFAULT 'upcoming',

  -- Self-referential FK for dependency DAG
  depends_on_task_id   uuid        REFERENCES tasks(id) ON DELETE SET NULL,

  -- Optional link to an institution (populated for notification tasks)
  institution_id       uuid        REFERENCES institutions(id) ON DELETE SET NULL,

  completed_at         timestamptz,
  completed_by         uuid        REFERENCES users(id) ON DELETE SET NULL,

  -- Which state_rule generated this task (nullable — not all tasks are rule-generated)
  state_rule_id        uuid        REFERENCES state_rules(id) ON DELETE SET NULL,

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

  CONSTRAINT tasks_no_self_dependency
    CHECK (depends_on_task_id <> id),
  CONSTRAINT tasks_completed_requires_timestamp
    CHECK (status <> 'completed' OR completed_at IS NOT NULL)
);

CREATE TRIGGER tasks_updated_at
  BEFORE UPDATE ON tasks
  FOR EACH ROW EXECUTE FUNCTION moddatetime(updated_at);

task_scripts

CREATE TABLE task_scripts (
  id             uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id        uuid        NOT NULL UNIQUE REFERENCES tasks(id) ON DELETE CASCADE,
  script_text    text        NOT NULL,

  -- Ordered list of items to have ready before calling
  what_youll_need text[]     NOT NULL DEFAULT '{}',

  -- [{question: "...", answer: "..."}, ...]
  faq            jsonb       NOT NULL DEFAULT '[]',

  created_at     timestamptz NOT NULL DEFAULT now()
);

institutions

CREATE TABLE institutions (
  id                   uuid             PRIMARY KEY DEFAULT gen_random_uuid(),
  name                 text             NOT NULL,
  type                 institution_type NOT NULL,
  phone                text,
  website              text,
  hours                text,            -- Human-readable: "Mon–Fri 8am–7pm ET"
  notification_tier   integer          NOT NULL
                         CHECK (notification_tier BETWEEN 1 AND 3),
  avg_hold_time_minutes integer,
  requires_in_person   boolean         NOT NULL DEFAULT false,
  accepts_written_notice boolean       NOT NULL DEFAULT false,
  created_at           timestamptz     NOT NULL DEFAULT now()
);

notification_records

CREATE TABLE notification_records (
  id               uuid                  PRIMARY KEY DEFAULT gen_random_uuid(),
  estate_id        uuid                  NOT NULL REFERENCES estates(id) ON DELETE CASCADE,
  institution_id   uuid                  NOT NULL REFERENCES institutions(id),
  tier             integer               NOT NULL CHECK (tier BETWEEN 1 AND 3),
  method           notification_method   NOT NULL,
  status           notification_status   NOT NULL DEFAULT 'pending',
  sent_at          timestamptz,
  confirmed_at     timestamptz,
  lob_tracking_id  text,                  -- Lob API tracking ID for physical mail
  response_notes   text,
  created_at       timestamptz           NOT NULL DEFAULT now(),
  updated_at       timestamptz           NOT NULL DEFAULT now()
);

CREATE TRIGGER notification_records_updated_at
  BEFORE UPDATE ON notification_records
  FOR EACH ROW EXECUTE FUNCTION moddatetime(updated_at);

documents

CREATE TABLE documents (
  id               uuid              PRIMARY KEY DEFAULT gen_random_uuid(),
  estate_id        uuid              NOT NULL REFERENCES estates(id) ON DELETE RESTRICT,
  uploaded_by      uuid              REFERENCES users(id) ON DELETE SET NULL,
  name             text              NOT NULL,
  -- R2 object key: estates/{estate_id}/docs/{document_id}/{filename}
  file_key         text              NOT NULL UNIQUE,
  file_type        text              NOT NULL,     -- MIME type
  file_size_bytes  bigint            NOT NULL,
  category         document_category NOT NULL DEFAULT 'other',
  copies_remaining integer,          -- For death certificates: track how many physical copies left
  expires_at       timestamptz,      -- For time-limited documents
  linked_task_id   uuid              REFERENCES tasks(id) ON DELETE SET NULL,
  auto_named       boolean           NOT NULL DEFAULT false,
  created_at       timestamptz       NOT NULL DEFAULT now()
);

benefits

CREATE TABLE benefits (
  id                  uuid              PRIMARY KEY DEFAULT gen_random_uuid(),
  estate_id           uuid              NOT NULL REFERENCES estates(id) ON DELETE CASCADE,
  type                benefit_type      NOT NULL,
  source_name         text              NOT NULL,
  estimated_amount    numeric(12,2),    -- Total lump sum estimate
  estimated_monthly   numeric(10,2),    -- For ongoing survivor benefits
  confidence          benefit_confidence NOT NULL DEFAULT 'possible',
  status              benefit_status    NOT NULL DEFAULT 'discovered',
  claim_filed_at      timestamptz,
  received_at         timestamptz,
  amount_received     numeric(12,2),
  created_at          timestamptz       NOT NULL DEFAULT now(),
  updated_at          timestamptz       NOT NULL DEFAULT now()
);

CREATE TRIGGER benefits_updated_at
  BEFORE UPDATE ON benefits
  FOR EACH ROW EXECUTE FUNCTION moddatetime(updated_at);

state_rules

CREATE TABLE state_rules (
  id              uuid            PRIMARY KEY DEFAULT gen_random_uuid(),
  state_code      char(2)        NOT NULL,
  rule_type       state_rule_type NOT NULL,

  -- Flexible schema for rule payload. Example:
  -- {"threshold": 100000, "form": "Small Estate Affidavit", "filing_required": false}
  rule_value      jsonb           NOT NULL,

  effective_date  date            NOT NULL,
  version         integer         NOT NULL DEFAULT 1,
  source_url      text,
  last_verified_at timestamptz,
  created_at      timestamptz     NOT NULL DEFAULT now(),

  -- Unique per state + rule type + version ensures immutable history
  CONSTRAINT state_rules_unique_version
    UNIQUE (state_code, rule_type, version),

  CONSTRAINT state_rules_state_code_format
    CHECK (state_code ~ '^[A-Z]{2}$')
);

audit_log

CREATE TABLE audit_log (
  id           uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id      uuid        REFERENCES users(id) ON DELETE SET NULL,
  estate_id    uuid        REFERENCES estates(id) ON DELETE SET NULL,
  action       text        NOT NULL,    -- e.g. 'task.completed', 'document.uploaded'
  entity_type  text        NOT NULL,    -- table name: 'tasks', 'documents', etc.
  entity_id    uuid,                    -- FK to the affected row
  old_value    jsonb,                   -- Row state before mutation (null for inserts)
  new_value    jsonb,                   -- Row state after mutation (null for deletes)
  ip_address   inet,
  created_at   timestamptz NOT NULL DEFAULT now()
);

-- Prevent mutations. Application role must only have INSERT + SELECT.
REVOKE UPDATE, DELETE ON audit_log FROM settle_app;

-- Partition by month starting at scale (Year 2+)
-- ALTER TABLE audit_log PARTITION BY RANGE (created_at);
-- CREATE TABLE audit_log_2026_04 PARTITION OF audit_log
--   FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

Index Strategy

All primary keys are indexed automatically. These additional indexes address the most critical query patterns. Use EXPLAIN (ANALYZE, BUFFERS) on production traffic before adding further indexes — each index has a write cost.

-- ─── tasks ───────────────────────────────────────────────────────────────────
-- Primary lookup: all tasks for an estate, by status
CREATE INDEX idx_tasks_estate_status
  ON tasks (estate_id, status);

-- DAG availability query: find tasks whose dependency is complete
CREATE INDEX idx_tasks_depends_on
  ON tasks (depends_on_task_id)
  WHERE depends_on_task_id IS NOT NULL;

-- Phase + order for rendering the task list UI in sequence
CREATE INDEX idx_tasks_estate_phase_order
  ON tasks (estate_id, phase, sequence_order);

-- Institution link for notification tasks
CREATE INDEX idx_tasks_institution
  ON tasks (institution_id)
  WHERE institution_id IS NOT NULL;

-- ─── notification_records ────────────────────────────────────────────────────
-- Dashboard: all notifications for an estate grouped by status
CREATE INDEX idx_notif_estate_status
  ON notification_records (estate_id, status);

-- Follow-up queue: pending/failed notifications needing attention
CREATE INDEX idx_notif_needs_followup
  ON notification_records (status, created_at)
  WHERE status IN ('failed', 'needs_followup');

-- Lob webhook lookup by tracking ID
CREATE INDEX idx_notif_lob_tracking
  ON notification_records (lob_tracking_id)
  WHERE lob_tracking_id IS NOT NULL;

-- ─── state_rules ─────────────────────────────────────────────────────────────
-- Primary lookup pattern: find current rules for a state
CREATE INDEX idx_state_rules_state_type
  ON state_rules (state_code, rule_type);

-- Version history: latest version per state+type
CREATE INDEX idx_state_rules_version
  ON state_rules (state_code, rule_type, version DESC);

-- ─── estate_members ──────────────────────────────────────────────────────────
-- "Which estates can this user see?" — used on every authenticated request
CREATE INDEX idx_estate_members_user
  ON estate_members (user_id);

-- ─── documents ───────────────────────────────────────────────────────────────
-- Document gallery per estate filtered by category
CREATE INDEX idx_documents_estate_category
  ON documents (estate_id, category);

-- Death certificate tracking: find certs with remaining copies
CREATE INDEX idx_documents_death_certs
  ON documents (estate_id, copies_remaining)
  WHERE category = 'death_certificate';

-- ─── audit_log ───────────────────────────────────────────────────────────────
-- Compliance review: all audit events for an estate
CREATE INDEX idx_audit_estate_created
  ON audit_log (estate_id, created_at DESC);

-- User activity log
CREATE INDEX idx_audit_user_created
  ON audit_log (user_id, created_at DESC);

-- ─── benefits ────────────────────────────────────────────────────────────────
CREATE INDEX idx_benefits_estate_status
  ON benefits (estate_id, status);

-- ─── users ───────────────────────────────────────────────────────────────────
-- Trigram index for name search (requires pg_trgm)
CREATE INDEX idx_users_name_trgm
  ON users USING gin (name gin_trgm_ops);

-- deceased_persons name search
CREATE INDEX idx_deceased_name_trgm
  ON deceased_persons USING gin (full_legal_name gin_trgm_ops);

-- JSONB index for state_rules rule_value lookups
CREATE INDEX idx_state_rules_value
  ON state_rules USING gin (rule_value);

Row-Level Security Policies

RLS restricts data visibility at the database layer — a defense-in-depth measure that complements application authorization. All tables with estate-scoped data have RLS enabled.

-- Application sets these on each transaction:
-- SET LOCAL app.current_user_id = '<uuid>';
-- SET LOCAL app.current_user_role = 'executor';

-- Helper function: returns true if the current user is a member of the estate
CREATE OR REPLACE FUNCTION is_estate_member(p_estate_id uuid)
RETURNS boolean LANGUAGE sql SECURITY DEFINER STABLE AS $$
  SELECT EXISTS (
    SELECT 1 FROM estate_members
    WHERE estate_id = p_estate_id
      AND user_id = current_setting('app.current_user_id')::uuid
      AND accepted_at IS NOT NULL
  );
$$;

CREATE OR REPLACE FUNCTION estate_member_role(p_estate_id uuid)
RETURNS text LANGUAGE sql SECURITY DEFINER STABLE AS $$
  SELECT role::text FROM estate_members
  WHERE estate_id = p_estate_id
    AND user_id = current_setting('app.current_user_id')::uuid
  LIMIT 1;
$$;

ALTER TABLE estates ENABLE ROW LEVEL SECURITY;

CREATE POLICY estates_member_select ON estates
  FOR SELECT TO settle_app
  USING (is_estate_member(id));

CREATE POLICY estates_executor_modify ON estates
  FOR ALL TO settle_app
  USING (
    is_estate_member(id) AND
    estate_member_role(id) IN ('executor', 'co_executor', 'attorney')
  );

ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

CREATE POLICY tasks_member_select ON tasks
  FOR SELECT TO settle_app
  USING (is_estate_member(estate_id));

CREATE POLICY tasks_executor_modify ON tasks
  FOR ALL TO settle_app
  USING (
    is_estate_member(estate_id) AND
    estate_member_role(estate_id) IN ('executor', 'co_executor', 'attorney')
  );

ALTER TABLE deceased_persons ENABLE ROW LEVEL SECURITY;

CREATE POLICY deceased_member_select ON deceased_persons
  FOR SELECT TO settle_app
  USING (is_estate_member(estate_id));

CREATE POLICY deceased_executor_modify ON deceased_persons
  FOR ALL TO settle_app
  USING (
    is_estate_member(estate_id) AND
    estate_member_role(estate_id) IN ('executor', 'co_executor', 'attorney')
  );

-- Column-level: revoke SSN access from the family viewer role
-- The app role handles this by never SELECTing ssn_encrypted for family_viewers.
-- For defense in depth, create a view that excludes the column:
CREATE VIEW deceased_persons_redacted AS
  SELECT id, estate_id, full_legal_name, date_of_birth, date_of_death,
         state_of_residence, military_service, military_branch,
         military_service_start, military_service_end,
         employer_history, created_at, updated_at
  FROM   deceased_persons;
-- Grant family_viewer role access only to this view, not the base table.

ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;

CREATE POLICY audit_executor_select ON audit_log
  FOR SELECT TO settle_app
  USING (
    is_estate_member(estate_id) AND
    estate_member_role(estate_id) IN ('executor', 'co_executor', 'attorney')
  );

CREATE POLICY audit_app_insert ON audit_log
  FOR INSERT TO settle_app
  WITH CHECK (true); -- Application may always insert

Top 10 Query Patterns

-- "Available" = upcoming tasks whose dependency (if any) is completed
SELECT
  t.id, t.title, t.phase, t.sequence_order,
  t.time_estimate_minutes, t.status,
  dep.title AS depends_on_title
FROM tasks t
LEFT JOIN tasks dep ON dep.id = t.depends_on_task_id
WHERE
  t.estate_id = $1
  AND t.status = 'upcoming'
  AND (
    t.depends_on_task_id IS NULL
    OR dep.status = 'completed'
  )
ORDER BY t.phase, t.sequence_order;
-- Index used: idx_tasks_estate_status, idx_tasks_depends_on

SELECT
  phase,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed,
  COUNT(*) FILTER (WHERE status = 'in_progress') AS in_progress,
  COUNT(*) FILTER (WHERE status = 'upcoming') AS upcoming,
  COUNT(*) FILTER (WHERE status = 'skipped') AS skipped,
  COUNT(*) AS total
FROM   tasks
WHERE  estate_id = $1
GROUP  BY phase
ORDER  BY array_position(
  ARRAY['immediate','first_two_weeks','financial',
        'property_legal','benefits','final']::task_phase[],
  phase
);
-- Index used: idx_tasks_estate_status (estate_id leading column)

-- Get the active (highest version) rule for each type for a given state
SELECT DISTINCT ON (rule_type)
  id, state_code, rule_type, rule_value,
  effective_date, version
FROM   state_rules
WHERE  state_code = $1           -- e.g. 'IL'
  AND  effective_date <= CURRENT_DATE
ORDER BY rule_type, version DESC;
-- Index used: idx_state_rules_version

SELECT
  nr.id,
  i.name AS institution_name,
  i.type AS institution_type,
  nr.tier, nr.method, nr.status,
  nr.sent_at, nr.confirmed_at,
  nr.lob_tracking_id
FROM   notification_records nr
JOIN   institutions i ON i.id = nr.institution_id
WHERE  nr.estate_id = $1
ORDER BY nr.tier, i.name;
-- Index used: idx_notif_estate_status

SELECT
  type,
  source_name,
  confidence,
  status,
  estimated_amount,
  estimated_monthly,
  -- Annualized value for ongoing benefits
  COALESCE(estimated_amount, estimated_monthly * 12) AS annual_value
FROM   benefits
WHERE  estate_id = $1
ORDER BY COALESCE(estimated_amount, estimated_monthly * 12) DESC NULLS LAST;

SELECT
  t.*,
  ts.script_text,
  ts.what_youll_need,
  ts.faq,
  i.name          AS institution_name,
  i.phone         AS institution_phone,
  i.hours         AS institution_hours,
  i.avg_hold_time_minutes,
  u.name          AS completed_by_name
FROM        tasks t
LEFT JOIN   task_scripts ts ON ts.task_id = t.id
LEFT JOIN   institutions i  ON i.id      = t.institution_id
LEFT JOIN   users u          ON u.id      = t.completed_by
WHERE t.id = $1;

SELECT
  e.id,
  e.status,
  e.state_code,
  e.created_at,
  dp.full_legal_name,
  dp.date_of_death,
  em.role AS my_role,
  ROUND(
    COUNT(t.id) FILTER (WHERE t.status = 'completed') * 100.0 /
    NULLIF(COUNT(t.id) FILTER (WHERE t.status <> 'skipped'), 0)
  , 1) AS completion_pct
FROM       estates e
JOIN       estate_members em  ON em.estate_id = e.id
                               AND em.user_id = $1
                               AND em.accepted_at IS NOT NULL
JOIN       deceased_persons dp ON dp.estate_id = e.id
LEFT JOIN  tasks t             ON t.estate_id  = e.id
GROUP BY   e.id, dp.full_legal_name, dp.date_of_death, em.role
ORDER BY   e.created_at DESC;

SELECT
  d.id,
  d.estate_id,
  d.name,
  d.copies_remaining,
  dp.full_legal_name,
  u.email AS executor_email
FROM   documents d
JOIN   deceased_persons dp  ON dp.estate_id = d.estate_id
JOIN   estate_members em   ON em.estate_id = d.estate_id
                             AND em.role = 'executor'
JOIN   users u              ON u.id = em.user_id
WHERE  d.category = 'death_certificate'
  AND  d.copies_remaining <= 2
ORDER BY d.copies_remaining;
-- Index used: idx_documents_death_certs

SELECT
  al.created_at,
  al.action,
  al.entity_type,
  al.entity_id,
  u.name  AS actor_name,
  u.email AS actor_email,
  al.old_value,
  al.new_value
FROM   audit_log al
LEFT JOIN users u ON u.id = al.user_id
WHERE  al.estate_id = $1
ORDER BY al.created_at DESC
LIMIT $2 OFFSET $3;  -- Paginate: $2=50, $3=page*50
-- Index used: idx_audit_estate_created

-- Called during estate onboarding. Inserts tasks from a template table
-- and links state-specific tasks to the rule that generated them.
WITH applicable_rules AS (
  -- Get latest rule versions for the estate's state
  SELECT DISTINCT ON (rule_type)
    id AS rule_id, rule_type, rule_value
  FROM  state_rules
  WHERE state_code = (SELECT state_code FROM estates WHERE id = $1)
    AND  effective_date <= CURRENT_DATE
  ORDER BY rule_type, version DESC
)
INSERT INTO tasks (
  estate_id, phase, sequence_order, title,
  description, why_this_matters, time_estimate_minutes,
  status, institution_id, state_rule_id
)
SELECT
  $1,                        -- estate_id
  tt.phase,
  tt.sequence_order,
  tt.title,
  tt.description,
  tt.why_this_matters,
  tt.time_estimate_minutes,
  'upcoming',               -- all tasks start as upcoming
  tt.institution_id,
  ar.rule_id                 -- null for universal tasks
FROM      task_templates tt
LEFT JOIN applicable_rules ar ON ar.rule_type = tt.required_rule_type
WHERE
  tt.is_universal = true
  OR ar.rule_id IS NOT NULL   -- include state-specific tasks only if rule applies
ORDER BY tt.phase, tt.sequence_order
RETURNING id;

PII Inventory

All PII must be classified, handled according to its tier, and logged when accessed. This inventory is the source of truth for CCPA/GDPR data mapping.

Encryption Implementation Details

-- ─── SSN Encryption / Decryption Pattern ─────────────────────────────────────
--
-- The encryption key is NOT stored in the database.
-- It is set per-transaction by the application server:
--   SET LOCAL app.encryption_key = 'your-256-bit-key-from-env';
--
-- WRITE (application encrypts before storing):
UPDATE deceased_persons
SET    ssn_encrypted = pgp_sym_encrypt(
  $1,  -- plaintext SSN, e.g. '123-45-6789'
  current_setting('app.encryption_key')
)
WHERE  estate_id = $2;

-- READ (application decrypts only when needed for form submission):
SELECT pgp_sym_decrypt(
  ssn_encrypted,
  current_setting('app.encryption_key')
) AS ssn_plaintext
FROM   deceased_persons
WHERE  estate_id = $1;
-- This SELECT must also be logged to audit_log with action = 'ssn.accessed'

-- KEY ROTATION: re-encrypt with new key without downtime
UPDATE deceased_persons
SET    ssn_encrypted = pgp_sym_encrypt(
  pgp_sym_decrypt(ssn_encrypted, $old_key),
  $new_key
)
WHERE  ssn_encrypted IS NOT NULL;
-- Run in batches of 1000 rows. Execute during low-traffic window.

Migration Strategy

Migrations are numbered, sequential, and forward-only. Each migration file is idempotent where possible. Never modify an applied migration — create a new one. Rollbacks are handled via compensating migrations, not DOWN scripts.

• V001
  Enable extensions

  pgcrypto, uuid-ossp, pg_trgm. Must run as superuser.
• V002
  Create all enum types

  All 12 enum types defined before any table references them.
• V003
  Create moddatetime function

  CREATE FUNCTION moddatetime() for updated_at triggers. Part of pg contrib.
• V004
  Create users table + trigger

  No foreign keys yet. Foundation of the user system.
• V005
  Create estates table + trigger

  References users(created_by). State code check constraint.
• V006
  Create deceased_persons table

  UNIQUE on estate_id (1:1). ssn_encrypted bytea column.
• V007
  Create estate_members table

  UNIQUE (estate_id, user_id). Invite/accept timestamps.
• V008
  Create institutions table

  Reference data. Must exist before tasks (FK) and notification_records.
• V009
  Create state_rules table

  Must exist before tasks (FK to state_rule_id).
• V010
  Create tasks table + trigger

  Self-referential FK. References institutions, state_rules, users.
• V011
  Create task_scripts table

  UNIQUE on task_id. what_youll_need text[] column.
• V012
  Create notification_records table + trigger

  lob_tracking_id for Lob webhook reconciliation.
• V013
  Create documents table

  ON DELETE RESTRICT on estate_id — prevents orphaned R2 objects.
• V014
  Create benefits table + trigger

  estimated_monthly for ongoing survivor benefits.
• V015
  Create audit_log table + REVOKE

  Revoke UPDATE/DELETE from settle_app role immediately after creation.
• V016
  Create all indexes

  All indexes from the index strategy section. Use CONCURRENTLY on production.
• V017
  Enable RLS + create policies

  Enable RLS on all estate-scoped tables. Create helper functions first.
• V018
  Create deceased_persons_redacted view

  View excluding ssn_encrypted for family_viewer role.
• V019
  Seed state_rules (50 states)

  Probate thresholds, small estate affidavit rules, filing deadlines for all 50 states. version=1.
• V020
  Seed institutions (SSA, IRS, major banks, utilities)

  ~200 institution records with tier, hold time, and contact details.
• V021
  Create task_templates table + seed

  Universal and state-conditional task templates per phase. Used by Query Pattern #10.

Seed Data

Representative examples. Full seed data lives in db/migrations/V019__seed_state_rules.sql, V020__seed_institutions.sql, and V021__seed_task_templates.sql.

State Rules — Selected Examples

-- db/migrations/V019__seed_state_rules.sql (excerpt)
INSERT INTO state_rules
  (state_code, rule_type, rule_value, effective_date, version, source_url)
VALUES
  ('IL', 'small_estate_affidavit',
   '{"threshold": 100000, "form": "Small Estate Affidavit", "filing_required": false, "waiting_days": 30}',
   '2024-01-01', 1, 'https://www.ilga.gov/legislation/ilcs/ilcs3.asp?ActID=2270'),

  ('CA', 'small_estate_affidavit',
   '{"threshold": 184500, "form": "Affidavit Procedure", "filing_required": false, "waiting_days": 40}',
   '2024-01-01', 1, 'https://leginfo.legislature.ca.gov/faces/codes_displaySection.xhtml?sectionNum=13100.'),

  ('TX', 'small_estate_affidavit',
   '{"threshold": 75000, "form": "Small Estate Affidavit", "filing_required": true, "filing_court": "probate_court"}',
   '2024-01-01', 1, 'https://statutes.capitol.texas.gov/Docs/ES/htm/ES.137.htm'),

  ('NY', 'probate_threshold',
   '{"threshold": 50000, "simplified_procedure": "Voluntary Administration", "court": "Surrogate Court"}',
   '2024-01-01', 1, 'https://www.nycourts.gov/courts/surrogates/'),

  ('FL', 'filing_deadline',
   '{"creditor_notice_days": 90, "estate_tax_months": 9, "homestead_declaration": true}',
   '2024-01-01', 1, 'https://www.leg.state.fl.us/statutes/index.cfm?App_mode=Display_Statute&URL=0700-0799/0733/'),

  ('IL', 'filing_deadline',
   '{"creditor_notice_days": 60, "will_filing_days": 30, "estate_tax_months": 9}',
   '2024-01-01', 1, 'https://www.ilga.gov/legislation/ilcs/ilcs5.asp?ActID=2270&ChapterID=60');
-- ... 50 states × 4 rule_types = ~200 rows total in full seed

Institution Seeds — Government + Financial

-- db/migrations/V020__seed_institutions.sql (excerpt)
INSERT INTO institutions
  (name, type, phone, website, hours, notification_tier,
   avg_hold_time_minutes, requires_in_person, accepts_written_notice)
VALUES
  ('Social Security Administration', 'government', '1-800-772-1213',
   'https://ssa.gov', 'Mon–Fri 8am–7pm local', 1, 45, false, false),

  ('Internal Revenue Service', 'government', '1-800-829-1040',
   'https://irs.gov', 'Mon–Fri 7am–7pm local', 1, 60, false, true),

  ('Department of Veterans Affairs', 'government', '1-800-827-1000',
   'https://va.gov', 'Mon–Fri 8am–9pm ET', 1, 30, false, true),

  ('Medicare', 'government', '1-800-633-4227',
   'https://medicare.gov', '24/7', 1, 20, false, false),

  ('Chase Bank', 'financial', '1-800-432-3117',
   'https://chase.com', 'Mon–Fri 8am–8pm, Sat 9am–6pm ET', 2, 25, true, true),

  ('Bank of America', 'financial', '1-800-432-1000',
   'https://bankofamerica.com', 'Mon–Fri 8am–11pm, Sat–Sun 8am–8pm ET', 2, 20, true, true),

  ('Wells Fargo', 'financial', '1-800-869-3557',
   'https://wellsfargo.com', 'Mon–Fri 7am–11pm, Sat 8am–8pm CT', 2, 22, true, true),

  ('Fidelity Investments', 'financial', '1-800-343-3548',
   'https://fidelity.com', 'Mon–Fri 8am–10pm ET', 2, 15, false, true),

  ('Vanguard', 'financial', '1-800-662-7447',
   'https://vanguard.com', 'Mon–Fri 8am–8pm ET', 2, 18, false, true),

  ('USAA', 'financial', '1-800-531-8722',
   'https://usaa.com', 'Mon–Fri 7:30am–5pm CT', 2, 12, false, true);
-- Full seed includes ~200 institutions across all types

Task Templates — Phase Examples

-- This requires a task_templates table (created in V021)
-- task_templates mirrors tasks schema but without estate_id
CREATE TABLE task_templates (
  id                  uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  phase               task_phase  NOT NULL,
  sequence_order      integer     NOT NULL,
  title               text        NOT NULL,
  description         text        NOT NULL DEFAULT '',
  why_this_matters    text        NOT NULL DEFAULT '',
  time_estimate_minutes integer,
  institution_id      uuid        REFERENCES institutions(id),
  is_universal        boolean     NOT NULL DEFAULT true,
  required_rule_type  state_rule_type  -- null for universal tasks
);

INSERT INTO task_templates
  (phase, sequence_order, title, description, why_this_matters, time_estimate_minutes, is_universal)
VALUES
  -- IMMEDIATE phase
  ('immediate', 10, 'Obtain death certificates',
   'Order certified copies from the funeral home or vital records office. You will need one copy per financial institution, insurance policy, and government agency.',
   'Every institution will require an original certified copy. Running out causes major delays. Order 10–15 to start.',
   60, true),

  ('immediate', 20, 'Notify Social Security Administration',
   'Call 1-800-772-1213 to report the death. If the deceased received benefits, those payments must stop immediately to avoid overpayments you will be required to return.',
   'SSA must be notified within 30 days. Unreturned overpayments accrue interest.',
   90, true),

  ('immediate', 30, 'Secure the home and personal property',
   'Change locks, forward mail, and make a preliminary inventory of valuable items. Take photographs of all rooms.',
   'As executor you are personally responsible for estate assets. Document everything before others have access.',
   120, true),

  -- FIRST_TWO_WEEKS phase
  ('first_two_weeks', 10, 'Locate and review the will',
   'Find the original will. Check safe deposit boxes, home files, and contact the deceased''s attorney. File the will with the probate court within 30 days in most states.',
   'The original will (not a copy) is required to open probate. Lost wills cause months of delay and legal expense.',
   120, true),

  ('first_two_weeks', 20, 'Open an estate bank account',
   'Open a dedicated checking account in the estate''s name (e.g., "Estate of Jane Smith"). All estate income and expenses flow through this account.',
   'Mixing estate funds with personal funds creates personal liability for the executor and complicates tax filings.',
   45, true),

  -- FINANCIAL phase
  ('financial', 10, 'Notify banks and close accounts',
   'Contact each financial institution where the deceased held accounts. Bring: certified death certificate, letters testamentary, your ID, and estate account information for fund transfer.',
   'Accounts must be retitled or closed to transfer funds to the estate. Joint accounts transfer automatically — single-owner accounts require probate authority.',
   180, true),

  ('financial', 20, 'File the final income tax return (Form 1040)',
   'File a federal income tax return for the year of death, covering January 1 through the date of death. Due April 15 of the following year.',
   'Failure to file results in penalties and interest that reduce estate value.',
   240, true),

  -- BENEFITS phase
  ('benefits', 10, 'Apply for VA burial benefits',
   'Veterans may be eligible for burial allowance ($948), plot allowance, and/or a burial flag. Apply via VA Form 21P-530EZ.',
   'VA burial benefits are often unclaimed. The application deadline is 2 years from burial date.',
   60, true),

  -- State-conditional task (requires small_estate_affidavit rule)
  ('property_legal', 10, 'File small estate affidavit',
   'Your state allows transferring assets without full probate if the estate value is below the threshold. Prepare and file the small estate affidavit with the appropriate court or institution.',
   'Avoids months of probate court proceedings and significant legal fees.',
   90, false)  -- required_rule_type = 'small_estate_affidavit'
;