Schema Reference

supabase_billing:install generates one migration that creates twelve tables: eight canonical tables that power the entitlement engine and four provider-mapping tables that translate Stripe and Adapty identifiers into canonical rows. Everything is UUID-keyed so primary keys line up with auth.users.id (a billing_customers.user_id is literally an auth.users.id), which is what makes the RLS policies one-liners.

ER diagram

users is the application's User model (created by supabase-rails); the rest are created by this gem. provider_events is a raw webhook log — it doesn't hold a foreign key to provider_subscriptions, but processing one usually creates or updates the other, which is what the dotted relationship in the diagram represents.

Canonical tables

The eight tables below are the source of truth for who is subscribed to what, which entitlements that grants them, and how much of any metered limit they have used.

billing_customers

One row per User who has ever had a billing relationship with any provider. Unique on user_id so each auth.users.id maps to exactly one billing customer.

Key columns: id uuid, user_id uuid (FK → users.id, unique), created_at, updated_at.

plans

The product catalog as your app sees it. Each row is one offering (free, pro, team_yearly), independent of any provider's price/product IDs.

Key columns: id uuid, key string (unique — what your code passes to the plan DSL), name string, description text, archived_at datetime (soft-archive when you stop offering a plan).

entitlements

The capability catalog — every feature, quota, or flag your app gates on. kind is "boolean" (capability flag) or "numeric" (quota / metered limit).

Key columns: id uuid, key string (unique — what entitled?(:key) and limit(:key) look up), kind string, description text.

plan_entitlements

The join table connecting plans to entitlements with a value. One row per (plan, entitlement) pair, unique on (plan_id, entitlement_id).

Key columns: id uuid, plan_id uuid (FK → plans), entitlement_id uuid (FK → entitlements), value_numeric bigint (set when kind = "numeric"), value_boolean boolean (set when kind = "boolean").

subscriptions

One row per active or historical subscription on a billing_customer. The current entitlement set is whichever subscription is currently status = "active" or "trialing".

Key columns: id uuid, billing_customer_id uuid (FK), plan_id uuid (FK), status string (active | trialing | past_due | cancelled | expired | refunded), source string (stripe | adapty), current_period_start, current_period_end, trial_end, cancelled_at.

subscription_items

Line items inside a subscription — necessary because Stripe subscriptions can have multiple prices (e.g. a base plan + a metered add-on).

Key columns: id uuid, subscription_id uuid (FK), provider_price_id string (mirrors Stripe's price ID for reconciliation), quantity integer (default 1).

usage_limits

The "remaining budget" snapshot for metered entitlements, scoped to one subscription and one billing period. Lets Current.user.remaining(:ai_requests) answer in O(1) instead of summing every usage_event since the start of the period.

Key columns: id uuid, subscription_id uuid (FK), entitlement_id uuid (FK), value_numeric bigint (cap for the period), period_start datetime, period_end datetime. Unique on (subscription_id, entitlement_id, period_start).

usage_events

The append-only log of metered consumption. record_usage(:key, amount:, recorded_at:) writes here; usage(:key) and remaining(:key) read here.

Key columns: id uuid, billing_customer_id uuid (FK), entitlement_id uuid (FK), amount bigint (positive consumed, negative for credits / refunds), recorded_at datetime.

Provider-mapping tables

The four tables below are the only place provider-specific IDs live. Every webhook handler writes through them so the rest of the schema stays vendor-agnostic.

provider_customers

Maps billing_customers to the provider's customer identifier (Stripe cus_…, Adapty profile_id). Unique on (provider, provider_customer_id).

Key columns: id uuid, billing_customer_id uuid (FK), provider string (stripe | adapty), provider_customer_id string.

provider_subscriptions

Maps subscriptions to the provider's subscription identifier (Stripe sub_…, Adapty access-level grant). subscription_id is nullable so we can record a provider subscription that hasn't been linked to a canonical row yet. raw_data jsonb keeps the full last-seen payload for debugging.

Key columns: id uuid, subscription_id uuid (FK, nullable), provider string, provider_subscription_id string, raw_status string, raw_data jsonb. Unique on (provider, provider_subscription_id).

provider_products

Maps plans to provider price/product IDs (Stripe price_… / prod_…, Adapty product IDs). One plan can have many provider products — that's how the same pro plan can be sold via three different Stripe prices and one Adapty product.

Key columns: id uuid, plan_id uuid (FK), provider string, provider_product_id string. Unique on (provider, provider_product_id).

provider_events

Raw webhook log — every event a provider sent us, even ones we ignored. Idempotency comes from the unique index on (provider, provider_event_id): a retried Stripe webhook is recognised and short-circuited before any side-effects run.

Key columns: id uuid, provider string, provider_event_id string, event_type string, payload jsonb, received_at datetime, processed_at datetime (NULL until processed; non-NULL guarantees we won't re-process).

RLS policies

The install migration enables RLS on the three user-owned tables and creates a single SELECT policy on each:

Writes are not RLS-gated because they happen from the Rails side (webhook controllers, the install generator, and bin/rails supabase_billing:sync) using the service role connection. The anon key cannot insert or update billing rows.

If you want to opt out — for example because you're not using the Supabase JS SDK against this database — pass --skip-rls to the install generator and the ENABLE ROW LEVEL SECURITY block is omitted from the migration.

Indexes

The migration creates the indexes that the entitlement engine and webhook adapters actually use:

• billing_customers (user_id) — unique. One billing customer per user.
• plans (key) — unique. Plan DSL lookups.
• entitlements (key) — unique. entitled? / limit lookups.
• plan_entitlements (plan_id, entitlement_id) — unique. One value per pair.
• subscriptions (billing_customer_id, status) — finding the active subscription for a user.
• usage_limits (subscription_id, entitlement_id, period_start) — unique. One budget row per period.
• usage_events (billing_customer_id, entitlement_id, recorded_at) — usage rollups.
• provider_customers (provider, provider_customer_id) — unique. Webhook dispatch.
• provider_subscriptions (provider, provider_subscription_id) — unique. Webhook dispatch.
• provider_products (provider, provider_product_id) — unique. Webhook dispatch.
• provider_events (provider, provider_event_id) — unique. Idempotency.
• provider_events (provider, processed_at) — finding un-processed events for retry.

Where this comes from

The full migration lives at db/migrate/<timestamp>_create_supabase_billing_schema.rb after you run the install generator, mirroring lib/generators/supabase_billing/install/templates/db/migrate/create_supabase_billing_schema.rb.tt in the gem. The companion Billing::* model files under app/models/billing/ declare the ActiveRecord associations the diagram above is built from.