Database & data models

ShipVeryFast persists data in Supabase (PostgreSQL) and describes every row shape with a small set of typed Zod models. This page covers the two Supabase clients, the models in models/, the tables the app expects, the SQL migration that creates them, and the throwaway Postgres container you run for integration tests.

Supabase clients: anon vs admin

Both clients live in libs/supabase.ts and are built from the Zod-validated env in libs/config.ts. There are exactly two exports, pick by where the code runs:

// libs/supabase.ts
import { createClient } from '@supabase/supabase-js';
import { env } from './config';

// Public client for queries from the frontend (respects Row Level Security)
export const supabase = createClient(env.SUPABASE_URL, env.SUPABASE_ANON_KEY);

// Admin client for server-side operations (service role, bypasses RLS)
export const supabaseAdmin = createClient(
  env.SUPABASE_URL,
  env.SUPABASE_SERVICE_ROLE_KEY,
);

• supabase uses SUPABASE_ANON_KEY and is the client-safe handle. It is subject to Row Level Security, so it can only read what your RLS policies allow.
• supabaseAdmin uses SUPABASE_SERVICE_ROLE_KEY and bypasses RLS. It is for server code only, the Stripe webhook handler, for example, writes subscription status with it.

All three values, SUPABASE_URL, SUPABASE_ANON_KEY, and SUPABASE_SERVICE_ROLE_KEY, are required and validated at startup by envSchema.parse in libs/config.ts; the app throws on boot if any are missing.

The Zod data models

Row shapes are declared as Zod schemas in models/ and re-exported from the barrel models/index.ts. There are four:

The barrel makes them importable from one place:

// models/index.ts
export { userSchema, type User } from './user';
export { subscriptionSchema, type Subscription } from './subscription';
export { paymentSchema, type Payment } from './payment';
export { blogPostSchema, type BlogPost } from './blogPost';

Each model also exports a static TypeScript type via z.infer, so you get an inferred row type for free wherever you import the schema.

Example: the user model

userSchema describes the users profile row. Notice the nullable fields and the ISO-datetime strings (Supabase returns timestamps as strings over the wire):

// models/user.ts
import { z } from 'zod';

export const userSchema = z.object({
  id: z.string(),
  email: z.string().email().nullable(),
  phone: z.string().nullable(),
  role: z.string().nullable(),
  created_at: z.string().datetime(),
  updated_at: z.string().datetime().nullable(),
});

export type User = z.infer<typeof userSchema>;

The same pattern holds for the others, subscriptionSchema carries user_id, stripe_customer_id, stripe_subscription_id and status; paymentSchema carries subscription_id, stripe_invoice_id, amount and currency; and blogPostSchema covers title, slug, content, published, tags and the optional meta_title / meta_description.

Tables & the SQL migration

The schema lives in one migration, supabase/migrations/0001_initial_schema.sql, which you run against your Supabase project (via supabase db push with the Supabase CLI, or by pasting it into the dashboard SQL Editor). It creates six tables in the public schema:

The migration does more than create tables. The users row id references auth.users(id), and a handle_new_user() trigger on auth.users auto-inserts a matching public.users row on signup. A shared set_updated_at() trigger keeps updated_at fresh on every update. Indexes are created on the columns the app filters by (e.g. stripe_subscription_id, slug).

Row Level Security is enabled on all six tables. The policies above govern the anon / authenticated keys (the supabase client). The server uses the service-role key (supabaseAdmin), which bypasses RLS, which is exactly why email_verifications and password_resets have no public policies at all: they are only ever touched server-side.

Server-only vs client-safe access

The service-role key is an all-powerful secret: it bypasses RLS and can read or write any row. It must never reach the browser. Two rules keep it safe in this boilerplate:

• SUPABASE_SERVICE_ROLE_KEY has no NEXT_PUBLIC_ prefix, so Next.js never inlines it into the client bundle. Only server code (route handlers, server components) can read it.
• Import supabaseAdminonly from server modules. For anything that runs in the browser, or any query that should respect a user's own permissions, use the supabase anon client, whose access is bounded by your RLS policies.

As a rule of thumb: reach for supabaseAdmin in webhook handlers and trusted server jobs that must act across users; reach for supabase everywhere a request is acting on behalf of a single signed-in user.

The local test database

Integration tests run against a real Postgres instance, not a mock. A docker-compose.yml service called db-test spins up Postgres 15 on host port 5433:

# docker-compose.yml -> service "db-test"
#   image: postgres:15-alpine
#   ports: "5433:5432"   (host:container)
#   POSTGRES_USER=test  POSTGRES_PASSWORD=test  POSTGRES_DB=shipveryfast_test

# start the test DB
npm run db:test:up      # docker-compose up -d db-test

# ...run your integration tests against it...
npm run test:integration

# tear it down
npm run db:test:down    # docker-compose down

The container database is named shipveryfast_test with user and password both test. It is disposable, bring it up before npm run test:integration and down again when you are done.

Querying with a typed model

Combine a Supabase client with a model to get validated, typed rows. Parse query results through the schema to fail fast on shape drift:

import { supabaseAdmin } from '@/libs/supabase';
import { subscriptionSchema } from '@/models';

const { data, error } = await supabaseAdmin
  .from('subscriptions')
  .select('*')
  .eq('user_id', userId)
  .single();

if (error) throw error;

// Validate + narrow to the Subscription type in one step
const subscription = subscriptionSchema.parse(data);
//    ^? typed as Subscription, status, stripe_subscription_id, ...

Adding a new model

To add a table-backed model, follow the existing pattern:

1. Add the table (and any RLS policies, indexes, triggers) to supabase/migrations/0001_initial_schema.sql, or a new migration file alongside it, and apply it to Supabase.
2. Create models/yourModel.ts exporting a Zod schema and its inferred type, mirroring models/user.ts.
3. Re-export the schema and type from models/index.ts so it is importable from @/models.
4. Query it with supabase (client-safe, RLS-bounded) or supabaseAdmin (server-only), parsing results through your schema.

Next steps

• Payments & subscriptions, how the webhook keeps the subscriptions table in sync.
• Environment variables, the three SUPABASE_* keys and where to find them.
• Authentication, how auth.users feeds the public.users table.