Sprint 1A — Supabase Production Schema

Production Supabase Claim Registry Schema — 1 table, 1 rate-limit table, 1 fencing-token sequence, 9 claim RPC functions. This file is ready to run in the Supabase SQL Editor.

Download SQL File

sprint-1a-supabase-production-schema-v1.0.sql

Run the entire file once in your Supabase project's SQL Editor

Deployment Instructions

1Open your Supabase project dashboard at supabase.com
2Navigate to SQL Editor in the left sidebar
3Click New query, then paste the entire contents of this SQL file
4Click Run — the entire file executes in one transaction. All statements use IF NOT EXISTS or CREATE OR REPLACE, so it is safe to run exactly once.
5Verify deployment by running the verification query at the bottom of the file — it should return 9 rows (one per RPC function).

What This Creates

claim_request_registry

1 registry table — source of truth for all claim concurrency-control fields

claim_rate_limit

1 rate-limit table for operation-specific throttling

claim_fencing_token_seq

1 monotonic fencing-token sequence

9 RPC Functions

reserve, revoke, code-gen, verify, fail-attempt, rate-limit, acquire, read, expire

SQL Preview (first 40 lines)

769 lines total

-- ============================================================================
-- Sprint 1A — Production Supabase Claim Registry Schema v1.0
-- 
-- Deploys: 1 claim registry table, 1 rate-limit table, 1 fencing-token sequence,
--          and 9 claim RPC functions (IC-100 v1.5 §22, AC-100 v1.0 §8A).
--
-- Conversion tables and RPCs are NOT included — they are deferred to Sprint 1B.
--
-- Governance: AC-100 v1.0 (FROZEN), IC-100 v1.5 (FROZEN), Schema Plan v1.4 (FROZEN)
-- ============================================================================

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

-- ============================================================================
-- TABLE: claim_request_registry
-- 
-- Source of truth for all claim concurrency-control fields.
-- One row per ClaimRequest. source_session_id is UNIQUE — one active
-- ClaimRequest per anonymous session (IC-100 §22, Schema Plan v1.4 §7).
-- ============================================================================
CREATE TABLE IF NOT EXISTS claim_request_registry (
    -- Identity and linkage
    claim_request_id              TEXT PRIMARY KEY,
    source_session_id             TEXT NOT NULL UNIQUE,
    claimant_user_id              TEXT,
    claim_email_normalized        TEXT NOT NULL,

    -- State and lifetime (IC-100 §26 state machine)
    claim_status                  TEXT NOT NULL DEFAULT 'pending_authentication',
    expires_at                    TIMESTAMPTZ NOT NULL,
    created_at                    TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at                    TIMESTAMPTZ NOT NULL DEFAULT now(),

    -- Authentication continuity
    authenticated_user_id         TEXT,
    authenticated_email_normalized TEXT,
    authenticated_email_matched_at TIMESTAMPTZ,
    continuation_verified_at      TIMESTAMPTZ,