Database Schema — SV-Wiki

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)
│   ├── customers             # Denormalized Stripe mapping
│   └── webhook_events        # Webhook idempotency log
│
├── auth (Supabase-managed)
│   └── users                 # Authentication (magic links)
│
└── storage (Supabase-managed)
    └── buckets               # File storage (item-photos)

Current Status (Jan 2026)

Schema	Status	Notes
`public`	ACTIVE PRODUCTION	8 customers, 8 items, 5 bookings
`sv`	PLANNED CANONICAL	0 records - not yet migrated to
`billing`	ACTIVE	Stripe webhook log

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)

Column	Type	Description
`user_id`	UUID (PK, FK)	Links to auth.users
`email`	TEXT (UNIQUE)	Customer email
`stripe_customer_id`	TEXT (UNIQUE)	Stripe customer ID
`subscription_id`	TEXT	Active subscription ID
`subscription_status`	ENUM	inactive, active, past_due, canceled, trialing, etc.
`last_payment_at`	TIMESTAMPTZ	Latest successful payment
`full_name`	TEXT	Customer name
`phone`	TEXT	Contact phone
`delivery_address`	JSONB	{street, unit, city, state, zip}
`out_of_service_area`	BOOLEAN	Soft gate for out-of-area

Protected Columns

Users CANNOT update subscription_status, stripe_customer_id, subscription_id directly.

items

Purpose: Customer inventory with multi-photo support

Column	Type	Description
`id`	UUID (PK)	Item identifier
`user_id`	UUID (FK)	Owner
`label`	TEXT (NOT NULL)	Item name
`description`	TEXT (NOT NULL)	Item description
`category`	TEXT	Optional category
`status`	ENUM	home, in_transit, stored, scheduled
`photo_paths`	TEXT[]	Multi-photo array (1-5)
`qr_code`	TEXT (UNIQUE)	Format: SV-YYYY-NNNNNN
`cubic_feet`	NUMERIC (GENERATED)	Auto-calculated
`tags`	TEXT[]	Searchable keywords

actions

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

Column	Type	Description
`id`	UUID (PK)	Action identifier
`user_id`	UUID (FK)	Customer
`service_type`	ENUM	pickup, redelivery, container_delivery
`status`	ENUM	pending_items, pending_confirmation, confirmed, in_progress, completed, canceled
`pickup_item_ids`	UUID[]	Items to pick up
`delivery_item_ids`	UUID[]	Items to deliver
`calendly_event_uri`	TEXT (UNIQUE)	Calendly event identifier
`scheduled_start`	TIMESTAMPTZ	Calendly booking start

service_areas

Purpose: Serviceable ZIP codes

Column	Type	Description
`zip`	TEXT (PK)	ZIP code
`city`	TEXT	City name
`state`	TEXT	State code

Billing Tables

billing.customers

Purpose: Denormalized Stripe customer mapping

Column	Type	Description
`user_id`	UUID (PK, FK)	User identifier
`stripe_customer_id`	TEXT (UNIQUE)	Stripe customer ID

billing.webhook_events

Purpose: Stripe webhook idempotency log

Column	Type	Description
`id`	BIGSERIAL (PK)	Auto-increment ID
`event_id`	TEXT (UNIQUE)	Stripe event.id (idempotency key)
`event_type`	TEXT	e.g., checkout.session.completed
`payload`	JSONB	Full Stripe event object
`processed_at`	TIMESTAMPTZ	Processing completion

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
);

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
✅ Owner can INSERT actions (pending_items status only)
❌ Owner CANNOT update to confirmed/completed (ops only)

inventory_events / booking_events

✅ Owner can SELECT events for their items/bookings
❌ 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: 33 migrations applied (verified Jan 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
20260107220309 — Billing v2 trial columns

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

On This Page

Schema Overview

Current Status (Jan 2026)

Entity Relationship Diagram

Core Tables

customer_profile

Protected Columns

items

actions

service_areas

Billing Tables

billing.customers

billing.webhook_events

Database Functions

update_subscription_status()

log_booking_event()

RLS Policies

customer_profile

items

actions

inventory_events / booking_events

Common Queries

Get user items for dashboard

Search items

Get pending bookings

Migration History