搭建应用数据库与鉴权后端
结合现有项目实现数据模型、行级权限和存储策略,交付迁移文件、类型化查询及权限测试。
完整提示词
<context>
With Supabase the browser talks to Postgres through the Data API using the public key (`anon`, or the newer publishable key), so row-level security is the only thing between any visitor and your data. The frequent failures: a table created without `enable row level security`, so it is world-readable; a policy that checks `auth.uid()` on SELECT but not `WITH CHECK` on INSERT and UPDATE, so users can write rows as someone else; the `service_role` (or newer secret) key shipped to the client; a view that bypasses RLS because it lacks `security_invoker`; a `security definer` function without a fixed `search_path`; and policies that join through membership tables without indexes, so every query gets slow.
</context>
<task>
Build the Supabase backend for this app:
{{app_data}}
{{#client}}
Client: {{client}}
{{/client}}
1. Read the repo: `supabase/` folder, existing migrations, generated types, how the client is created (`@supabase/ssr`, `supabase-js`), and any server code. Follow what exists.
2. Design the tables with primary keys, foreign keys, `not null`, check constraints, `created_at` defaults and an `owner` or `user_id` column referencing `auth.users(id)` where rows belong to a user. Add a `profiles` table filled by a trigger on `auth.users` insert, using a `security definer` function with `set search_path = ''`.
3. Write everything as migrations (`supabase migration new <name>`), never as dashboard clicks.
4. Enable RLS on every table in exposed schemas. For each table write separate policies per command (select, insert, update, delete) and role (`authenticated`, `anon`), with `USING` and `WITH CHECK` as needed. Put membership checks in a `security definer` helper function where they repeat, and index every column the policies filter on. Write `(select auth.uid())` rather than bare `auth.uid()` in policies so Postgres evaluates it once per query, not per row.
5. Create storage buckets with policies keyed on the object path (for example the first folder is the user id) and mark buckets public only for truly public files.
6. Generate TypeScript types (`supabase gen types typescript --local`) and write typed query helpers for the app's main reads and writes, using the user's session client, not `service_role`.
7. Keep `service_role` or secret keys for trusted server code only (cron, admin tasks, webhooks), read from server environment variables.
8. Test policies against the local stack (`supabase start`, `supabase db reset`): SQL tests with pgTAP if the project uses it, or integration tests that sign in as two users and prove each can only see and change their own or their team's rows, and that `anon` sees only what it should.
9. Run the tests and list remaining manual setup (auth providers, email templates, redirect URLs) for the user.
</task>
<constraints>
- Do not run migrations or `db push` against a remote project, and do not reset any non-local database, without the user's confirmation.
- Never put the `service_role` key or database password in client code, the repo or logs.
- No `using (true)` policies on tables holding user data unless the data is meant to be public, and say so.
{{> guardrails/scope-discipline}}
{{> guardrails/investigate-before-answering}}
{{> guardrails/verify-before-done}}
</constraints>
<output_format>
## Data model
Tables with key columns and relations, in a short list.
## Changes
One line per file: `path`, what changed.
## Access matrix
Table: Table | anon | authenticated owner | team member | other user.
## Tests
One line per test.
## Verification
Commands run and their real results.
## Setup steps
Dashboard or CLI steps the user must do, numbered.
</output_format>可替换内容
- {{app_data}}
- {{#client}}
- {{client}}
- {{/client}}
- {{> guardrails/scope-discipline}}
- {{> guardrails/investigate-before-answering}}
- {{> guardrails/verify-before-done}}
分类与标签
编程开发、后端开发、数据库迁移、行级权限、鉴权测试