BEGIN;

SET ROLE work_role;

CREATE TABLE IF NOT EXISTS work.extension_proposals (
  proposal_id TEXT PRIMARY KEY,
  requested_by_principal_id TEXT NOT NULL,
  stage TEXT NOT NULL,
  evidence_refs TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  deduplicated_signal_keys TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  desired_outcome TEXT NOT NULL,
  smallest_change_layer TEXT NOT NULL,
  affected_owners TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  affected_contracts TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  risk_tier TEXT NOT NULL,
  evaluation_plan TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  alternatives TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  rollback_plan TEXT NOT NULL,
  assumptions TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  created_at_unix_millis BIGINT NOT NULL,
  CHECK (proposal_id <> ''), CHECK (requested_by_principal_id <> ''), CHECK (stage = 'PROPOSED'), CHECK (desired_outcome <> ''), CHECK (smallest_change_layer <> ''), CHECK (risk_tier <> ''), CHECK (rollback_plan <> ''), CHECK (created_at_unix_millis > 0)
);

CREATE TABLE IF NOT EXISTS work.development_scaffolds (
  scaffold_id TEXT PRIMARY KEY,
  requested_by_principal_id TEXT NOT NULL,
  stage TEXT NOT NULL,
  proposal_ref TEXT NOT NULL,
  base_repo_revision TEXT NOT NULL,
  worktree_ref TEXT NOT NULL,
  task_intent TEXT NOT NULL,
  modules TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  allowed_paths TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  forbidden_paths TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  required_tests TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  artifact_lineage_refs TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  protected_main_access TEXT NOT NULL,
  production_access TEXT NOT NULL,
  secret_access TEXT NOT NULL,
  created_at_unix_millis BIGINT NOT NULL,
  CHECK (scaffold_id <> ''), CHECK (requested_by_principal_id <> ''), CHECK (stage = 'SANDBOXED'), CHECK (proposal_ref <> ''), CHECK (base_repo_revision <> ''), CHECK (worktree_ref <> ''), CHECK (task_intent <> ''), CHECK (protected_main_access = 'DENIED'), CHECK (production_access = 'DENIED'), CHECK (secret_access = 'DENIED'), CHECK (created_at_unix_millis > 0)
);

RESET ROLE;
COMMIT;