create_eval_tables.sql

BEGIN;

-- ============================================================================
-- SeedFast AI — Evaluation Database Schema
-- ============================================================================
-- 14 tables across 2 schemas (org, pm) covering 19 database patterns.
-- Designed for AI seeding evaluation: per-table, per-level, and full-run tests.
--
-- Dependency levels:
--   L0: org.offices, pm.clients, pm.tags               (roots)
--   L1: org.departments, pm.projects                    (depend on L0)
--   L2: org.employees, pm.milestones, pm.contracts,     (depend on L0-L1)
--       pm.employee_profiles
--   L3: pm.tasks, pm.sprints, pm.reviews                (3-table cycle)
--   L4: pm.time_entries, pm.task_tags                    (depend on cycle)
-- ============================================================================


-- === Extensions ===
CREATE EXTENSION IF NOT EXISTS pgcrypto;   -- gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS citext;     -- case-insensitive text


-- === Schemas ===
CREATE SCHEMA IF NOT EXISTS org;
CREATE SCHEMA IF NOT EXISTS pm;


-- === Domain ===
CREATE DOMAIN email_domain AS CITEXT
  CHECK (VALUE ~* '^[^@\s]+@[^@\s]+\.[^@\s]+$');


-- === ENUM types ===
CREATE TYPE employment_type AS ENUM ('full_time', 'part_time', 'contractor', 'intern');
CREATE TYPE project_status  AS ENUM ('planning', 'active', 'on_hold', 'completed', 'cancelled');
CREATE TYPE task_status     AS ENUM ('todo', 'in_progress', 'in_review', 'done', 'blocked');
CREATE TYPE contract_type   AS ENUM ('fixed_price', 'time_and_materials', 'retainer');
CREATE TYPE review_type     AS ENUM ('code_review', 'design_review', 'sprint_review');
CREATE TYPE review_status   AS ENUM ('pending', 'approved', 'changes_requested', 'rejected');


-- === Helper function ===
CREATE OR REPLACE FUNCTION set_updated_at()
  RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END $$;


-- ============================================================================
-- LEVEL 0 — Root tables (no foreign keys)
-- ============================================================================

-- org.offices: JSONB, citext, CHECK regex, defaults
CREATE TABLE org.offices (
  id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  name         text        NOT NULL,
  city         CITEXT      NOT NULL,
  country_code CHAR(3)     NOT NULL CHECK (country_code ~ '^[A-Z]{3}$'),
  address      text,
  timezone     text        NOT NULL DEFAULT 'UTC',
  metadata     JSONB       NOT NULL DEFAULT '{}',
  created_at   timestamptz NOT NULL DEFAULT now()
);

-- pm.clients: custom domain (nullable), JSONB, UNIQUE name
CREATE TABLE pm.clients (
  id            UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  name          text         NOT NULL UNIQUE,
  industry      text,
  contact_email email_domain,
  website       text,
  notes         JSONB        NOT NULL DEFAULT '{}',
  created_at    timestamptz  NOT NULL DEFAULT now()
);

-- pm.tags: citext UNIQUE, default color
CREATE TABLE pm.tags (
  id         UUID    PRIMARY KEY DEFAULT gen_random_uuid(),
  name       CITEXT  NOT NULL UNIQUE,
  color      CHAR(7) NOT NULL DEFAULT '#000000',
  category   text,
  created_at timestamptz NOT NULL DEFAULT now()
);


-- ============================================================================
-- LEVEL 1 — Depend on L0 only
-- ============================================================================

-- org.departments: range CHECK, composite UNIQUE
CREATE TABLE org.departments (
  id         UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
  office_id  UUID          NOT NULL REFERENCES org.offices(id) ON DELETE CASCADE,
  name       text          NOT NULL,
  code       text          NOT NULL,
  budget     numeric(12,2) CHECK (budget >= 0 AND budget < 10000000),
  is_active  boolean       NOT NULL DEFAULT true,
  created_at timestamptz   NOT NULL DEFAULT now(),
  UNIQUE (office_id, code)
);

-- pm.projects: ENUM, generated tsvector, cross-column CHECK, JSONB
CREATE TABLE pm.projects (
  id            UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id     UUID           NOT NULL REFERENCES pm.clients(id) ON DELETE CASCADE,
  name          text           NOT NULL,
  status        project_status NOT NULL DEFAULT 'planning',
  priority      integer        NOT NULL CHECK (priority BETWEEN 1 AND 5),
  start_date    date           NOT NULL,
  end_date      date,
  description   text,
  settings      JSONB          NOT NULL DEFAULT '{}',
  search_vector tsvector       GENERATED ALWAYS AS (
    setweight(to_tsvector('simple', coalesce(name, '')), 'A') ||
    setweight(to_tsvector('simple', coalesce(description, '')), 'B')
  ) STORED,
  created_at    timestamptz    NOT NULL DEFAULT now(),
  updated_at    timestamptz    NOT NULL DEFAULT now(),
  CHECK (end_date IS NULL OR end_date >= start_date)
);
CREATE INDEX projects_search_idx ON pm.projects USING GIN (search_vector);
CREATE TRIGGER projects_updated_at
  BEFORE UPDATE ON pm.projects
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();


-- ============================================================================
-- LEVEL 2 — Depend on L0-L1
-- ============================================================================

-- org.employees: self-ref FK, generated column, partial unique index,
--   custom domain, ENUM, array type, soft delete, cross-column CHECK, trigger
CREATE TABLE org.employees (
  id               UUID            PRIMARY KEY DEFAULT gen_random_uuid(),
  department_id    UUID            NOT NULL REFERENCES org.departments(id) ON DELETE CASCADE,
  reports_to       UUID            REFERENCES org.employees(id),
  email            email_domain    NOT NULL,
  first_name       text            NOT NULL,
  last_name        text            NOT NULL,
  hire_date        date            NOT NULL,
  termination_date date,
  employment_type  employment_type NOT NULL,
  salary           numeric(10,2)   NOT NULL CHECK (salary > 0),
  skills           text[]          NOT NULL DEFAULT '{}',
  full_name        text            GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
  deleted_at       timestamptz,
  created_at       timestamptz     NOT NULL DEFAULT now(),
  updated_at       timestamptz     NOT NULL DEFAULT now(),
  CHECK (termination_date IS NULL OR termination_date > hire_date)
);
CREATE UNIQUE INDEX employees_email_uq ON org.employees (email) WHERE deleted_at IS NULL;
CREATE INDEX employees_reports_to_idx ON org.employees (reports_to);
CREATE TRIGGER employees_updated_at
  BEFORE UPDATE ON org.employees
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- pm.milestones: partitioned table, composite PK
CREATE TABLE pm.milestones (
  id           UUID NOT NULL DEFAULT gen_random_uuid(),
  project_id   UUID NOT NULL REFERENCES pm.projects(id) ON DELETE CASCADE,
  name         text NOT NULL,
  due_date     date NOT NULL,
  completed_at timestamptz,
  created_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id, due_date)
) PARTITION BY RANGE (due_date);

CREATE TABLE pm.milestones_2025 PARTITION OF pm.milestones
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE pm.milestones_2026 PARTITION OF pm.milestones
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE pm.milestones_default PARTITION OF pm.milestones DEFAULT;

-- pm.contracts: composite PK (both columns are FKs), ENUM, range CHECK, JSONB
CREATE TABLE pm.contracts (
  client_id     UUID          NOT NULL REFERENCES pm.clients(id) ON DELETE CASCADE,
  project_id    UUID          NOT NULL REFERENCES pm.projects(id) ON DELETE CASCADE,
  contract_type contract_type NOT NULL,
  hourly_rate   numeric(8,2)  CHECK (hourly_rate >= 0 AND hourly_rate < 1000),
  start_date    date          NOT NULL,
  end_date      date,
  terms         JSONB         NOT NULL DEFAULT '{}',
  signed_at     timestamptz,
  created_at    timestamptz   NOT NULL DEFAULT now(),
  PRIMARY KEY (client_id, project_id),
  CHECK (end_date IS NULL OR end_date > start_date)
);

-- pm.employee_profiles: 1:1 relationship (UNIQUE FK), cross-schema FK, JSONB
CREATE TABLE pm.employee_profiles (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  employee_id UUID        NOT NULL UNIQUE REFERENCES org.employees(id) ON DELETE CASCADE,
  bio         text,
  avatar_url  text,
  linkedin_url text,
  github_handle text,
  preferences JSONB       NOT NULL DEFAULT '{}',
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);
CREATE TRIGGER employee_profiles_updated_at
  BEFORE UPDATE ON pm.employee_profiles
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();


-- ============================================================================
-- LEVEL 3 — 3-table cycle (tasks ↔ sprints ↔ reviews)
-- ============================================================================
-- Created WITHOUT cycle FK constraints first. Cycle FKs added via ALTER TABLE
-- after all three tables exist.

-- pm.tasks: composite FK to milestones, cross-schema FK (DEFERRABLE),
--   ENUM, array type, nullable FKs
CREATE TABLE pm.tasks (
  id                 UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  milestone_id       UUID        NOT NULL,
  milestone_due_date date        NOT NULL,
  assignee_id        UUID        REFERENCES org.employees(id)
                                 DEFERRABLE INITIALLY DEFERRED,
  current_sprint_id  UUID,                           -- cycle FK, added later
  title              text        NOT NULL,
  description        text,
  status             task_status NOT NULL DEFAULT 'todo',
  priority           integer     NOT NULL CHECK (priority BETWEEN 1 AND 5),
  estimated_hours    numeric(6,1) CHECK (estimated_hours >= 0),
  tags               text[]      NOT NULL DEFAULT '{}',
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now(),
  FOREIGN KEY (milestone_id, milestone_due_date)
    REFERENCES pm.milestones(id, due_date) ON DELETE CASCADE
);
CREATE TRIGGER tasks_updated_at
  BEFORE UPDATE ON pm.tasks
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- pm.sprints: cross-column CHECK, composite UNIQUE
CREATE TABLE pm.sprints (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id      UUID        NOT NULL REFERENCES pm.projects(id) ON DELETE CASCADE,
  final_review_id UUID,                              -- cycle FK, added later
  name            text        NOT NULL,
  sprint_number   integer     NOT NULL,
  start_date      date        NOT NULL,
  end_date        date        NOT NULL,
  goal            text,
  velocity        integer     CHECK (velocity >= 0),
  created_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (project_id, sprint_number),
  CHECK (end_date > start_date)
);

-- pm.reviews: cross-schema FK, two ENUMs, range CHECK, JSONB
CREATE TABLE pm.reviews (
  id           UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id      UUID,                                  -- cycle FK, added later
  reviewer_id  UUID          NOT NULL REFERENCES org.employees(id),
  review_type  review_type   NOT NULL,
  status       review_status NOT NULL DEFAULT 'pending',
  body         text,
  rating       integer       CHECK (rating BETWEEN 1 AND 5),
  metadata     JSONB         NOT NULL DEFAULT '{}',
  submitted_at timestamptz,
  created_at   timestamptz   NOT NULL DEFAULT now()
);


-- === Add cycle FK constraints ===
ALTER TABLE pm.tasks
  ADD CONSTRAINT tasks_current_sprint_fk
  FOREIGN KEY (current_sprint_id) REFERENCES pm.sprints(id);

ALTER TABLE pm.sprints
  ADD CONSTRAINT sprints_final_review_fk
  FOREIGN KEY (final_review_id) REFERENCES pm.reviews(id);

ALTER TABLE pm.reviews
  ADD CONSTRAINT reviews_task_fk
  FOREIGN KEY (task_id) REFERENCES pm.tasks(id);


-- ============================================================================
-- LEVEL 4 — Depend on cycle tables
-- ============================================================================

-- pm.time_entries: cross-schema FK, range CHECK, multi-column UNIQUE
CREATE TABLE pm.time_entries (
  id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id     UUID         NOT NULL REFERENCES pm.tasks(id) ON DELETE CASCADE,
  employee_id UUID         NOT NULL REFERENCES org.employees(id),
  date        date         NOT NULL,
  hours       numeric(4,1) NOT NULL CHECK (hours > 0 AND hours <= 24),
  description text,
  billable    boolean      NOT NULL DEFAULT true,
  created_at  timestamptz  NOT NULL DEFAULT now(),
  UNIQUE (task_id, employee_id, date)
);

-- pm.task_tags: junction table with extra fields, composite PK, cross-schema FK
CREATE TABLE pm.task_tags (
  task_id   UUID        NOT NULL REFERENCES pm.tasks(id) ON DELETE CASCADE,
  tag_id    UUID        NOT NULL REFERENCES pm.tags(id) ON DELETE CASCADE,
  tagged_by UUID        NOT NULL REFERENCES org.employees(id),
  tagged_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (task_id, tag_id)
);


COMMIT;