supabase_schema.sql

-- ─────────────────────────────────────────────────────────────────────────────
-- LabCard AI — Supabase Schema
-- Run this in Supabase SQL Editor: https://supabase.com/dashboard/project/_/sql
-- ─────────────────────────────────────────────────────────────────────────────

-- ── User Profiles ─────────────────────────────────────────────────────────────
-- Extends auth.users — created automatically on first report / payment

CREATE TABLE IF NOT EXISTS public.user_profiles (
  id                   UUID         REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  tier                 TEXT         NOT NULL DEFAULT 'free'
                                    CHECK (tier IN ('free', 'premium')),
  reports_analyzed     INTEGER      NOT NULL DEFAULT 0,
  premium_expires_at   TIMESTAMPTZ,
  created_at           TIMESTAMPTZ  DEFAULT NOW(),
  updated_at           TIMESTAMPTZ  DEFAULT NOW()
);

-- Auto-update updated_at on any change
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_profiles_updated_at
  BEFORE UPDATE ON public.user_profiles
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ── Reports ───────────────────────────────────────────────────────────────────
-- Full LabReport JSON stored in report_json JSONB for future trend analysis.
-- Summary columns (score, grade, etc.) allow fast SQL aggregates without
-- parsing the full JSON every time.

CREATE TABLE IF NOT EXISTS public.reports (
  id                   UUID         DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id              UUID         REFERENCES auth.users(id) ON DELETE SET NULL,
  -- Extracted patient fields (denormalized for fast queries)
  patient_name         TEXT,
  patient_age          TEXT,
  patient_gender       TEXT,
  lab_name             TEXT,
  report_date          TEXT,
  -- Health metrics (all deterministic — not from AI)
  health_score         INTEGER      CHECK (health_score >= 0 AND health_score <= 100),
  health_grade         TEXT,
  biological_age       INTEGER,
  chronological_age    INTEGER,
  has_critical_alert   BOOLEAN      DEFAULT FALSE,
  -- Processing metadata
  tier_used            TEXT         DEFAULT 'free',
  biomarker_count      INTEGER,
  abnormal_count       INTEGER,
  processing_time_ms   INTEGER,
  -- Full report blob (enables trend queries + future re-analysis)
  report_json          JSONB        NOT NULL,
  created_at           TIMESTAMPTZ  DEFAULT NOW()
);

-- Indexes for common access patterns
CREATE INDEX IF NOT EXISTS idx_reports_user_id
  ON public.reports(user_id);

CREATE INDEX IF NOT EXISTS idx_reports_created_at
  ON public.reports(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_reports_health_score
  ON public.reports(health_score);

-- GIN index on JSONB for fast biomarker trend queries
CREATE INDEX IF NOT EXISTS idx_reports_json
  ON public.reports USING GIN (report_json);

-- ── Payments ──────────────────────────────────────────────────────────────────
-- Audit trail for all Razorpay transactions.
-- razorpay_order_id is UNIQUE — idempotent upsert is safe.

CREATE TABLE IF NOT EXISTS public.payments (
  id                   UUID         DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id              UUID         REFERENCES auth.users(id) ON DELETE SET NULL,
  razorpay_order_id    TEXT         UNIQUE NOT NULL,
  razorpay_payment_id  TEXT         UNIQUE,
  razorpay_signature   TEXT,
  plan                 TEXT         NOT NULL
                                    CHECK (plan IN ('per_report', 'monthly')),
  amount_paise         INTEGER      NOT NULL,
  status               TEXT         NOT NULL DEFAULT 'pending'
                                    CHECK (status IN ('pending', 'success', 'failed')),
  verified_at          TIMESTAMPTZ,
  created_at           TIMESTAMPTZ  DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_payments_user_id
  ON public.payments(user_id);

-- ── Row Level Security ────────────────────────────────────────────────────────
-- Users can only read their own data.
-- Backend uses service role key — bypasses RLS for writes.

ALTER TABLE public.user_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.reports       ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.payments      ENABLE ROW LEVEL SECURITY;

-- user_profiles: users see and update only their own row
CREATE POLICY "Users see own profile"
  ON public.user_profiles FOR ALL
  USING (auth.uid() = id);

-- reports: users can read their own reports
CREATE POLICY "Users see own reports"
  ON public.reports FOR SELECT
  USING (auth.uid() = user_id);

-- reports: backend service role inserts (WITH CHECK true allows service key)
CREATE POLICY "Service role inserts reports"
  ON public.reports FOR INSERT
  WITH CHECK (true);

-- payments: users can only see their own payment records
CREATE POLICY "Users see own payments"
  ON public.payments FOR SELECT
  USING (auth.uid() = user_id);

-- payments: backend service role inserts
CREATE POLICY "Service role inserts payments"
  ON public.payments FOR INSERT
  WITH CHECK (true);

-- ── Helper: increment reports_analyzed counter ────────────────────────────────
-- Called via trigger when a new report is inserted

CREATE OR REPLACE FUNCTION increment_reports_analyzed()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.user_id IS NOT NULL THEN
    INSERT INTO public.user_profiles (id, reports_analyzed)
      VALUES (NEW.user_id, 1)
      ON CONFLICT (id) DO UPDATE
        SET reports_analyzed = user_profiles.reports_analyzed + 1,
            updated_at = NOW();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_report_inserted
  AFTER INSERT ON public.reports
  FOR EACH ROW EXECUTE FUNCTION increment_reports_analyzed();

-- ── Trend analysis view (bonus — useful for future dashboard) ─────────────────
-- Returns latest health score per user for trend charts

CREATE OR REPLACE VIEW public.user_health_trends AS
SELECT
  r.user_id,
  r.id           AS report_id,
  r.created_at,
  r.health_score,
  r.health_grade,
  r.biological_age,
  r.chronological_age,
  r.abnormal_count,
  r.lab_name
FROM public.reports r
WHERE r.user_id IS NOT NULL
ORDER BY r.user_id, r.created_at DESC;

-- ── Verify setup ──────────────────────────────────────────────────────────────
SELECT
  schemaname,
  tablename,
  tableowner
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename IN ('user_profiles', 'reports', 'payments');