Postgres Rules for Supabase Projects

Project: fresh `npm init -y` + `npm i -D vitest typescript` (Vitest 5.1.0, TypeScript strict). Every runner output below is pasted from the actual run.

Behaviour list (simplest first): 1. returns empty for blank input · 2. returns the address trimmed and lowercased when valid · 3. returns malformed when there is no @ · 4. returns malformed when there is more than one @ · 5. returns malformed when the domain has no dot · 6. returns too_long when the address is over 254 characters

Cycle 1: returns empty for blank input

```
[TDD] cycle 1/6  step RED  behaviour: returns empty for blank input
test:    src/validateEmail.test.ts > validateEmail > returns empty for blank input
ran:     npx vitest run src/validateEmail.test.ts -t "returns empty for blank input"
result:  FAIL  Error: Cannot find module './validateEmail' imported from <project>/src/validateEmail.test.ts
reason:  right (the unit under test does not exist yet: missing function, not a typo in the test)
changed: src/validateEmail.test.ts (+8, new file)
next:    minimum code in src/validateEmail.ts
```

```
[TDD] cycle 1/6  step GREEN  behaviour: returns empty for blank input
ran:     npx vitest run src/validateEmail.test.ts -t "returns empty for blank input" --reporter=verbose; npx vitest run src/validateEmail.test.ts
result:  PASS  Tests 1 passed (1)
changed: src/validateEmail.ts (+9, new file: result types + hard-coded `{ ok: false, error: 'empty' }`)
next:    refactor
```

Refactor: no refactor (hard-coded return is forced into the general version by cycle 2).

[Cycles 2–6 follow the same shape: each RED shows a real assertion mismatch such as `Tests 1 failed | 1 skipped (2)`, each GREEN the passing count, with refactors in cycles 5 and 6.]

Done

```
[TDD] DONE  cycles 6  tests added 6  suite PASS
ran:     npm test   (vitest run)   and   npx tsc -p .
result:  Test Files  1 passed (1) | Tests  6 passed (6); tsc exit 0
pre-existing failures: none (no tests existed)
found, not fixed: the 254 boundary itself (exactly 254 chars accepted) has no test; it passes today, so a test would be green on first run. 'a@.com' and 'a@b.' are accepted because the spec only asks for a dot in the domain.
```

No-fake-green check before every GREEN: no `.skip`/`.todo`, no assertion weakened, no test-only branch, nothing mocked.

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.]

Phase 1: Root cause investigation
Full suite: FAIL test_expired_coupon_rejected_twice (tests/test_checkout.py:16) AssertionError: Coupon(code='OLD50', …expires_at=2020-01-01…) is not None.
In isolation the same test fails at a different line (17): AssertionError: 50.0 != 100.0. So the first lookup in a process is rejected and every later lookup is accepted; in the full suite the first lookup was done by test_expired_coupon_rejected. Cross-test pollution through module-level state.
Cache probe at the boundary: cache before [] → 1st None → cache after ['OLD50'] → 2nd Coupon(OLD50). Docstring says the cache was added in 3f2a1c9 "to cut DB reads": that is the regression point.
Trace (root-cause-tracing.md): apply_coupon → get_coupon → `if code in _CACHE: return _CACHE[code]`. get_coupon writes the coupon to _CACHE before the expiry check, and the cache-hit branch has no expiry check at all.

Phase 2: Pattern analysis
Working path: cache miss → _load → expiry check. Broken path: cache hit. Expiry depends on `now`, passed per call, so it cannot be a cached property; "don't cache expired coupons" alone would still serve a coupon cached while valid after it expires.

Phase 3: Hypothesis
"get_coupon returns cached coupons without re-checking expires_at against the caller's now; the cache was added around the lookup but the validity check stayed only on the miss path." Confirmed by the probe above.

Phase 4: Implementation
Failing test first: test_cached_coupon_rechecked_for_expiry (look up while valid, then after expiry) plus a setUp that clears the module cache. Before the fix: 5 tests, 2 failures, both for the expected reason.
Fix at the source: the cache stores the raw record; the expiry check runs on every lookup, cached or not.

    c = _CACHE.get(code)
    if c is None:
        c = _load(code)
        if c is None:
            return None
        _CACHE[code] = c
    if c.expires_at < now:
        return None
    return c

Verification: python3 -m unittest -v → Ran 5 tests … OK. The originally failing test in isolation, 3 runs: OK, OK, OK. Fix attempts: 1.

Scope: app/dashboard/page.tsx and the modules it imports (components/charts, UsageCard, ProjectList, RichTextEditor, lib/api.ts).

| # | Rule | File:line | Impact | Finding |
|---|---|---|---|---|
| 1 | async-dependencies | app/dashboard/page.tsx:8-10 | CRITICAL | getProjects(user.id) and getUsage(user.id) depend only on `user` but are awaited one after the other: 3 sequential round trips before any HTML is sent, where 2 would do. Promise.all them. |
| 2 | bundle-barrel-imports | components/ProjectList.tsx:3 | CRITICAL | `import _ from 'lodash'` in a 'use client' file ships all of lodash for one sortBy. Use lodash/sortBy, or toSorted / localeCompare. |
| 3 | async-suspense-boundaries | app/dashboard/page.tsx:7-19 | HIGH | The whole page, including the static shell and RichTextEditor, waits for all three fetches. Move usage and projects into async children in <Suspense> so the shell streams first. |
| 4 | server-serialization | app/dashboard/page.tsx:16 | HIGH | The raw projects array goes to client ProjectList, which reads only id, name and status. Map to {id, name, status} on the server. |
| 5 | rerender-no-inline-components | components/ProjectList.tsx:7 | HIGH | `Row` is defined inside ProjectList, so every keystroke in the filter remounts every <li>. Interaction cost, not first load. Hoist Row. |
| 6 | rerender-derived-state-no-effect | components/UsageCard.tsx:4-5 | MEDIUM | percent is kept in state and set in useEffect: server HTML renders "0% of plan used" and the real value appears only after hydration. Compute it during render. |

Score: 62 / 100 (100 − 2×10 CRITICAL − 3×5 HIGH − 1×3 MEDIUM). This counts rule findings; it is not a measured load time.

Checked and not scored: the local charts barrel (five tiny stubs, tree-shakeable); RichTextEditor is a <textarea> today, so no dynamic import yet. Not a performance rule but it blocks the build: components/charts/index.ts re-exports names the chart files do not export, and page.tsx reads `params.id` synchronously though params is a Promise in Next 15.

What makes the first load slow, in order:
1. The server waterfall (rows 1 and 3). Parallelizing projects and usage removes one full round trip; Suspense lets the header paint after only getUser.
2. Client JS weight (row 2). A one-line import change.
3. Payload and first-paint correctness (rows 4 and 6).
Row 5 is an interaction cost; fix it after the others. Fixes were not requested, so no files were changed.