OneSummer
Data Model

-- Enable pgcrypto for gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TYPE user_role AS ENUM ('parent', 'director', 'admin');

CREATE TABLE users (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  email           TEXT        NOT NULL UNIQUE,
  name            TEXT        NOT NULL,
  role            user_role   NOT NULL DEFAULT 'parent',
  password_hash   TEXT        NOT NULL,
  email_verified  BOOLEAN     NOT NULL DEFAULT false,
  last_login_at   TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Partial index: fast lookup of verified parents only
CREATE INDEX idx_users_email     ON users (email);
CREATE INDEX idx_users_role      ON users (role);
CREATE INDEX idx_users_verified  ON users (email_verified) WHERE email_verified = true;

-- Auto-update updated_at on any row change
CREATE OR REPLACE FUNCTION set_updated_at()
  RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END; $$;

CREATE TRIGGER trg_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TYPE gender_type   AS ENUM ('male', 'female', 'nonbinary', 'prefer_not_to_say');
CREATE TYPE tshirt_size   AS ENUM ('YS', 'YM', 'YL', 'AS', 'AM', 'AL', 'AXL');

CREATE TABLE children (
  id                    UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  parent_id             UUID           NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  full_name             TEXT           NOT NULL,
  date_of_birth         DATE           NOT NULL,
  gender                gender_type,
  school                TEXT,
  grade                 SMALLINT       CHECK (grade BETWEEN -1 AND 12), -- -1 = PreK
  tshirt_size           tshirt_size,
  allergies             JSONB          NOT NULL DEFAULT '[]',
  medications           JSONB          NOT NULL DEFAULT '[]',
  dietary_restrictions  TEXT,
  special_needs_notes   TEXT,
  preferences_notes     TEXT,
  created_at            TIMESTAMPTZ    NOT NULL DEFAULT now(),
  updated_at            TIMESTAMPTZ    NOT NULL DEFAULT now()
);

CREATE INDEX idx_children_parent     ON children (parent_id);
CREATE INDEX idx_children_dob        ON children (date_of_birth);

-- GIN indexes for JSONB containment queries
-- e.g. allergies @> '[{"type": "peanut"}]'
CREATE INDEX idx_children_allergies    ON children USING GIN (allergies);
CREATE INDEX idx_children_medications  ON children USING GIN (medications);

CREATE TRIGGER trg_children_updated_at
  BEFORE UPDATE ON children
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

// allergies: array of allergy objects
[
  { "type": "peanut", "severity": "anaphylactic", "notes": "Carries EpiPen" },
  { "type": "bee_sting", "severity": "mild", "notes": "" }
]

// medications: array of medication objects
[
  {
    "name": "Adderall XR",
    "dosage": "10mg",
    "frequency": "daily_morning",
    "prescriber": "Dr. Nguyen",
    "notes": "Do not administer after 2pm"
  }
]

-- ── emergency_contacts ─────────────────────────────────────────────
CREATE TABLE emergency_contacts (
  id            UUID     PRIMARY KEY DEFAULT gen_random_uuid(),
  child_id      UUID     NOT NULL REFERENCES children(id) ON DELETE CASCADE,
  name          TEXT     NOT NULL,
  relationship  TEXT     NOT NULL,
  phone         TEXT     NOT NULL,
  email         TEXT,
  sort_order    SMALLINT NOT NULL DEFAULT 0
);

CREATE INDEX idx_ec_child ON emergency_contacts (child_id, sort_order);

-- ── insurance_info ──────────────────────────────────────────────────
CREATE TABLE insurance_info (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  child_id       UUID NOT NULL UNIQUE REFERENCES children(id) ON DELETE CASCADE,
  provider       TEXT NOT NULL,
  policy_number  TEXT NOT NULL,
  group_number   TEXT
);

-- ── authorized_pickups ──────────────────────────────────────────────
CREATE TABLE authorized_pickups (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  child_id      UUID NOT NULL REFERENCES children(id) ON DELETE CASCADE,
  name          TEXT NOT NULL,
  relationship  TEXT NOT NULL,
  phone         TEXT NOT NULL
);

CREATE INDEX idx_pickups_child ON authorized_pickups (child_id);

-- ── documents ───────────────────────────────────────────────────────
CREATE TYPE doc_category AS ENUM (
  'physical_exam', 'immunization', 'insurance_card', 'photo_id', 'other'
);

CREATE TABLE documents (
  id          UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  child_id    UUID           NOT NULL REFERENCES children(id) ON DELETE CASCADE,
  name        TEXT           NOT NULL,
  file_key    TEXT           NOT NULL UNIQUE, -- S3 object key
  file_type   TEXT           NOT NULL,         -- MIME type
  category    doc_category   NOT NULL,
  expires_at  DATE,
  uploaded_at TIMESTAMPTZ    NOT NULL DEFAULT now()
);

CREATE INDEX idx_docs_child    ON documents (child_id);
CREATE INDEX idx_docs_category ON documents (child_id, category);
CREATE INDEX idx_docs_expires  ON documents (expires_at) WHERE expires_at IS NOT NULL;

CREATE TYPE camp_type      AS ENUM ('day', 'overnight', 'hybrid');
CREATE TYPE schedule_type  AS ENUM ('full_day', 'half_am', 'half_pm');
CREATE TYPE session_status AS ENUM ('open', 'full', 'waitlist', 'cancelled');

CREATE TABLE camps (
  id                       UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  director_id              UUID           NOT NULL REFERENCES users(id),
  name                     TEXT           NOT NULL,
  description              TEXT,
  location                 TEXT,          -- street address display string
  city                     TEXT           NOT NULL,
  state                    CHAR(2)        NOT NULL,
  zip                      TEXT           NOT NULL,
  latitude                 NUMERIC(9,6)  NOT NULL,
  longitude                NUMERIC(9,6)  NOT NULL,
  camp_type                camp_type      NOT NULL DEFAULT 'day',
  age_min                  SMALLINT       NOT NULL,
  age_max                  SMALLINT       NOT NULL,
  schedule_type            schedule_type  NOT NULL DEFAULT 'full_day',
  price_per_week           NUMERIC(8,2),
  counselor_ratio          TEXT,          -- e.g. '1:8'
  years_operating          SMALLINT,
  scholarship_available    BOOLEAN        NOT NULL DEFAULT false,
  inclusive_accommodations BOOLEAN        NOT NULL DEFAULT false,
  search_vector            TSVECTOR,      -- full-text search
  created_at               TIMESTAMPTZ    NOT NULL DEFAULT now(),
  updated_at               TIMESTAMPTZ    NOT NULL DEFAULT now(),

  CONSTRAINT chk_age_range CHECK (age_min <= age_max),
  CONSTRAINT chk_lat       CHECK (latitude  BETWEEN -90  AND 90),
  CONSTRAINT chk_lng       CHECK (longitude BETWEEN -180 AND 180)
);

CREATE INDEX idx_camps_director     ON camps (director_id);
CREATE INDEX idx_camps_state        ON camps (state);
CREATE INDEX idx_camps_age          ON camps (age_min, age_max);
CREATE INDEX idx_camps_geo          ON camps (latitude, longitude);
CREATE INDEX idx_camps_fts          ON camps USING GIN (search_vector);
CREATE INDEX idx_camps_scholarship  ON camps (scholarship_available) WHERE scholarship_available = true;
CREATE INDEX idx_camps_inclusive    ON camps (inclusive_accommodations) WHERE inclusive_accommodations = true;

-- Auto-generate tsvector from name + description + city
CREATE OR REPLACE FUNCTION camps_search_vector_update()
  RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.city, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(NEW.description, '')), 'C');
  RETURN NEW;
END; $$;

CREATE TRIGGER trg_camps_fts
  BEFORE INSERT OR UPDATE ON camps
  FOR EACH ROW EXECUTE FUNCTION camps_search_vector_update();

CREATE TRIGGER trg_camps_updated_at
  BEFORE UPDATE ON camps
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ── sessions ────────────────────────────────────────────────────────
CREATE TABLE sessions (
  id              UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  camp_id         UUID           NOT NULL REFERENCES camps(id) ON DELETE CASCADE,
  start_date      DATE           NOT NULL,
  end_date        DATE           NOT NULL,
  capacity        SMALLINT       NOT NULL CHECK (capacity > 0),
  spots_remaining SMALLINT       NOT NULL CHECK (spots_remaining >= 0),
  status          session_status NOT NULL DEFAULT 'open',

  CONSTRAINT chk_dates    CHECK (end_date > start_date),
  CONSTRAINT chk_spots   CHECK (spots_remaining <= capacity)
);

CREATE INDEX idx_sessions_camp    ON sessions (camp_id);
CREATE INDEX idx_sessions_dates   ON sessions (start_date, end_date);
CREATE INDEX idx_sessions_open    ON sessions (camp_id, status) WHERE status = 'open';
CREATE INDEX idx_sessions_future  ON sessions (start_date) WHERE start_date > now();

CREATE TYPE application_status AS ENUM (
  'draft', 'submitted', 'under_review', 'accepted',
  'waitlisted', 'declined', 'withdrawn'
);

CREATE TABLE applications (
  id                       UUID               PRIMARY KEY DEFAULT gen_random_uuid(),
  child_id                 UUID               NOT NULL REFERENCES children(id),
  session_id               UUID               NOT NULL REFERENCES sessions(id),
  status                   application_status NOT NULL DEFAULT 'draft',
  submitted_at             TIMESTAMPTZ,
  responded_at             TIMESTAMPTZ,
  waitlist_position        SMALLINT,
  -- Keys are form_mappings.id; values are the parent's answers
  camp_specific_responses  JSONB              NOT NULL DEFAULT '{}',
  created_at               TIMESTAMPTZ        NOT NULL DEFAULT now(),
  updated_at               TIMESTAMPTZ        NOT NULL DEFAULT now(),

  -- One child can only have one active application per session
  CONSTRAINT uq_child_session UNIQUE (child_id, session_id)
);

CREATE INDEX idx_apps_child       ON applications (child_id);
CREATE INDEX idx_apps_session     ON applications (session_id);
CREATE INDEX idx_apps_status      ON applications (status);
CREATE INDEX idx_apps_submitted   ON applications (submitted_at) WHERE submitted_at IS NOT NULL;
CREATE INDEX idx_apps_responses   ON applications USING GIN (camp_specific_responses);
CREATE INDEX idx_apps_waitlist    ON applications (session_id, waitlist_position)
  WHERE status = 'waitlisted';

CREATE TRIGGER trg_apps_updated_at
  BEFORE UPDATE ON applications
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Decrement spots_remaining when an application is accepted
CREATE OR REPLACE FUNCTION manage_session_capacity()
  RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status = 'accepted' AND (OLD.status IS NULL OR OLD.status != 'accepted') THEN
    UPDATE sessions
    SET    spots_remaining = spots_remaining - 1,
           status = CASE WHEN spots_remaining - 1 = 0 THEN 'full'::session_status
                         ELSE status END
    WHERE  id = NEW.session_id;
  ELSIF OLD.status = 'accepted' AND NEW.status = 'withdrawn' THEN
    UPDATE sessions
    SET    spots_remaining = spots_remaining + 1,
           status = 'open'
    WHERE  id = NEW.session_id;
  END IF;
  RETURN NEW;
END; $$;

CREATE TRIGGER trg_capacity
  AFTER UPDATE ON applications
  FOR EACH ROW EXECUTE FUNCTION manage_session_capacity();

CREATE TABLE reviews (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  parent_id   UUID        NOT NULL REFERENCES users(id),
  camp_id     UUID        NOT NULL REFERENCES camps(id) ON DELETE CASCADE,
  session_id  UUID        REFERENCES sessions(id),
  rating      SMALLINT    NOT NULL CHECK (rating BETWEEN 1 AND 5),
  text        TEXT,
  verified    BOOLEAN     NOT NULL DEFAULT false,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- One review per parent per camp session
  CONSTRAINT uq_review UNIQUE (parent_id, camp_id, session_id)
);

CREATE INDEX idx_reviews_camp      ON reviews (camp_id);
CREATE INDEX idx_reviews_verified  ON reviews (camp_id, verified) WHERE verified = true;

-- ── saved_camps ─────────────────────────────────────────────────────
CREATE TABLE saved_camps (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  parent_id   UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  camp_id     UUID        NOT NULL REFERENCES camps(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  CONSTRAINT uq_saved UNIQUE (parent_id, camp_id)
);

CREATE INDEX idx_saved_parent ON saved_camps (parent_id);

CREATE TABLE form_mappings (
  id                 UUID    PRIMARY KEY DEFAULT gen_random_uuid(),
  camp_id            UUID    NOT NULL REFERENCES camps(id) ON DELETE CASCADE,
  -- Dot-path into the child/related profile, e.g.:
  -- 'child.full_name', 'child.allergies', 'emergency_contacts.0.phone'
  canonical_field    TEXT    NOT NULL,
  camp_field_label   TEXT    NOT NULL,
  required           BOOLEAN NOT NULL DEFAULT true,
  sort_order         SMALLINT NOT NULL DEFAULT 0,
  help_text          TEXT,

  CONSTRAINT uq_camp_canonical UNIQUE (camp_id, canonical_field)
);

CREATE INDEX idx_fm_camp             ON form_mappings (camp_id, sort_order);
CREATE INDEX idx_fm_canonical        ON form_mappings (canonical_field);

-- Camps within 10 miles of lat/lng point (zip 78704, Austin TX)
-- Step 1: bounding box (uses idx_camps_geo B-tree, very fast)
-- Step 2: Haversine rerank for accurate distance

WITH origin AS (
  SELECT
    30.2500  AS lat,
    -97.7500 AS lng,
    10.0     AS radius_miles
),
bbox AS (
  SELECT
    lat - (radius_miles / 69.0)  AS lat_min,
    lat + (radius_miles / 69.0)  AS lat_max,
    lng - (radius_miles / cos(radians(lat)) / 69.0) AS lng_min,
    lng + (radius_miles / cos(radians(lat)) / 69.0) AS lng_max,
    lat, lng, radius_miles
  FROM origin
)
SELECT
  c.id,
  c.name,
  c.city,
  c.price_per_week,
  3959 * acos(
    cos(radians(b.lat)) * cos(radians(c.latitude)) *
    cos(radians(c.longitude) - radians(b.lng)) +
    sin(radians(b.lat)) * sin(radians(c.latitude))
  ) AS distance_miles
FROM       camps c
CROSS JOIN bbox  b
WHERE
  c.latitude  BETWEEN b.lat_min AND b.lat_max
  AND c.longitude BETWEEN b.lng_min AND b.lng_max
HAVING
  3959 * acos(
    cos(radians(b.lat)) * cos(radians(c.latitude)) *
    cos(radians(c.longitude) - radians(b.lng)) +
    sin(radians(b.lat)) * sin(radians(c.latitude))
  ) <= b.radius_miles
ORDER BY distance_miles
LIMIT 50;

CREATE EXTENSION IF NOT EXISTS postgis;

-- Add geography column (uses WGS-84 spheroid — accurate to meters)
ALTER TABLE camps
  ADD COLUMN geog GEOGRAPHY(Point, 4326);

-- Back-fill from existing lat/lng
UPDATE camps
SET    geog = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

-- GIST spatial index (replaces lat/lng B-tree for proximity)
CREATE INDEX idx_camps_geog ON camps USING GIST (geog);

-- PostGIS proximity query (cleaner, uses index natively)
SELECT
  c.id, c.name,
  ST_Distance(c.geog, ST_MakePoint(-97.75, 30.25)::geography) / 1609.34 AS miles
FROM  camps c
WHERE ST_DWithin(c.geog, ST_MakePoint(-97.75, 30.25)::geography, 16093.4) -- 10 miles in meters
ORDER BY miles
LIMIT  50;

-- Enable RLS on all user-data tables
ALTER TABLE children             ENABLE ROW LEVEL SECURITY;
ALTER TABLE emergency_contacts   ENABLE ROW LEVEL SECURITY;
ALTER TABLE insurance_info       ENABLE ROW LEVEL SECURITY;
ALTER TABLE authorized_pickups   ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents            ENABLE ROW LEVEL SECURITY;
ALTER TABLE camps                ENABLE ROW LEVEL SECURITY;
ALTER TABLE sessions             ENABLE ROW LEVEL SECURITY;
ALTER TABLE applications         ENABLE ROW LEVEL SECURITY;
ALTER TABLE saved_camps          ENABLE ROW LEVEL SECURITY;
ALTER TABLE reviews              ENABLE ROW LEVEL SECURITY;
ALTER TABLE form_mappings        ENABLE ROW LEVEL SECURITY;

-- ── CHILDREN: parents own their children ──────────────────────────
CREATE POLICY children_parent_all ON children
  USING      (parent_id = current_setting('app.user_id')::uuid)
  WITH CHECK (parent_id = current_setting('app.user_id')::uuid);

-- ── EMERGENCY CONTACTS: visible if parent owns the child ──────────
CREATE POLICY ec_parent_all ON emergency_contacts
  USING (
    EXISTS (
      SELECT 1 FROM children
      WHERE  id = emergency_contacts.child_id
      AND    parent_id = current_setting('app.user_id')::uuid
    )
  );

-- Same pattern for insurance_info, authorized_pickups, documents
-- (omitted for brevity — replace table name and child FK column)

-- ── CAMPS: public read, director write ────────────────────────────
CREATE POLICY camps_public_read ON camps
  FOR SELECT USING (true);

CREATE POLICY camps_director_write ON camps
  FOR ALL
  USING      (director_id = current_setting('app.user_id')::uuid)
  WITH CHECK (director_id = current_setting('app.user_id')::uuid);

-- ── APPLICATIONS: parent sees own child's apps; director sees apps
--                 for their sessions only ──────────────────────────
CREATE POLICY apps_parent_read ON applications
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM children
      WHERE  id = applications.child_id
      AND    parent_id = current_setting('app.user_id')::uuid
    )
  );

CREATE POLICY apps_director_read ON applications
  FOR SELECT USING (
    EXISTS (
      SELECT 1
      FROM   sessions s
      JOIN   camps    c ON c.id = s.camp_id
      WHERE  s.id = applications.session_id
      AND    c.director_id = current_setting('app.user_id')::uuid
    )
  );

-- Director may update status only (not child_id, session_id)
CREATE POLICY apps_director_status ON applications
  FOR UPDATE USING (
    EXISTS (
      SELECT 1
      FROM   sessions s
      JOIN   camps    c ON c.id = s.camp_id
      WHERE  s.id = applications.session_id
      AND    c.director_id = current_setting('app.user_id')::uuid
    )
  );

-- Set current user in every transaction (called by API middleware)
-- SET LOCAL app.user_id = '<uuid>';

-- Verified parental deletion request: wipe child and all related PII
CREATE OR REPLACE PROCEDURE delete_child_data(p_child_id UUID, p_requesting_parent UUID)
LANGUAGE plpgsql AS $$
DECLARE
  v_parent_id UUID;
BEGIN
  -- Verify caller is the parent
  SELECT parent_id INTO v_parent_id
  FROM   children
  WHERE  id = p_child_id;

  IF v_parent_id IS NULL OR v_parent_id != p_requesting_parent THEN
    RAISE EXCEPTION 'Unauthorized deletion attempt for child %', p_child_id;
  END IF;

  -- Collect S3 keys before deletion for async cleanup
  INSERT INTO s3_deletion_queue (file_key, queued_at)
  SELECT file_key, now() FROM documents WHERE child_id = p_child_id;

  -- Cascade delete handles all child-related tables
  DELETE FROM children WHERE id = p_child_id;

  INSERT INTO audit_log (actor_id, action, table_name, row_id, accessed_at)
  VALUES (p_requesting_parent, 'COPPA_DELETE', 'children', p_child_id, now());

  COMMIT;
END; $$;

-- Fetch all form mappings for a camp, with resolved profile values
-- Application layer resolves dot-paths; DB supplies the raw profile

SELECT
  fm.id                AS mapping_id,
  fm.canonical_field,
  fm.camp_field_label,
  fm.required,
  fm.sort_order,
  fm.help_text,

  -- The full child profile row — app resolves canonical_field dot-path
  row_to_json(ch.*)  AS child_profile,
  row_to_json(ii.*)  AS insurance,
  row_to_json(u.*)   AS parent,

  -- Emergency contacts as ordered array
  (SELECT json_agg(ec ORDER BY ec.sort_order)
   FROM emergency_contacts ec
   WHERE ec.child_id = ch.id) AS emergency_contacts

FROM       form_mappings fm
JOIN       children      ch ON ch.id        = $1  -- :child_id
JOIN       users         u  ON u.id         = ch.parent_id
LEFT JOIN  insurance_info ii ON ii.child_id = ch.id
WHERE      fm.camp_id = $2  -- :camp_id
ORDER BY   fm.sort_order;

-- Range-partition applications by enrollment year
-- (submitted_at year, NULL drafts go to current partition)

CREATE TABLE applications_2025
  PARTITION OF applications
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE applications_2026
  PARTITION OF applications
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- Partition pruning kicks in automatically for queries like:
-- WHERE submitted_at >= '2026-01-01'
-- Result: query scans 1 partition instead of full 2M-row table