Database Schema

Schema Overview

Supabase Database
├── public (legacy customer-facing - being phased out)
│   ├── customer_profile      # User accounts + subscription
│   ├── items                 # Inventory management
│   ├── actions               # Service requests (pickup/delivery)
│   ├── inventory_events      # Item movement audit trail
│   ├── booking_events        # Booking lifecycle audit trail
│   ├── claims                # Insurance claims
│   └── service_areas         # Serviceable ZIP codes
│
├── sv (canonical domain schema - primary)
│   ├── customer_profile      # Canonical customer data
│   ├── items                 # Canonical inventory
│   ├── actions               # Service requests
│   ├── inventory_events      # Item movement audit
│   ├── ops_tasks             # Operations tasks
│   ├── item_embeddings       # Vector embeddings for search
│   ├── staff                 # Staff access control
│   ├── container_catalog     # Container types
│   ├── referrals             # Referral program
│   ├── account_ledger        # Financial transactions
│   ├── pre_customers         # Pre-signup leads
│   ├── signup_anomalies      # Error tracking
│   └── webhook_events        # Webhook idempotency
│
├── billing (Stripe integration)
│   └── webhook_events        # Webhook idempotency log
│
├── auth (Supabase-managed)
│   └── users                 # Authentication (magic links)
│
└── storage (Supabase-managed)
    └── buckets               # File storage (item-photos)

Current Status (Jul 2026)

Migrations: 96 applied to production (head 20260827002000_plans_trial_days_default_30), local/remote in lockstep, verified Sep 11, 2026. pgTAP suite runs locally only — the pgTAP extension is not installed on prod.

Custody predicate (F13, Jul 2026): fn_v2_item_in_custody is the single shared definition of “in Storage Valet’s hands” (stored, or scheduled with scheduled_kind = 'delivery'). Both the capacity meter and the insurance-coverage meter derive from it — any new meter or disposition feature must reuse it, never re-implement the filter.

Coverage override & four-reader contract (F13.1, Jul 15, 2026): customer_profile.insurance_cap_cents_override lets ops raise a member’s coverage cap above the plan default; the effective cap is always coalesce(override, plans.insurance_cap_cents) (customer UPDATE revoked, all overrides NULL today). The cap is resolved in exactly four places — v_v2_insurance_coverage, fn_v2_read_meter_preview, sv.fn_v2_meter_thresholds_check, fn_v2_ops_booking_detail — and a cap change must update all four together. Threshold crossings (plans.meter_warn_threshold_pct, 80%) persist to meter_threshold_state and log an inventory_events row (meter_threshold_crossed). customer_profile.is_internal flags internal accounts; member/MRR aggregates filter is_internal = false at the read-model.

Ops missing-dimensions counts (F1, Jul 17, 2026): fn_v2_ops_booking_detail returns two distinct measurement-gap counts. items_missing_dims is manifest-scoped — the active manifest items on this booking whose cubic_feet is null; it is the visit work-prompt telling ops exactly what to measure at the appointment. account_items_missing_dims is an additive, account-wide count of every unmeasured item the member owns — the backfill signal, independent of any single visit. Migration 20260717170000.

Scheduled-in space preview (F4, Jul 19, 2026): v_v2_insurance_coverage grew append-only from 14 to 16 columns — scheduled_in_cubic_feet (known cubic feet of pickup-scheduled items; unmeasured items contribute nothing) and scheduled_in_has_unmeasured (true when any pickup-scheduled item has no dimensions yet — the dominant case, since ops measures at pickup). Cap logic is untouched; the four-reader contract is unaffected. Migration 20260719210000_f4_coverage_scheduled_in.

Entity Relationship Diagram

┌──────────────────────┐
│     auth.users       │ (Supabase-managed)
│  ─────────────────   │
│  id (PK)             │
│  email               │
└──────────┬───────────┘
           │ 1:1
           ▼
┌──────────────────────────────────────────────────┐
│         public.customer_profile                  │
│  ─────────────────────────────────────────────  │
│  user_id (PK, FK → auth.users)                  │
│  email, stripe_customer_id, subscription_id     │
│  subscription_status, last_payment_at           │
│  full_name, phone, delivery_address (jsonb)     │
│  out_of_service_area, needs_manual_refund       │
└───────────┬──────────────────────────────────────┘
            │ 1:N
            ▼
┌────────────────────────────────────────────┐
│          public.items                      │
│  ───────────────────────────────────────  │
│  id (PK), user_id (FK)                     │
│  label, description, category              │
│  status (home|in_transit|stored|scheduled) │
│  photo_paths[], qr_code (UNIQUE)           │
│  weight_lbs, dimensions, cubic_feet        │
│  physical_locked_at, tags[]                │
└────────┬──────────────────┬────────────────┘
         │ 1:N              │ M:N (via arrays)
         ▼                  ▼
┌────────────────────┐   ┌──────────────────────────┐
│ inventory_events   │   │    public.actions        │
│  ───────────────   │   │  ─────────────────────  │
│  id, item_id (FK)  │   │  id, user_id (FK)        │
│  event_type        │   │  service_type, status    │
│  event_data        │   │  pickup_item_ids[]       │
│  created_at        │   │  delivery_item_ids[]     │
└────────────────────┘   │  calendly_event_uri      │
                         │  scheduled_start/end     │
                         └─────────┬────────────────┘
                                   │ 1:N
                                   ▼
                         ┌──────────────────────┐
                         │   booking_events     │
                         │  ─────────────────── │
                         │  id, action_id (FK)  │
                         │  event_type          │
                         │  metadata, created_at│
                         └──────────────────────┘

Core Tables

customer_profile

Purpose: User account and subscription data (1:1 with auth.users)

items

Purpose: Customer inventory with multi-photo support

actions

Purpose: Service requests (pickup, redelivery, container delivery)

service_areas

Purpose: Serviceable ZIP codes

Billing Tables

billing.webhook_events

Purpose: Stripe webhook idempotency log

Database Functions

update_subscription_status()

Purpose: Update billing-protected columns from webhooks
Type: SECURITY DEFINER (bypasses RLS)

SELECT update_subscription_status(
  p_user_id := 'user-uuid',
  p_status := 'active',
  p_subscription_id := 'sub_123',
  p_last_payment_at := now()
);

log_booking_event()

Purpose: Insert booking events from edge functions
Type: SECURITY DEFINER

SELECT log_booking_event(
  p_action_id := 'action-uuid',
  p_event_type := 'calendly_webhook',
  p_metadata := '{"event_uri": "..."}'::jsonb
);

fn_v2_assert_entitled() — v2 Entitlement Gate (Jun 10, 2026)

Purpose: Single entitlement authority for the v2 RPCs. Migration 20260610061949_v2_entitlement_gate adds a server-side membership assertion to the two RPCs that grow stored inventory.
Type: SECURITY DEFINER

• Gated (members only): fn_v2_item_create, and fn_v2_booking_attach_items only when the call adds pickup items
• Never gated: login, reads, delivery-only bookings, cancel/revert, reschedule, confirm of an existing commitment — customers can always recover their inventory
• Entitled = subscription_status IN ('active','trialing','past_due'); otherwise raises SV_NOT_ENTITLED (errcode P0001)
• Staff bypass: sv.staff identities (ops/QA) are exempt

RLS Policies

Staff access requires a second factor (Jul 20, 2026)

Four security migrations shipped Jul 20, 2026 and are applied in production:

• 20260720100000_revoke_client_dml_public_sv — closed the client-write bypass. anon/authenticated now hold only SELECT, REFERENCES, TRIGGER on public; every write goes through the fn_v2_* SECURITY DEFINER RPCs.
• 20260720100001_neutralize_agent_role — contained an unaccounted login credential: agent_role is now NOLOGIN with grants, schema USAGE and the 24 ALTER DEFAULT PRIVILEGES entries all removed. The default-privilege clearance matters — revoking current grants alone would have re-granted on the next CREATE TABLE.
• 20260720100002_backfill_hollow_ledger_entries — data backfill.
• 20260720110000_aal2_staff_rls — staff RLS now requires AAL2. sv.is_staff() = membership and an MFA-stepped-up session; all policies referencing it inherit the requirement. Before this, an sv.staff row exposed every customer's bookings, service_address, booking_events and booking_items for reading with no second factor.

Do not gate public.is_staff() on AAL2. It is the identity predicate — the portal calls it to decide whether to show the ops route and offer MFA step-up, and it renders the 404 surface when it returns false. Gating it would 404 a staff member at AAL1, hide the Verify button, and permanently lock out the only staff account. It deliberately no longer delegates to sv.is_staff(); both functions carry a COMMENT ON FUNCTION recording this.

Local/prod grant parity is enforced by supabase/seed.sql, which now fails the reset if a client DML grant reappears. Any grant-changing migration must regenerate the seed in the same commit — otherwise a local rebuild silently reopens what the migration closed.

customer_profile

• ✅ Owner can SELECT own profile
• ✅ Owner can UPDATE editable fields (name, phone, address)
• ❌ Owner CANNOT update billing fields

items

• ✅ Owner can SELECT/INSERT/UPDATE/DELETE own items

actions

• ✅ Owner can SELECT own actions (or staff can view all)
• ✅ Owner can INSERT actions
• ✅ Owner can UPDATE/DELETE own actions in pending, pending_items, or pending_confirmation status only
• ✅ Staff can UPDATE/DELETE any action regardless of status
• ❌ Owner CANNOT update to confirmed/completed (ops only)

service_areas

• ✅ RLS enabled — authenticated users can SELECT (lookup table)
• ❌ No INSERT/UPDATE/DELETE for users

sv.staff

• ✅ RLS enabled — service_role only (no direct user access)

inventory_events

• ✅ Owner can SELECT events for their items
• ❌ Owner CANNOT insert (system-only via service_role)

booking_events

• ✅ Owner can SELECT events for their own bookings (via action ownership JOIN)
• ✅ Staff can SELECT all booking events
• ❌ Orphan events (action_id IS NULL) are only visible to staff
• ❌ Owner CANNOT insert (system-only via service_role)

Common Queries

Get user items for dashboard

SELECT * FROM items
WHERE user_id = auth.uid()
ORDER BY created_at DESC;

Search items

SELECT * FROM items
WHERE user_id = auth.uid()
  AND status = 'stored'
  AND (
    label ILIKE '%keyword%'
    OR description ILIKE '%keyword%'
    OR 'keyword' = ANY(tags)
  );

Get pending bookings

SELECT * FROM actions
WHERE user_id = auth.uid()
  AND status IN ('pending_items', 'pending_confirmation')
ORDER BY scheduled_start ASC;

Migration History

Total: 96 migrations applied (repo↔prod in lockstep, head 20260827002000_plans_trial_days_default_30, which sets the default trial to 30 days per DEC-023. Verified Sep 11, 2026.)

Key migrations include:

• 0001_init — Core schema
• 0004_phase1_inventory_enhancements — Multi-photo, status, batch ops
• 0005_create_sv_schema — Create sv domain schema
• 20251112000005_billing_status_tracking — Payment timestamps
• 20251201062222_create_service_areas_table — Service areas
• 20260107000001 — Billing v2 trial columns
• 20260129000001 — Enable RLS on service_areas
• 20260202000001 — fn_revert_orphaned_scheduled guardrail (dropped Jun 29, 2026 by DEC-021; the v2 equivalent is fn_v2_reconcile)
• 20260202000002 — Deprecate billing.customers
• 20260204000001–05 — v1 ops dashboard: sv.v_ops_actions view, get_ops_actions RPC, staff RLS hardening (view and RPC dropped Jun 29, 2026 by migration 20260629120000, DEC-021)
• 20260211000001 — Track A: Harden actions UPDATE/DELETE RLS (restore status constraint), fix booking_events cross-tenant leak
• 20260211000002 — Track A: Capture 3 missing RPCs in migration history (check_stripe_webhook_event, insert_stripe_webhook_event, get_user_id_by_email)
• 20260211000003 — Track A: is_valid_zip_code() STABLE table-backed function
• 20260211000004 — Track A: Drop stale booking_events_read_policy (P0 leak closure) and duplicate p_actions_owner_insert
• 20260215000001 — Drop duplicate update_subscription_status(subscription_status_enum) overload (P0 stripe-webhook fix)
• 20260227000001 — Drop deprecated upsert_billing_customer function (legacy helper for billing.customers, no longer referenced)
• 20260304000001 — Drop deprecated billing.customers table (redundant with customer_profile.stripe_customer_id)
• 20260526223235 — Harden security advisors (pin search_path, revoke EXECUTE from PUBLIC)
• 20260529130001–200001 — v2 backend train (applied prod Jun 9): additive schema, plans seed, fn_v2_* RPC suite, Stripe helpers, v_v2_* read-model views, fn_v2_item_revert, reconcile cron, realtime publication
• 20260531142114 — Enable RLS on billing.webhook_events
• 20260610040905 — customer_profile grant lockdown (column-scoped UPDATE; billing fields locked)
• 20260610041336 — Insurance possession-split (insured-now / scheduled-in)
• 20260610061949 — Server-side entitlement gate (fn_v2_assert_entitled; pickups/creates gated, deliveries never)
• 20260610215009 — Operational-minimum data-capture floors + coherence-constraint VALIDATE
• 20260612233000 — P0: release_stripe_webhook_event_v2 (claim release on webhook failure)
• 20260614120000 — B3: current_period_end column + next_billing_label derivation in v_v2_membership

Pending Migrations

No pending migrations.

Full migration list: ~/code/sv-db/supabase/migrations/