Set up a managed database for an AI-assisted web app

An AI coding agent can draft a database migration for your existing app. The harder part is proving that the migration reached the intended managed development project, the app uses that same project, and users can access only their own rows. This Supabase Postgres guide takes you from a reviewed local migration through those checks and a separate production and recovery decision. It assumes you own an existing Vite app that already uses Supabase Auth; the example adds one new table, not a replacement for your app's data model.

The big change

• What changed: A database setup task becomes a traceable path from app requirements and versioned SQL to a named development project, migration history and schema readback.
• Why it matters: A local migration or working screen cannot show that the managed app points at the right database or that cross-user operations are denied.
• What to watch: Use two real permitted test identities and inspect responses and row state. Record the production authorization and recovery evidence separately.

The owner needs the app repository, authority over a Supabase organization and development project, a running local container runtime, and permission to use two distinct test accounts and disposable data. Give a file-capable coding agent access only to the files and requirements it may inspect. Review every proposed change. Keep customer data, passwords, service keys and access tokens out of its prompt and commits. This is a documented procedure; BIG CHANGE has not run it against an app or Supabase account.

If you first need to add email sign-in and protected CRUD to an existing React/Vite app, use our Supabase Auth guide. If the unresolved decision is which backend architecture your app needs, use our backend requirements guide. Here the decision and work are narrower: establish a managed database, move a reviewed migration to the right environment, verify access there, and plan recovery.

1. Choose against the app's actual data

List the app's entities, relationships, reads, writes, retention rules and multi-record transactions. Have the agent cite the relevant requirement or existing code for each proposed table, index and API call. Relational data with foreign keys, joins and transactions is a good reason to consider Postgres; it is not a reason to migrate an unknown app automatically. Record why the selected provider meets the app's region, network, identity, migration and recovery requirements. The worked path below selects Supabase managed Postgres, Auth and its Data API. A different identity provider, document database or trusted-server data path needs its own authorization and migration design.

Check the actual organization plan, development project region, compute and variable-usage budget, and who can administer the project. Separate development and production projects are separate billable resources; inspect your account's current terms before creating either. Use permitted test data only. Supabase's database overview explains the managed Postgres platform, while its billing guide identifies the plan and usage categories. Neither establishes your account's price or entitlement.

Give the agent this bounded assignment after the owner has answered those questions:

Inspect only this authorized app repository and its data requirements. Inventory the current entities, queries, writes, identity flow, API/server routes, environment configuration and migration history, citing files for each finding. Propose the smallest Supabase Postgres migration and app integration for the approved data model. Use the reference project_items SQL and browser client below only if their per-user model fits. Show the complete migration, client and configuration diff, and list every table, view, function and server route needing a separate access decision. Do not run remote migrations, create provider resources, change production configuration or claim access tests passed. Return an acceptance plan for two permitted users, a signed-out client, and owner-change attempts.

2. Create and replay the migration locally

Supabase's current CLI setup requires Node.js 20 or later for npm installation and a running Docker-compatible container runtime for its local stack. In the reader's existing npm-managed app, the following commands install a project-scoped CLI, initialize Supabase configuration if absent, start the local stack and create an empty timestamped migration. If the app uses another package manager, use the official equivalent and keep its existing lockfile. Record the resolved CLI version and commit the reviewed configuration and migration. init is needed only when this repository lacks supabase/config.toml.

npm install supabase --save-dev
npx supabase --version
npx supabase init
npx supabase start
npx supabase migration new create_project_items

Place the complete SQL below in the generated supabase/migrations/<timestamp>_create_project_items.sql after comparing its owner model with your real app. It is a new table reference for one user owning each row. Existing schemas, team membership, shared rows and other protected paths need their own reviewed migrations and policies. Preserve an already applied migration file; later changes belong in another migration.

-- Reference migration for an app using Supabase Auth + Data API.
-- Review against the real app before applying; this is a NEW table example.
create table public.project_items (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null default auth.uid() references auth.users(id),
  label text not null check (char_length(trim(label)) between 1 and 160),
  created_at timestamptz not null default now()
);

create index project_items_owner_created_idx
  on public.project_items (owner_id, created_at desc, id);

alter table public.project_items enable row level security;

-- Some projects provide broad default grants. Remove them explicitly.
revoke all on table public.project_items from anon, authenticated;
grant select, insert, update, delete on table public.project_items to authenticated;

create policy project_items_select_own on public.project_items
  for select to authenticated
  using (owner_id = (select auth.uid()));

create policy project_items_insert_own on public.project_items
  for insert to authenticated
  with check (owner_id = (select auth.uid()));

create policy project_items_update_own on public.project_items
  for update to authenticated
  using (owner_id = (select auth.uid()))
  with check (owner_id = (select auth.uid()));

create policy project_items_delete_own on public.project_items
  for delete to authenticated
  using (owner_id = (select auth.uid()));

-- This file does not secure any other table, view, function, Storage bucket or server route.

Replay the migration only on the disposable local stack. db reset --local destroys local data and replays local migrations and seed data, so save anything needed first. It is not a remote drift repair. Record the migration filename and SHA-256, local result and resulting table, index, grants, RLS and four policies. Supabase's migration guide and local workflow document these steps.

npx supabase db reset --local

3. Move it to the exact managed development project

Before setting app variables or creating managed-project test users, the owner approves a development-only organization, project name, exact project reference, region, plan and likely charges. Read the reference from its Dashboard URL. The local stack's success has not migrated that project. Sign in to the CLI, link by the approved reference and compare the linked entry from projects list with the Dashboard name/reference and the intended app environment. Do not place the access token or prompted database password in the acceptance record.

npx supabase login
npx supabase link --project-ref <approved-development-project-ref>
npx supabase projects list
npx supabase migration list

Compare the local migration files with the managed project's remote migration history and actual schema. A matching history timestamp alone does not prove its table or policies have not drifted. If the project already has Dashboard-made changes, inspect them and follow Supabase's documented db pull and review path before a push; reconcile the resulting migration locally. Stop on an unexpected difference. Do not use migration repair to make a mismatch disappear.

With the exact target and immutable migration reviewed, preview pending filenames, open those files to inspect their SQL, then have the authorized development operator run one push. --dry-run lists pending migrations; it does not execute or prove them. A failed or uncertain push requires a fresh remote history and schema read before considering another attempt. The CLI reference documents both flags.

npx supabase db push --dry-run
npx supabase db push
npx supabase migration list

Read back the managed development project itself: public.project_items, its project_items_owner_created_idx, the actual anon and authenticated grants, enabled RLS, and the select, insert, update and delete policies. Record observed evidence, not only the CLI's success line. Do not use db reset --linked here: Supabase says it drops and rebuilds the linked remote schema and data. The local --local reset above is the only reset in this recipe.

4. Connect the app to that migrated project

Only after the managed readback, set VITE_SUPABASE_URL and VITE_SUPABASE_PUBLISHABLE_KEY for the same development project. Compare the URL's project reference and the Dashboard key's project identity to the linked migration target. Create the permitted test users through that project's actual Supabase Auth flow. A publishable browser key identifies the project; the signed-in user's token supplies identity for auth.uid(). The service_role key, secret key, database password and any privileged server credential never belong in a VITE_ variable, browser bundle, prompt or commit. See the React quickstart and API key guide.

For an existing npm-managed Vite app, install a reviewed @supabase/supabase-js v2 release and record its resolved lockfile version:

npm install @supabase/supabase-js@^2

Save the next complete example as a reviewed browser module in the app, then connect its calls to the actual screens. It assumes the app already establishes Supabase Auth sessions. It does not create a sign-in UI. Its insert leaves owner_id to the SQL default. Do not use the module for an unrelated table or treat it as proof the app's other routes are protected.

// Browser-side reference for an app that already uses Supabase Auth.
// Install a reviewed @supabase/supabase-js v2 release in the reader's app.
import { createClient } from '@supabase/supabase-js'

const url = import.meta.env.VITE_SUPABASE_URL
const publishableKey = import.meta.env.VITE_SUPABASE_PUBLISHABLE_KEY
if (!url || !publishableKey) throw new Error('Missing project URL or publishable key')

export const db = createClient(url, publishableKey)

function resultOrThrow({ data, error }) {
  if (error) throw error
  return data
}

export async function listOwnItems() {
  return resultOrThrow(await db.from('project_items')
    .select('id, owner_id, label, created_at')
    .order('created_at', { ascending: false }))
}

export async function addOwnItem(label) {
  // SQL defaults owner_id to auth.uid(); the client must not choose it.
  return resultOrThrow(await db.from('project_items')
    .insert({ label })
    .select('id, owner_id, label, created_at')
    .single())
}

export async function renameOwnItem(id, label) {
  return resultOrThrow(await db.from('project_items')
    .update({ label })
    .eq('id', id)
    .select('id, owner_id, label, created_at'))
}

export async function removeOwnItem(id) {
  return resultOrThrow(await db.from('project_items')
    .delete()
    .eq('id', id)
    .select('id'))
}

// Empty update/delete arrays can mean RLS denied row visibility. Compare
// actual before/after database state during acceptance checks.

An app with a trusted server may need a direct Postgres connection instead of browser Data API calls. Select the route for its actual runtime from Supabase's connection guide: direct for persistent IPv6 or separately approved IPv4, session pooler for persistent IPv4-only, and transaction pooler for many short-lived connections. Transaction mode has prepared-statement and session-state restrictions. Read the endpoint from the project's Connect dialog; do not infer its host. Keep the credential server-side. An administrative Postgres connection can bypass the browser's per-user RLS path, so the server must enforce its own validated-user authorization. Test the chosen route with permitted requests and logs.

5. Prove the access boundary in the real development app

Use two distinct permitted Auth users, A and B, plus a signed-out client. Through the real app and direct Data API calls, let A insert with the default owner, read, update and delete an own row. Keep another A row while B attempts to read, update and delete it directly. B should also try to insert with A's owner_id; A should try inserting with B's owner_id and changing an existing A row's owner_id to B. Repeat read, insert, update and delete as signed out. Inspect each response and a fresh owner-side row readback. A filtered update or delete can return an empty array instead of an error. Record whether the original row exists and is unchanged. Check any trusted server route touching the same data separately. Do not put test passwords or JWTs in a shared record.

For a compact record, copy and fill this form. An empty observation is untested, not passed.

Add the date, repo revision, CLI/SDK versions, development organization/name/reference, privately held A/B user IDs, exact connection mode/endpoint without credentials, and any failed check. If a legitimate request is denied, inspect the actual Auth identity, grants and policies. If cross-user access succeeds, hold release, fix the affected table/policy/server path in a new reviewed migration or code change, and repeat the full matrix. A passing screen does not establish the database boundary.

6. Authorize production and recovery separately

The development result does not authorize a production project, migration or app release. The owner first approves the separate production organization/reference, region, plan and spend, identity configuration, data policy, backup window and exact code/migration diff. Then the authorized operator links the approved production reference and repeats the identity guard, history and schema comparison. Only if these agree with the reviewed plan, inspect the pending files in a dry-run, push once, and read back fresh migration history, table, index, grants, RLS and policies for that project. This is a separate authorized session and target; check the project identity again after linking.

npx supabase link --project-ref <approved-production-project-ref>
npx supabase projects list
npx supabase migration list
npx supabase db push --dry-run
npx supabase db push
npx supabase migration list

Use permitted production test identities to repeat the allowed and denied operations before real data. Check the app's actual release path: changing database configuration or schema may also require an app deployment. Record any failed or unobserved step as a release hold.

Inspect the project's actual Database > Backups page. Supabase documents daily backups for Pro, Team and Enterprise with plan-dependent retention; Free projects need a separate export plan. Point-in-time recovery is an optional add-on, not an assumed setting. Database backups exclude Storage file objects. Record who can restore, acceptable data loss and downtime, a separate Storage-object recovery path where needed, and which Auth/settings integrations a restored project would require. The restore-to-new-project guide explains its scope. Seeing a backup listed is not a restore test. If the owner authorizes a non-destructive rehearsal, use a separate recovery project or approved export/restore sandbox and check actual rows, policies and identity behavior. Otherwise record recovery as untested and hold any claim of restore readiness.

If a push reports drift, compare the exact remote history and schema before another action, reconcile through the documented migration path, and replay locally. If a connection fails, check the actual endpoint, connection mode, IP support, TLS and credential scope without printing secrets. If the plan lacks the required backup or restore path, keep the recovery hold until an export route or plan change is approved. The app host, agent and Supabase project may each have separate costs; compute, storage, transfer and recovery settings affect the bill. No account-specific price, successful migration, security result or restore outcome is asserted here.

Sources & further reading

• Supabase CLI installation, local workflow and database migrations provide the executable local and linked-project sequence. The CLI push reference explains what dry-run does and why a linked reset is destructive.
• Row Level Security explains grants, auth.uid() and operation policies. The SQL above is scoped to the new example table; inspect every other exposed route separately.
• React Auth quickstart, JavaScript client and API keys distinguish the browser project key from a user's session and privileged credentials.
• Connecting to Postgres documents direct and pooled modes. Choose from the actual runtime and project Connect dialog.
• Backups, restore to a new project and billing describe plan-dependent recovery and costs. Your Dashboard and an observed rehearsal determine what your own project can recover.