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 (Feb 2026)

The sv schema represents the target architecture. Migration from public to sv is planned post-launch.

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_revert_orphaned_scheduled()

Purpose: Detect and revert items stuck in scheduled state with no active booking. Called on dashboard load as a safety guardrail.
Type: SECURITY DEFINER

• Finds items where status='scheduled' with no active action referencing them
• Reverts pickup orphans to home, delivery orphans to stored
• Logs each reversion to inventory_events with type orphaned_scheduled_reverted
• Returns JSON summary for frontend toast notification
• Items with no action history are flagged as unknown (not mutated)

get_ops_actions()

Purpose: Staff-only RPC for the Ops Dashboard. Returns actions joined with customer profile data.
Type: SECURITY DEFINER, gated by sv.is_staff()

• Wraps sv.v_ops_actions view (cross-schema join of public.actions + sv.customer_profile)
• Only accessible to authenticated staff members

RLS Policies

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: 44 migrations applied (verified Mar 13, 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
• 20260202000002 — Deprecate billing.customers
• 20260204000001–05 — Ops dashboard: sv.v_ops_actions view, get_ops_actions RPC, staff RLS hardening
• 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)

Pending Migrations

No pending migrations.

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