Schema & Tables

The eight tables and the credit, claim and trigger functions the migrations create.

Tables

Seven numbered migrations in lib/supabase/migrations create these (006 changes defaults rather than adding tables). Every table has row-level security on.

public.users              # one row per Supabase auth user
  auth_user_id, email, full_name, avatar_url,
  stripe_customer_id, price_id, has_access, refunded_at

public.usage              # credits, one row per user
  credits_remaining (default 20 — the trial), credits_used, last_reset_at

public.subscriptions      # synced from Stripe
  stripe_subscription_id, status, current_period_start,
  current_period_end, cancel_at_period_end

public.pending_purchases  # paid before sign-in, claimed on first login
  stripe_customer_id, stripe_session_id, email, price_id, mode,
  status (pending | claimed), claimed_by_user_id, refunded_at

public.email_log          # lifecycle emails sent, so none go twice
public.trial_spend        # free-trial credits spent per day, for the daily cap

public.ai_conversations   # AIChat history
public.ai_generations     # ContentWriter history
public.ai_images          # ImageStudio gallery

Functions

- consume_credit(user_id, amount): atomic decrement, fails if insufficient - refund_credit(user_id, amount): returns a credit after a failed provider call - claim_pending_purchases(user_id, email): links paid sessions to a new user - handle_new_user, handle_new_app_user: triggers that create the users and usage rows on sign-up - auto_claim_on_user_insert, auto_claim_on_purchase_insert: claim in whichever order the webhook and sign-in arrive - consume_trial_budget(amount, cap): records free-trial spend for today, refuses past the cap - whirlwind_schema_version(): the newest template migration applied; doctor reads it The credit, claim and budget functions can only be called with the service-role key (migration 007). The public anon key cannot reach them, so a signed-in user cannot top up their own balance.

💡

npm run doctor probes every table and every function, so a half-applied migration is reported rather than discovered in production.