Markdown edition

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

> Choose a managed Postgres path for an existing app, move a reviewed migration to the right Supabase development project, test per-user access and plan production recovery.

By BIGCHANGE.AI Editorial

Published: 2026-10-22T22:52:38.031Z
Updated: 2026-10-22T22:52:38.031Z
Canonical: https://bigchange.ai/blog/managed-database-ai-assisted-web-app-guide

![Conceptual charcoal illustration of one developer at a desktop monitor with unreadable code-like marks, and two unbranded dark-screen phones resting on separate stands beside it.](https://bigchange.ai/api/media/file/managed-database-development-verification-hero-v1.png)
AI-generated conceptual illustration by BIG CHANGE.

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](https://bigchange.ai/blog/react-supabase-email-auth-protected-data-guide). If the unresolved decision is which backend architecture your app needs, use our [backend requirements guide](https://bigchange.ai/blog/choose-ai-assisted-web-app-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](https://supabase.com/docs/guides/database/overview) explains the managed Postgres platform, while its [billing guide](https://supabase.com/docs/guides/platform/billing-on-supabase) 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:

```text
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](https://supabase.com/docs/guides/local-development/cli/getting-started) 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`.

```bash
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.

```sql
-- 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](https://supabase.com/docs/guides/deployment/database-migrations) and [local workflow](https://supabase.com/docs/guides/local-development/cli-workflows) document these steps.

```bash
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.

```bash
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](https://supabase.com/docs/reference/cli/supabase-db-push) documents both flags.

```bash
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](https://supabase.com/docs/guides/auth/quickstarts/react) and [API key guide](https://supabase.com/docs/guides/getting-started/api-keys).

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

```bash
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.

```javascript
// 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](https://supabase.com/docs/guides/database/connecting-to-postgres): 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.

| Check | Expected | Actual response and row-state evidence |
| --- | --- | --- |
| Local migration and SHA-256 | Reviewed file replays on disposable local stack |  |
| Development target | Dashboard name/reference, CLI link and app URL/key identify one project |  |
| Pre-push history, schema, dry-run | No unexplained drift; only approved pending files |  |
| Development push and fresh readback | Migration history, table, index, grants, RLS, four policies |  |
| A own insert/read/update/delete | Allowed; owner and row changes match |  |
| B read/update/delete A row | No A data or change |  |
| B insert as A; A insert as B; A change owner to B | Denied; no new or reassigned row |  |
| Signed-out read/insert/update/delete | No access or row change |  |
| Any app server path | Same owner boundary |  |
| Production and recovery | Separate authorization and observed evidence, or hold |  |

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.

```bash
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](https://supabase.com/docs/guides/platform/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](https://supabase.com/docs/guides/platform/clone-project) 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](https://supabase.com/docs/guides/local-development/cli/getting-started), [local workflow](https://supabase.com/docs/guides/local-development/cli-workflows) and [database migrations](https://supabase.com/docs/guides/deployment/database-migrations) provide the executable local and linked-project sequence. The CLI [push reference](https://supabase.com/docs/reference/cli/supabase-db-push) explains what dry-run does and why a linked reset is destructive.
- [Row Level Security](https://supabase.com/docs/guides/database/postgres/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](https://supabase.com/docs/guides/auth/quickstarts/react), [JavaScript client](https://supabase.com/docs/reference/javascript/initializing) and [API keys](https://supabase.com/docs/guides/getting-started/api-keys) distinguish the browser project key from a user's session and privileged credentials.
- [Connecting to Postgres](https://supabase.com/docs/guides/database/connecting-to-postgres) documents direct and pooled modes. Choose from the actual runtime and project Connect dialog.
- [Backups](https://supabase.com/docs/guides/platform/backups), [restore to a new project](https://supabase.com/docs/guides/platform/clone-project) and [billing](https://supabase.com/docs/guides/platform/billing-on-supabase) describe plan-dependent recovery and costs. Your Dashboard and an observed rehearsal determine what your own project can recover.

## Sources

- [Supabase CLI setup](https://supabase.com/docs/guides/local-development/cli/getting-started) — Current npm, Node and container prerequisites; local init and start.
- [Local development workflow](https://supabase.com/docs/guides/local-development/cli-workflows) — Local replay and exact linked-project workflow, drift and destructive reset distinction.
- [Database migrations](https://supabase.com/docs/guides/deployment/database-migrations) — Timestamped SQL migration files and remote deployment.
- [CLI db push](https://supabase.com/docs/reference/cli/supabase-db-push) — Dry-run lists pending migrations; push applies to linked project.
- [Row Level Security](https://supabase.com/docs/guides/database/postgres/row-level-security) — Grants, auth.uid and per-operation policies.
- [React Auth quickstart](https://supabase.com/docs/guides/auth/quickstarts/react) — Vite browser client configuration.
- [API keys](https://supabase.com/docs/guides/getting-started/api-keys) — Publishable browser key versus privileged secrets.
- [Connecting to Postgres](https://supabase.com/docs/guides/database/connecting-to-postgres) — Direct, session and transaction connection modes.
- [Database backups](https://supabase.com/docs/guides/platform/backups) — Plan-specific backups and Storage-object exclusion.
- [Restore to a new project](https://supabase.com/docs/guides/platform/clone-project) — Restore scope and configuration gaps.
- [Billing on Supabase](https://supabase.com/docs/guides/platform/billing-on-supabase) — Plan, project compute and usage categories.