Catalog

> Assumption: signed-in users read their own orders and create their own orders as `pending`; every status change after that is made by your server with the `service_role` key, never by the browser.

Audit: `public.orders` (about 1.8M rows)

Rule files applied: `security-rls-basics`, `security-rls-performance`, `security-privileges`, `schema-foreign-key-indexes`, `query-missing-indexes`, `query-composite-indexes`, `schema-data-types`, `schema-constraints`, `lock-short-transactions`, `monitor-explain-analyze`.

Findings, by rule (first 6 of 12)

| # | Severity | Rule | Finding |
|---|---|---|---|
| 1 | CRITICAL | `security-rls-basics` | `orders_insert ... with check (true)` lets anyone who can insert write an order for any `user_id`. Combined with finding 2, an anonymous visitor holding only your public anon key can create orders on behalf of any user. |
| 2 | CRITICAL | `security-privileges` | `grant select, insert, update ... to anon`. Orders belong to signed-in users, so `anon` needs no privilege at all. None of the three policies has a `to` clause, so each applies to every role, including `anon`. |
| 3 | HIGH | `security-privileges` | `authenticated` can `update` every column of its own rows: the owner can still run `update orders set status = 'paid', total_cents = 1` from the browser. |
| 4 | HIGH | `security-rls-basics` | The insert path also trusts `status`: a client can insert a row that is already `status = 'paid'`. |
| 5 | HIGH | `security-rls-performance` | All policies call `auth.uid()` bare, so it is evaluated per row. Wrap it as `(select auth.uid())` so it is evaluated once per statement. |
| 6 | HIGH | `schema-foreign-key-indexes`, `query-missing-indexes` | No index on `user_id`. It is the foreign key, the RLS filter column and the column every "my orders" query filters on. Each RLS-filtered select over 1.8M rows is a sequential scan. |

Fixing migration (file 1 of 2, excerpt)

```sql
set local lock_timeout = '5s';

revoke all on public.orders from anon;
revoke update, delete, truncate, references, trigger on public.orders from authenticated;
grant select, insert on public.orders to authenticated;

drop policy if exists orders_select on public.orders;
drop policy if exists orders_insert on public.orders;
drop policy if exists orders_update on public.orders;

create policy orders_select on public.orders
  for select to authenticated
  using ((select auth.uid()) = user_id);

create policy orders_insert on public.orders
  for insert to authenticated
  with check ((select auth.uid()) = user_id and status = 'pending');
-- No update or delete policy: denied for every role except service_role.
```

File 2 holds only `create index concurrently if not exists orders_user_id_created_at_idx on public.orders (user_id, created_at desc);` because `concurrently` cannot run inside the transaction the Supabase CLI wraps around each migration.

[The full answer continues with findings 7–12, the lock plan for 1.8M rows, the status and total_cents check constraints added `not valid` then validated, an `explain analyze` to run before and after, and one RLS test per role.]

Assumption: no n8n-MCP server is connected in this session, so I took the skill's fallback path: I wrote the workflow JSON directly from the bundled rules and checked it by review (a rule-check script plus a dry run of every expression). The real validator (`validate_workflow`) was not run, and nothing was deployed.

How the skill routed this

| Step | Sub-skill used | What it decided |
|---|---|---|
| Shape | `n8n-workflow-patterns` | Webhook processing pattern: Webhook, then Validate, then Action, then Respond |
| Node parameters | `n8n-node-configuration` | Slack post needs a channel and text. `responseMode: "responseNode"`. `options.responseCode` set on every Respond node. UUID node ids, no placeholder credentials |
| Expressions | `n8n-expression-syntax` | Webhook data lives under `.body`. Use `$('Webhook').item.json.body.*` once past the IF node. `=` prefix on every expression field |
| Error paths | `n8n-error-handling` | Slack gets `onError: "continueErrorOutput"` and its `main[1]` is wired to the 500 responder. `retryOnFail` is on. Bad input is caught upstream and gets a 400 |
| Validation | `n8n-validation-expert` | Runtime-profile checks done by review. Security warnings get fixed (header auth on the webhook) |

```
Webhook ──> Validate Input ──true──> Post to #signups ──success (main[0])──> Respond 200 OK
                        └──false──> Respond 400 Invalid Input
                                     Post to #signups ──error (main[1])────> Respond 500 Slack Failed
```

Validation result (excerpt)

```
PASS  Webhook responseMode is responseNode (Respond nodes control the reply)
PASS  Every branch (success and each error) ends at a Respond to Webhook node: no hanging branch
PASS  'Post to #signups': onError=continueErrorOutput AND main[1] wired
PASS  'Post to #signups': retryOnFail with maxTries <= 5 and waitBetweenTries <= 5000
PASS  Every {{ }} field starts with = (no 'Expression requires = prefix' error)
PASS  Webhook fields read from .body, never $json.name / $json.email / $json.plan
…
43 passed, 0 failed
```

Expression dry run against sample webhook items:

```
valid           IF -> true  (Slack)  Slack text: New signup: Ana Lima (ana@example.com) on pro
missing_plan    IF -> false (400)
bad_email       IF -> false (400)
no_body         IF -> false (400)
500 body:   {"error":"upstream_error","message":"The signup was received but could not be posted to Slack. Please retry later."}
```

Not verified without the server: the node typeVersions (Webhook 2.2, IF 2.3, Slack 2.3, Respond 1.5) and the Slack v2 parameter names; confirm by opening the nodes after import.

[The full answer also holds the six-node list, the importable workflow JSON and the expressions table with the reason behind each one.]

2. Tool design

| Tool | Does | Inputs (constraints) | Returns | Read-only / destructive | Errors the model can fix |
|---|---|---|---|---|---|
| `find_customers` | Customers whose name or email contains the text, case-insensitive | `query` string 1–100 (trimmed), `limit` int 1–50 = 20, `cursor` optional | id, name, email; `next_cursor` | read-only, idempotent | no match: try a shorter part of the name or the email domain |
| `list_customer_orders` | One customer's orders, newest first | `customer_id` int ≥ 1, `status` enum optional, `limit`, `cursor` | customer + orders; `next_cursor` | read-only, idempotent | unknown customer: call `find_customers` |
| `get_order` | One order with the customer's name and email | `order_id` int ≥ 1 | order + customer (structured) | read-only, idempotent | not found: check the number or list the customer's orders |
| `refund_order` | Sets a paid order to refunded | `order_id` int ≥ 1, `confirm` int optional, must equal `order_id` | updated order, `previous_status: "paid"` | destructive, idempotent | no `confirm`: preview, ask the user; order pending or already refunded; not found |

Checklist notes: one rule for empty results (an `isError` result with a next step), applied to every tool. All SQL is parameterized. Read tools use a read-only SQLite handle; only `refund_order` opens a writable one, and its `UPDATE ... WHERE id = ? AND status = 'paid'` is a single statement. The guard on the destructive tool is server-side (the preview/confirm step), not just the `destructiveHint` annotation.

3. Scaffold

From `templates/typescript-server.md`. Installed: `@modelcontextprotocol/server` 2.4.0, `@modelcontextprotocol/client` 2.4.0, `zod` 4.6.7; Node 24.6.0.

```
build-an-mcp-server/
  data/seed.sql, data/shop.db     3 customers, 5 orders
  src/index.ts                    stdio entry point
  src/server.ts                   createServer(): the four tools
  src/shop.ts                     SQLite client module (the system being wrapped)
  test/smoke.ts                   spawns the server over stdio, calls every tool
```

5. Test (Inspector CLI, eval loop layer 2)

```
$ npx @modelcontextprotocol/inspector --cli npx tsx src/index.ts --method tools/call --tool-name get_order --tool-arg order_id=1001
{
  "content": [{ "type": "text", "text": "Order #1001 for Ada Lovelace <ada@example.com>: $49.99, paid, created 2026-10-02T09:15:00Z" }],
  "structuredContent": { "order": { "id": 1001, "customer_id": 1, "total_cents": 4999, "status": "paid", "created_at": "2026-10-02T09:15:00Z", "customer_name": "Ada Lovelace", "customer_email": "ada@example.com" } }
}
```

Built, tested, not tested

- Built: four tools over `data/shop.db`, server-side preview/confirm guard on `refund_order`, smoke test, configs for Claude Code, Codex and Cursor, README.
- Tested: starter smoke (pass), full smoke test 14 checks (pass), strict typecheck (pass), Inspector CLI call (pass), seed database unchanged after tests.
- Not tested: the Inspector web UI by hand and the model check (running the five real requests through a live Claude Code session and recording which tool it picks).

[The full answer also holds `src/shop.ts`, `src/server.ts`, `test/smoke.ts` and the three client config snippets.]

Integrations

Auth — DONE. Supabase magic link; /app and /history require a session; /login and / are public.
Database — DONE. users(id, email, plan, trial_ends_at), courses(id, title), progress(id, user_id, course_id, lesson_no, completed_at). RLS: users read/write only rows where user_id = auth.uid().
Payments — NEEDS KEY. Stripe Checkout, $49/month, 5-day trial. Webhook /api/stripe/webhook verifies the signature with STRIPE_WEBHOOK_SECRET before any write. checkout.session.completed → users.plan = "pro"; invoice.payment_failed → plan = "past_due"; customer.subscription.deleted → plan = "free".
Email — NEEDS KEY. Resend: welcome on sign-up ("You're in — here's day 1"); trial-ending 2 days before trial_ends_at ("Your trial ends Thursday").

Source of truth for "paid": users.plan, written only by the webhook. The client never sets it.

Secrets expected in the environment panel: SUPABASE_URL, SUPABASE_ANON_KEY, SUPABASE_SERVICE_ROLE_KEY (server only), STRIPE_SECRET_KEY, STRIPE_WEBHOOK_SECRET, RESEND_API_KEY.

Test before going live:
1. Sign up with a fresh email → magic link arrives → /app loads → one row in users with plan = "free".
2. Stripe test card 4242… → return to /app → plan = "pro" within 5 seconds → welcome email received.
3. Fire a test `customer.subscription.deleted` from the Stripe dashboard → plan = "free".

Built. Here is what exists now:

- /today — the screen learners live on: today's lesson card, a 7-day streak strip, and a trial banner counting down from 5 days with one coral "Keep my streak" button that routes to /upgrade.
- /login — magic-link sign-in by email, no password.
- /settings — sign-out only.

Data model (created before UI, as asked):
users(id, email, created_at, trial_ends_at, plan)
lessons(id, day_number, title, duration_min)
completions(id, user_id, lesson_id, completed_at)
Streak is computed from completions, not stored.

Assumptions: one lesson per day; streak resets after a missed day; trial length fixed at 5 days.
Left out on purpose: analytics, onboarding tour, dark mode, admin.
One question before the next screen: should a learner on day 6 with no plan still see today's lesson, or only the upgrade page?

Done — one change.

Files changed:
- src/pages/Pricing.tsx — added a Monthly / Yearly segmented toggle above the plan cards; yearly shows the discounted price and a "save 20%" tag
- src/lib/pricing.ts — added `yearlyPrice(monthly)`; no other exports touched

Outside the pricing page: nothing. Layout, colour tokens, routes and the dashboard are untouched. I did not reformat Pricing.tsx beyond the inserted block.

Acceptance: toggle defaults to Monthly; switching shows $190/yr under the Pro plan instead of $19/mo, and the tag appears. Verified in preview.

Revert: delete the toggle block in Pricing.tsx (lines 41–58) and the `yearlyPrice` helper.

Restating: submitting the sign-up form throws `column "email" does not exist`, and you expected to be redirected to /dashboard. It worked before the profile table was added.

Ranked causes
1. The sign-up handler now inserts into `profiles`, which has no `email` column — the form was pointed at the new table, not `users`. Check: open the insert in `signup.ts`; if the table is `profiles`, confirmed.
2. A migration added `profiles` without `email` while the ORM model still declares it. Check: compare `schema.sql` to the model.
3. Two databases (local vs. hosted) with different schemas. Check: run the migration list against the hosted DB.

Checking #1: `signup.ts` line 22 inserts `{ email, name }` into `profiles`. Confirmed.

Fix (smallest): insert `email` into `users` as before, and only `{ user_id, name }` into `profiles`.
- await db.from("profiles").insert({ email, name })
+ const { data: u } = await db.from("users").insert({ email }).select("id").single()
+ await db.from("profiles").insert({ user_id: u.id, name })

Verify: sign up with a fresh address; you should land on /dashboard and see one row in each table.
Regression risk: any other code that assumed profiles.email — I found none.