One-Time Setup: Create Audit Results Table

Run this SQL once in your Supabase SQL Editor before first use. This creates the table with per-project tracking — same test ID can be verified separately for each LEED project.

CREATE TABLE IF NOT EXISTS leed_audit_results (
  id           BIGSERIAL PRIMARY KEY,
  project_id   UUID,
  test_id      TEXT NOT NULL,
  status       TEXT,
  evidence     TEXT,
  verified_by  TEXT,
  verified_at  TIMESTAMPTZ DEFAULT NOW(),
  updated_at   TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE NULLS NOT DISTINCT (project_id, test_id)
);

ALTER TABLE leed_audit_results ENABLE ROW LEVEL SECURITY;

CREATE POLICY "admin_manage_leed_audit" ON leed_audit_results
  FOR ALL TO anon
  USING (true) WITH CHECK (true);

CREATE INDEX IF NOT EXISTS idx_leed_audit_project ON leed_audit_results(project_id);
CREATE INDEX IF NOT EXISTS idx_leed_audit_test ON leed_audit_results(test_id);

Already created the v1 (single-project) table? Run this migration instead:

-- Migration from v1 (single-project) to v2 (per-project)
ALTER TABLE leed_audit_results
  ADD COLUMN IF NOT EXISTS id BIGSERIAL,
  ADD COLUMN IF NOT EXISTS project_id UUID;

-- Drop old primary key if it was test_id only
ALTER TABLE leed_audit_results DROP CONSTRAINT IF EXISTS leed_audit_results_pkey;
ALTER TABLE leed_audit_results ADD PRIMARY KEY (id);

-- Unique on (project_id, test_id) — treating NULL project_id as platform-wide
ALTER TABLE leed_audit_results
  ADD CONSTRAINT leed_audit_proj_test_uq UNIQUE NULLS NOT DISTINCT (project_id, test_id);

CREATE INDEX IF NOT EXISTS idx_leed_audit_project ON leed_audit_results(project_id);
CREATE INDEX IF NOT EXISTS idx_leed_audit_test ON leed_audit_results(test_id);