Database, migrations and data access

Schema ownership

Supabase is hosted in development and production. Next.js uses the Data API and RPCs; kickstart applies versioned SQL through the Management API. The current installation uses its own private.kickstart_migrations checksum ledger, not Supabase CLI migration history.

Choose the correct client

lib/supabase/server.ts creates a server client using the public project key and the signed-in user's cookies. requireUser() verifies the account and returns its user-scoped client. This is the normal choice for dashboard reads and mutations.

import { getWorkspace, requireUser } from '@/lib/auth';

export async function readSelectedWorkspaceName() {
  const { organization } = await getWorkspace();
  const { supabase } = await requireUser();
  const { data, error } = await supabase
    .from('organizations')
    .select('id,name')
    .eq('id', organization.id)
    .single();
  if (error) throw new Error('Workspace could not be loaded.');
  return data;
}

lib/supabase/admin.ts is privileged and server-only. Existing justified consumers include API credential exchange, Auth deletion, email receipts and guarded billing synchronization. Do not use it for ordinary dashboard reads just because an RLS error is inconvenient.

Queries, grants and RLS

A table grant permits an operation; RLS restricts rows. Both are needed. An authenticated role alone is not a tenant boundary. Policies should use current verified membership and explicit ownership, not editable user metadata.

The notes migration gives every note org_id, a workspace foreign key, an index and narrow grants. Members can select; managers can insert a note authored by themselves. No update or delete access is granted. The complete SQL is downloadable and is the same source rendered in that tutorial.

Adding UPDATE requires a SELECT policy and appropriate USING/WITH CHECK conditions. Restrict writable columns so the client cannot move a record to another tenant. Index fields used in membership/tenant filters. Prefer explicit selected columns and bounded queries over unbounded select('*') lists.

RPC and privileged functions

RPCs let one database transaction enforce lifecycle or concurrency rules. The existing workspace invitation and owner-transfer functions check the actor, lock relevant state and return safe results. Preserve their lock ordering when extending them.

Security-definer functions execute with elevated database privileges. Use them only for a specific operation that cannot be safely expressed through ordinary policies, set an empty search path, qualify every relation and revoke default public execution. Public wrappers must validate identity and permissions. A function is not secure merely because its UI is hidden. Supabase RLS guidance.

Add an immutable migration

Create a new timestamped SQL file under supabase/migrations. Review the proposed schema, grants, policies, triggers, cleanup and compatibility with existing rows. Apply it through the existing kickstart runner on the explicitly authorized hosted test target. Its checksum ledger and transaction/advisory locking coordinate repeat runs.

Once a migration has been applied or released, do not edit it. Make a new corrective migration. Do not reset a shared database, remove checksums to hide drift or combine this runner with CLI migrations without a documented transition.

The Management API token is setup-only. You do not need direct database credentials or a Docker-dependent shadow database to author a SQL file. Before any provisioning, run the applicable read-only checks; they cannot conclusively prove DDL write permission.

Types and fixtures

This source snapshot has local TypeScript types and some explicit relationship narrowing, but no shipped database-type generation command. Use actual existing types, or define a small result shape at a reviewed boundary as the notes example does. Do not import a nonexistent Database module or advertise an npm type-generation script that is not installed.

Public example records use synthetic values. Avoid automatically seeding fake customers or paid subscriptions into buyer installs. SQL test fixtures belong in transactions that roll back on a dedicated test project. Real Auth/email/payment flows require separate setup and cleanup; transactional SQL is not an inbox test.

Retry and recover safely

A failed migration should report the specific error and preserve previous provider resources. Inspect the migration ledger and intended target before retrying. If an already-applied file's checksum differs, restore the original released content and add a corrective migration. Do not reset the database to make the checksum agree.

Code rollback and data rollback are different. An older application may not understand a new column or schema constraint. Prefer additive changes and staged removal, and rehearse recovery under your provider's available backup plan. Maintenance covers the boundary between setup receipts and database backups.

Verify isolation

Test A reading/writing B, a Member calling a manager operation, an unverified account, a stale token after deletion and concurrent operations where uniqueness matters. npm run test:hosted uses an explicit target guard and transactional fixtures; browser tests verify the user flow separately.

No hosted migration or notes RLS test was run for this documentation project. Static sample validation does not establish database enforcement. Execute the provided fixture on your authorized test installation before adopting the recipe.