Overview

aSaaSin uses Supabase (Postgres + RLS). The schema is minimal, practical, and built for real SaaS flows - profiles, plans, subscriptions, projects, API tokens, roadmap voting, and key/value config.

Relationships

auth.users ───┐
              ├─ public.profiles (1:1 via id, created by trigger on auth.users insert)
              ├─ public.subscriptions (1:1 user_id)
              ├─ public.projects (1:N user_id)
              └─ public.api_tokens (1:N user_id)

public.subscription_plans (1:N) ── public.subscriptions (subscription_plan_id)

public.roadmap_votes (standalone per slug)
public.config        (key/value)

Security model

  • RLS is enabled on all user-owned tables.
  • Users can only read/update their own rows using auth.uid().
  • subscription_plans is public read-only; writes are disallowed.
  • Profile rows are created via handle_new_user trigger on auth.users.
  • Voting uses SECURITY DEFINER functions with tight scope.
  • Service role keys are server-only (webhooks, admin tasks).

Do not expose the service role key to the client. Keep write operations that cross user boundaries on the server (e.g. webhooks).

Core tables

profiles

  • id matches auth.users.id.
  • Policies: public select; users can insert/update their own row.
  • Trigger: inserts a profile on new auth.users record.

subscription_plans

  • Unique on (name, billing_cycle).
  • features (JSONB) holds capability flags, e.g. maxApiTokens, maxProjects.
  • Public read; no writes via RLS.
  • Stores Polar product_id to map external products to internal plans.

subscriptions

  • One active row per user (user_id unique).
  • References subscription_plans.
  • Stores Polar identifiers and status fields: customer_id, subscription_id, status, current_period_start, current_period_end, canceled_at, timestamps.
  • Policies: user can read/update own row. Written by the webhook.

projects

  • status: draft | active | completed.
  • Full CRUD restricted to the owner.

api_tokens

  • Per-user tokens; consider hashing the private token and showing once.
  • Owner-only read/insert/delete.

roadmap_votes

  • Per slug counters, updated via increment_vote(slug) / decrement_vote(slug).
  • Readable by anyone; updates allowed for counting.

config

  • Simple key/value (e.g. maintenance mode toggles).

Plans & subscription mapping

Plans are defined in subscription_plans with features JSONB for capability flags. Each plan stores Polar’s product_id, which the webhook uses to resolve the internal subscription_plan_id when processing subscription events.

Keep (name, billing_cycle) unique. Customize pricing and store product_id per plan. Extend features as your product evolves.

Local development

You can run the full database locally using the Supabase CLI:

  1. Install the CLI and start services: supabase start
  2. Set env vars in .env.local:
    • NEXT_PUBLIC_SUPABASE_URL
    • NEXT_PUBLIC_SUPABASE_ANON_KEY
    • SUPABASE_SERVICE_ROLE_KEY (server-only; used in API routes/webhooks)
  3. Apply schema (migrations/SQL): run your SQL in the Supabase SQL editor or re-seed with supabase db reset.
  4. (Optional) Seed plans: insert free, plus, pro for both billing cycles and set Polar product_id if the products already exist.

Never commit real service role keys. Use separate keys for local, staging, and production.

Migrations & environments

  1. Create a migration
    • Empty file: supabase migration new <name> then add SQL
    • From changes: supabase db diff -f <name>.sql
  2. Apply & test locally
    • supabase db reset (rebuilds from migrations and runs seeds)
  3. Push to a remote project
    • Link: supabase link --project-ref <ref>
    • Deploy: supabase db push (use --dry-run first)
  4. Pull remote schema
    • supabase db pull to capture remote changes into a migration
  5. Recommended flow
    • Branch → changes → db diff → db reset → commit → push/deploy