Data Model

-- ─────────────────────────────────────────────────────────────
-- Enum Types
-- Create before tables. ALTER TYPE ... ADD VALUE is safe for
-- adding new members without a migration lock.
-- ─────────────────────────────────────────────────────────────

CREATE TYPE user_role AS ENUM (
  'homeowner',
  'contractor',
  'admin'
);

CREATE TYPE project_status AS ENUM (
  'setup',      -- contract not yet signed
  'active',     -- work underway
  'paused',     -- work temporarily halted
  'completed',  -- final walkthrough done
  'disputed'    -- active dispute in progress
);

CREATE TYPE milestone_status AS ENUM (
  'upcoming',
  'in_progress',
  'completed',
  'blocked'
);

CREATE TYPE change_order_type AS ENUM (
  'unforeseen',        -- hidden condition discovered during work
  'homeowner_request', -- scope addition requested by owner
  'code_requirement'   -- mandated by inspection / permit
);

CREATE TYPE change_order_status AS ENUM (
  'pending',
  'approved',
  'declined',
  'modified'  -- counter-offered by homeowner
);

CREATE TYPE payment_status AS ENUM (
  'scheduled',
  'due',
  'paid',
  'overdue'
);

CREATE TYPE document_category AS ENUM (
  'contract',
  'permit',
  'inspection',
  'change_order',
  'insurance',
  'license'
);

CREATE TYPE activity_source AS ENUM (
  'contractor',
  'system',
  'city',
  'homeowner'
);

CREATE TYPE activity_event_type AS ENUM (
  'milestone_update',
  'change_order',
  'delivery',
  'inspection',
  'payment',
  'note'
);

CREATE TABLE users (
  id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  email         TEXT        NOT NULL UNIQUE
                              CHECK (lower(email) = email),
  name          TEXT        NOT NULL CHECK (length(name) >= 1),
  role          user_role   NOT NULL DEFAULT 'homeowner',
  password_hash TEXT        NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Enforce lowercase email at DB level to prevent duplicate accounts
CREATE UNIQUE INDEX idx_users_email_lower
  ON users (lower(email));

-- 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 TABLE contractor_profiles (
  id                          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id                     UUID        NOT NULL UNIQUE
                                            REFERENCES users(id)
                                            ON DELETE CASCADE,
  company_name                TEXT        NOT NULL,
  license_number              TEXT,        -- encrypted via pgcrypto in application layer
  license_state               CHAR(2),
  license_verified            BOOLEAN     NOT NULL DEFAULT FALSE,
  insurance_verified          BOOLEAN     NOT NULL DEFAULT FALSE,
  years_in_business           SMALLINT    CHECK (years_in_business >= 0),
  avg_timeline_variance_pct   NUMERIC(6,2), -- positive = over schedule, negative = early
  avg_change_order_pct        NUMERIC(6,2),
  total_projects_on_platform  INTEGER     NOT NULL DEFAULT 0
                                            CHECK (total_projects_on_platform >= 0),
  created_at                  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_contractor_profiles_user_id
  ON contractor_profiles (user_id);

CREATE INDEX idx_contractor_profiles_verified
  ON contractor_profiles (license_verified, insurance_verified)
  WHERE license_verified = TRUE;   -- partial index: only verified contractors

CREATE TRIGGER trg_contractor_profiles_updated_at
  BEFORE UPDATE ON contractor_profiles
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE projects (
  id                       UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  homeowner_id             UUID           NOT NULL
                                           REFERENCES users(id)
                                           ON DELETE RESTRICT,
  contractor_id            UUID           REFERENCES users(id)
                                           ON DELETE SET NULL,
  name                     TEXT           NOT NULL,
  address                  TEXT           NOT NULL,
  city                     TEXT           NOT NULL,
  state                    CHAR(2)        NOT NULL,
  zip                      CHAR(10)       NOT NULL,
  original_contract_amount NUMERIC(12,2)  NOT NULL
                                           CHECK (original_contract_amount > 0),
  current_total            NUMERIC(12,2)  NOT NULL,
  start_date               DATE,
  expected_end_date        DATE,
  actual_end_date          DATE,
  status                   project_status NOT NULL DEFAULT 'setup',
  created_at               TIMESTAMPTZ    NOT NULL DEFAULT now(),
  updated_at               TIMESTAMPTZ    NOT NULL DEFAULT now(),

  CONSTRAINT chk_projects_end_after_start
    CHECK (expected_end_date IS NULL OR start_date IS NULL
           OR expected_end_date >= start_date),
  CONSTRAINT chk_projects_current_gte_original
    CHECK (current_total >= 0)
);

CREATE INDEX idx_projects_homeowner_id   ON projects (homeowner_id);
CREATE INDEX idx_projects_contractor_id  ON projects (contractor_id)
  WHERE contractor_id IS NOT NULL;
CREATE INDEX idx_projects_status        ON projects (status);
CREATE INDEX idx_projects_state_status  ON projects (state, status);

CREATE TRIGGER trg_projects_updated_at
  BEFORE UPDATE ON projects
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE milestones (
  id                      UUID             PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id              UUID             NOT NULL
                                           REFERENCES projects(id)
                                           ON DELETE CASCADE,
  name                    TEXT             NOT NULL,
  description             TEXT,
  sequence_order          SMALLINT         NOT NULL,
  planned_start           DATE,
  planned_end             DATE,
  actual_start            DATE,
  actual_end              DATE,
  status                  milestone_status NOT NULL DEFAULT 'upcoming',
  depends_on_milestone_id UUID             REFERENCES milestones(id)
                                           ON DELETE SET NULL,
  created_at              TIMESTAMPTZ      NOT NULL DEFAULT now(),
  updated_at              TIMESTAMPTZ      NOT NULL DEFAULT now(),

  CONSTRAINT chk_milestones_no_self_dep
    CHECK (depends_on_milestone_id <> id),
  CONSTRAINT uq_milestones_project_order
    UNIQUE (project_id, sequence_order)
);

CREATE INDEX idx_milestones_project_id       ON milestones (project_id);
CREATE INDEX idx_milestones_project_status   ON milestones (project_id, status);
CREATE INDEX idx_milestones_depends_on       ON milestones (depends_on_milestone_id)
  WHERE depends_on_milestone_id IS NOT NULL;

CREATE TRIGGER trg_milestones_updated_at
  BEFORE UPDATE ON milestones
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE change_orders (
  id                   UUID                PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id           UUID                NOT NULL
                                           REFERENCES projects(id)
                                           ON DELETE CASCADE,
  submitted_by         UUID                NOT NULL
                                           REFERENCES users(id)
                                           ON DELETE RESTRICT,
  amount               NUMERIC(12,2)       NOT NULL,
  markup_percentage    NUMERIC(5,2)        NOT NULL DEFAULT 0
                                           CHECK (markup_percentage >= 0),
  description          TEXT                NOT NULL,
  type                 change_order_type   NOT NULL,
  status               change_order_status NOT NULL DEFAULT 'pending',
  groundwork_analysis  JSONB,
  signed_at            TIMESTAMPTZ,
  created_at           TIMESTAMPTZ         NOT NULL DEFAULT now(),
  updated_at           TIMESTAMPTZ         NOT NULL DEFAULT now(),

  CONSTRAINT chk_co_signed_only_when_approved
    CHECK (signed_at IS NULL OR status = 'approved')
);

CREATE INDEX idx_change_orders_project_id     ON change_orders (project_id);
CREATE INDEX idx_change_orders_project_status ON change_orders (project_id, status);
CREATE INDEX idx_change_orders_submitted_by   ON change_orders (submitted_by);

-- GIN index for JSONB analysis querying
CREATE INDEX idx_change_orders_analysis_gin
  ON change_orders USING GIN (groundwork_analysis)
  WHERE groundwork_analysis IS NOT NULL;

CREATE TRIGGER trg_change_orders_updated_at
  BEFORE UPDATE ON change_orders
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE payments (
  id           UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id   UUID           NOT NULL
                               REFERENCES projects(id)
                               ON DELETE CASCADE,
  milestone_id UUID           REFERENCES milestones(id)
                               ON DELETE SET NULL,
  amount       NUMERIC(12,2)  NOT NULL CHECK (amount > 0),
  status       payment_status NOT NULL DEFAULT 'scheduled',
  due_date     DATE           NOT NULL,
  paid_date    DATE,
  created_at   TIMESTAMPTZ    NOT NULL DEFAULT now(),

  CONSTRAINT chk_payments_paid_date_requires_paid_status
    CHECK (paid_date IS NULL OR status = 'paid')
);

CREATE INDEX idx_payments_project_id        ON payments (project_id);
CREATE INDEX idx_payments_milestone_id      ON payments (milestone_id)
  WHERE milestone_id IS NOT NULL;
CREATE INDEX idx_payments_project_status    ON payments (project_id, status);
-- Partial index: only overdue/due payments — most common dashboard query
CREATE INDEX idx_payments_due_overdue       ON payments (due_date, project_id)
  WHERE status IN ('due', 'overdue');

CREATE TABLE documents (
  id              UUID              PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id      UUID              NOT NULL
                                    REFERENCES projects(id)
                                    ON DELETE CASCADE,
  uploaded_by     UUID              NOT NULL
                                    REFERENCES users(id)
                                    ON DELETE RESTRICT,
  name            TEXT              NOT NULL,
  file_key        TEXT              NOT NULL UNIQUE,  -- R2 object key
  file_type       TEXT              NOT NULL,
  file_size_bytes BIGINT            NOT NULL CHECK (file_size_bytes > 0),
  category        document_category NOT NULL,
  created_at      TIMESTAMPTZ       NOT NULL DEFAULT now()
);

CREATE INDEX idx_documents_project_id          ON documents (project_id);
CREATE INDEX idx_documents_project_category    ON documents (project_id, category);
CREATE INDEX idx_documents_uploaded_by         ON documents (uploaded_by);

CREATE TABLE contract_health_scores (
  id                        UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id                UUID        NOT NULL
                                        REFERENCES projects(id)
                                        ON DELETE CASCADE,
  overall_score             SMALLINT    NOT NULL CHECK (overall_score BETWEEN 0 AND 100),
  payment_schedule_score    SMALLINT    NOT NULL CHECK (payment_schedule_score BETWEEN 0 AND 100),
  timeline_specificity_score SMALLINT   NOT NULL CHECK (timeline_specificity_score BETWEEN 0 AND 100),
  change_order_clause_score SMALLINT    NOT NULL CHECK (change_order_clause_score BETWEEN 0 AND 100),
  dispute_resolution_score  SMALLINT    NOT NULL CHECK (dispute_resolution_score BETWEEN 0 AND 100),
  completion_definition_score SMALLINT  NOT NULL CHECK (completion_definition_score BETWEEN 0 AND 100),
  warranty_score            SMALLINT    NOT NULL CHECK (warranty_score BETWEEN 0 AND 100),
  insurance_licensing_score SMALLINT    NOT NULL CHECK (insurance_licensing_score BETWEEN 0 AND 100),
  lien_waiver_score         SMALLINT    NOT NULL CHECK (lien_waiver_score BETWEEN 0 AND 100),
  cancellation_terms_score  SMALLINT    NOT NULL CHECK (cancellation_terms_score BETWEEN 0 AND 100),
  analysis_json             JSONB       NOT NULL,
  created_at                TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_chs_project_id          ON contract_health_scores (project_id);
CREATE INDEX idx_chs_project_created     ON contract_health_scores (project_id, created_at DESC);
CREATE INDEX idx_chs_overall_score       ON contract_health_scores (overall_score);
CREATE INDEX idx_chs_analysis_gin        ON contract_health_scores USING GIN (analysis_json);

CREATE TABLE activity_feed (
  id          UUID                 PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id  UUID                 NOT NULL
                                   REFERENCES projects(id)
                                   ON DELETE CASCADE,
  actor_id    UUID                 REFERENCES users(id)
                                   ON DELETE SET NULL,
  source      activity_source      NOT NULL,
  event_type  activity_event_type  NOT NULL,
  description TEXT                 NOT NULL,
  metadata    JSONB,
  created_at  TIMESTAMPTZ          NOT NULL DEFAULT now()
);

-- Primary access pattern: project feed ordered by time (most recent first)
CREATE INDEX idx_activity_project_created
  ON activity_feed (project_id, created_at DESC);
CREATE INDEX idx_activity_project_event_type
  ON activity_feed (project_id, event_type);
-- GIN for metadata key searches (e.g. find events referencing a specific change_order_id)
CREATE INDEX idx_activity_metadata_gin
  ON activity_feed USING GIN (metadata)
  WHERE metadata IS NOT NULL;

CREATE TABLE audit_log (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID        REFERENCES users(id)
                          ON DELETE SET NULL,
  action      TEXT        NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
  entity_type TEXT        NOT NULL,
  entity_id   UUID        NOT NULL,
  old_value   JSONB,
  new_value   JSONB,
  ip_address  INET,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Lookup by user for compliance review
CREATE INDEX idx_audit_user_id         ON audit_log (user_id, created_at DESC);
-- Lookup all changes to a specific record
CREATE INDEX idx_audit_entity          ON audit_log (entity_type, entity_id, created_at DESC);
CREATE INDEX idx_audit_created_at      ON audit_log (created_at);  -- for time-range compliance exports

-- audit_log rows must never be deleted or updated — enforce via RLS
ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;

-- Convert audit_log to a partitioned table.
-- Do this at schema creation — retrofitting requires pg_partman or table swap.

CREATE TABLE audit_log (
  ...  -- same columns as above
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Create monthly partitions (use pg_partman to automate)
CREATE TABLE audit_log_2026_04
  PARTITION OF audit_log
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- Old partitions can be detached + archived to cold storage (e.g. S3/R2 pg_dump)
-- without affecting the live table
ALTER TABLE audit_log DETACH PARTITION audit_log_2024_01;

-- Enable RLS on all application tables
ALTER TABLE users                  ENABLE ROW LEVEL SECURITY;
ALTER TABLE contractor_profiles    ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects              ENABLE ROW LEVEL SECURITY;
ALTER TABLE milestones            ENABLE ROW LEVEL SECURITY;
ALTER TABLE change_orders        ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments             ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents            ENABLE ROW LEVEL SECURITY;
ALTER TABLE contract_health_scores ENABLE ROW LEVEL SECURITY;
ALTER TABLE activity_feed        ENABLE ROW LEVEL SECURITY;

-- Helper functions used across policies
CREATE OR REPLACE FUNCTION current_user_id() RETURNS UUID LANGUAGE sql STABLE AS $$
  SELECT nullif(current_setting('app.current_user_id', TRUE), '')::UUID
$$;

CREATE OR REPLACE FUNCTION current_user_role() RETURNS TEXT LANGUAGE sql STABLE AS $$
  SELECT current_setting('app.current_role', TRUE)
$$;

CREATE OR REPLACE FUNCTION is_project_participant(p_id UUID)
RETURNS BOOLEAN LANGUAGE sql STABLE SECURITY DEFINER AS $$
  SELECT EXISTS (
    SELECT 1 FROM projects
    WHERE id = p_id
      AND (homeowner_id = current_user_id()
           OR contractor_id = current_user_id())
  )
$$;

V{version}__{description}.sql

Examples:
V001__create_enum_types.sql
V002__create_users.sql
V010__create_audit_log.sql
V011__add_contractor_profiles_insurance_expiry.sql
V012__add_index_projects_state_status.sql

-- Repeatable migrations (views, functions, triggers)
-- Re-run whenever the file changes:
R__set_updated_at_function.sql
R__rls_policies_projects.sql

# Run in CI/CD before deploying application code
# Migrations must be backward-compatible with the previous app version

# Flyway example
flyway -url="jdbc:postgresql://host/groundwork" \
       -user="$DB_USER" \
       -password="$DB_PASSWORD" \
       -locations="filesystem:./migrations" \
       migrate

# Verify: check applied migrations match expected count
flyway info | grep "Pending" | wc -l  # should be 0 after successful migrate

-- Development seed: creates a known admin user.
-- Password: 'devpassword123' (Argon2id hash shown below is illustrative)
-- DO NOT run this seed in production.

INSERT INTO users (id, email, name, role, password_hash)
VALUES (
  '00000000-0000-0000-0000-000000000001',
  'admin@groundwork.dev',
  'Groundwork Admin',
  'admin',
  '$argon2id$v=19$m=65536,t=3,p=4$...'
)
ON CONFLICT (id) DO NOTHING;

-- Sample homeowner
INSERT INTO users (id, email, name, role, password_hash)
VALUES (
  '00000000-0000-0000-0000-000000000002',
  'homeowner@groundwork.dev',
  'Jane Homeowner',
  'homeowner',
  '$argon2id$v=19$m=65536,t=3,p=4$...'
)
ON CONFLICT (id) DO NOTHING;

-- Sample contractor + profile
INSERT INTO users (id, email, name, role, password_hash)
VALUES (
  '00000000-0000-0000-0000-000000000003',
  'contractor@groundwork.dev',
  'Bob Builder',
  'contractor',
  '$argon2id$v=19$m=65536,t=3,p=4$...'
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO contractor_profiles
  (user_id, company_name, license_state,
   license_verified, insurance_verified, years_in_business)
VALUES (
  '00000000-0000-0000-0000-000000000003',
  'Bob Builder LLC',
  'CA',
  TRUE, TRUE, 12
)
ON CONFLICT (user_id) DO NOTHING;

-- No user records in production seed.
-- Enums are in DDL. No lookup tables required.
--
-- What does ship to production:
--   1. Database configuration settings
--   2. pg_partman setup for audit_log partitioning
--   3. Nightly maintenance job configuration

-- Ensure pg_partman extension is available
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Register audit_log with pg_partman for automatic monthly partition creation
SELECT partman.create_parent(
  p_parent_table => 'public.audit_log',
  p_control      => 'created_at',
  p_interval     => 'monthly',
  p_premake      => 3   -- pre-create 3 months ahead
);

-- Activity feed: partition when it approaches 500K rows (Year 2+)
-- Register same way when partitioning is added via migration.