---
name: database-amplification-guardrails
description: Use when implementing, changing, or reviewing any repo path that reads, writes, fans out to, or migrates database-backed state, including API handlers, store/service logic, realtime/background jobs, client flows that trigger eager API reads, N+1 risks, broad first-paint reads, access checks, lazy resolvers, and TiDB migrations where runtime or rollout cost may grow with unrelated data.
---

# Database Amplification Guardrails

## Core Principle

Database work must scale with the explicit user intent, job scope, or product limit, not with unrelated persisted data. Before adding or reusing a query, write, fanout, cleanup, or migration step, name the bounded set it depends on.

If you discover an existing path with amplification risk while working nearby, report it with the file/path, trigger, cost driver, and suggested follow-up. Do not silently copy the pattern.

## When To Use

Use this skill for any repo change that can affect database cost, including but not limited to:

- API handlers, DTO assembly, store/domain queries, service orchestration, realtime subscriptions, background jobs, cleanup jobs, imports, exports, sync, search, reporting, or admin operations.
- Client or daemon changes that add eager API calls, first-paint data requirements, polling, lazy resolvers, autocomplete, or send/submit flows.
- Authorization, membership, visibility, collaborator, participant, recipient, notification, delivery, unread, or fanout logic.
- SQL that loops over rows, calls helpers inside loops, builds one query per item, scans broad tables, or writes one row per unrelated object.
- Migrations, new indexes, `INSERT ... SELECT`, backfills, or table drops on large tables.

Do not use it for purely in-memory transformations or UI-only rendering that cannot change API shape, storage access, polling, or write behavior.

## Implementation And Review Workflow

1. Identify the operation: first paint, explicit action, autocomplete, send/submit, list/detail/search, import/export, realtime update, background job, admin job, or migration.
2. Identify the cost driver: requested IDs, page size, candidate scan limit, visible window, selected objects, job scope, workspace/org scope, table rows, or migration backfill size.
3. Reject designs whose cost grows with unrelated data. Examples: all workspace records to check a few requested IDs, all owners to show limited candidates, all historical rows to compute optional first-paint state, or read paths that perform cleanup writes.
4. Prefer bounded batch queries, owner fast-paths, lazy intent resolvers, unique constraints, and grouped SQL.
5. Add query-count or SQL-shape tests when the risk is runtime amplification.
6. For migrations, separate runtime safety from deploy-time cost and audit TiDB DDL/backfill behavior.

## Implementation Rules

- Before writing database code, state the request-bound or product-limit-bound set the query depends on.
- Do not call broad visibility, membership, authorization, lookup, hydration, participant, collaborator, owner, message, source, search, or list loaders from narrow read/write paths without proving the bound.
- Do not reuse a helper with hidden broad enumeration or read-path writes just because it already exists. Inline or split a bounded helper when semantics differ.
- Keep unrelated cleanup out of read paths; report cleanup debt separately if it is discovered.
- Treat workspace-wide, org-wide, or table-wide work as acceptable only when the operation is explicitly scoped that way and has a product, job, pagination, or migration rollout bound.

## Runtime Red Flags

- A list endpoint calls a helper once per returned row.
- A first-paint path precomputes optional action state, badges, eligibility, permissions, counters, notifications, or candidate lists.
- A narrow operation enumerates workspace-visible or org-visible objects, histories, owners, members, participants, recipients, sources, or search results.
- A read-only endpoint calls cleanup helpers, creates authorization rows, or writes default/empty rows.
- Deduplication preloads all existing rows instead of relying on a unique key or requested IDs.
- SQL uses `LIMIT K` before filtering unauthorized candidates when the product expects K authorized results.
- A client change adds broad eager API calls or polling to render optional UI state.

## Preferred Patterns

- **Requested IDs first**: build a CTE from the request and join outward.
- **Bounded lists**: enforce page size, candidate scan limit, or job scope before expensive hydration and authorization.
- **Batch authorization and hydration**: collect relation keys such as `(scopeType, scopeID, memberID)` or object IDs, then check/load in one or a few queries.
- **Lazy optional state**: resolve optional eligibility, counters, candidates, or action state only on explicit intent, selection, menu open, send/submit, or realtime delta.
- **Fast-path defaults**: owner/local/self cases should not create empty rows or perform People/membership lookups when the rule is deterministic.
- **Sparse writes**: write rows only for explicit targets or changed facts; avoid materializing defaults for every possible recipient/object/scope.
- **No read-path cleanup**: filter expired/inactive facts in reads; cleanup belongs to mutation or maintenance paths.

## Migration And Rollout

For TiDB migrations, review deploy-time cost separately from request-time cost:

- Read `specs/storage/storage.md`, `docs/tech_design.md`, and any closer `internal/tidb/AGENTS.md`.
- Check table sizes and existing indexes before adding indexes or backfills on hot tables.
- Audit `INSERT ... SELECT`, `GROUP BY`, backfill, and `DROP TABLE` steps for large-table impact.
- Decide explicitly whether the migration should be single-step, phased, chunked, or documented as safe.
- Verify fresh database and branch-upgraded database paths when migrations may have changed during review.

## Verification

- For Go runtime paths, prefer integration tests with a counting `coredb.DB` / `coredb.Tx` wrapper or SQL-shape assertions.
- Compare one item vs many items and assert queries stay constant or grow by a small fixed number.
- For frontend first-paint risks, add wiring tests that prove broad/lazy endpoints are not called until intent.
- For migration changes, run migration tests and document any external TiDB/table-size assumptions.

## Reporting Existing Risks

When the current task exposes an existing amplification risk that is outside scope, mention it in the handoff or review:

- The code path and trigger.
- The unrelated data that drives cost.
- Whether it affects first paint, explicit action, send, background job, or deploy.
- A bounded direction for a later fix.

## Common Mistakes

- Treating "bounded by current data" as safe without naming the bound. The bound must be tied to the request or product limit.
- Sharing helper code whose hidden behavior does broad enumeration or writes on reads.
- Optimizing the autocomplete path but leaving the send path as the true unbounded authorization path.
- Adding a cache instead of removing a broad query. Cache only after the underlying query shape is acceptable.