oma-db

어떤 스킬인가요?

Design schemas and migrations, tune queries, or plan vector

설치 방법

프로젝트 폴더에서 아래 명령어를 실행하고, 설치할 에이전트를 선택하세요.

설치 명령을 직접 실행해야 적용됩니다. 지원 에이전트와 필요한 권한·라이선스는 제작자의 안내를 확인하세요.

스킬 원문 파일

# Experiment Ledger

Load when comparing an actual hypothesis with a baseline or another approach. Routine implementation, formatting, and every failed check do not require experiment bookkeeping.

## Location and ownership

Use `../runtime/memory-protocol.md` for the configured coordination store. The default is `.agents/state/memories/experiment-ledger-{sessionId}.md`. The coordinator maintains the shared ledger; parallel agents write task/run-scoped results for the coordinator to merge, avoiding concurrent edits to the same table. Reuse an existing experiment artifact when it already carries the evidence.

## Record a comparison

1. State the hypothesis, affected scope, baseline revision, and success criteria before changing behavior.
2. Capture comparable baseline and candidate measurements as described in `quality-score.md`. Preserve required binary checks and raw evidence paths.
3. Record the decision and reason: retain, repair, discard, or inconclusive. A missing or noisy measurement is not a loss. Do not rank unrelated tasks or agents by average score delta.
4. If discarding, remove only changes owned by that experiment after inspecting the diff. Preserve user edits, other agents' work, and useful failure evidence.

Suggested fields, not a parsed schema:

| Experiment / task / run | Hypothesis | Baseline evidence | Candidate evidence | Required checks | Decision and reason | Changed paths |
|---|---|---|---|---|---|---|
| IDs from active run | Specific mechanism | Revision and report | Revision and report | pass / fail / missing | retain / repair / discard / inconclusive | Owned files |

Keep units and comparison criteria beside metric deltas. A scalar composite is optional only when the project already defines and computes it; there is no OMA default formula.

## End of an experiment

Summarize the selected approach, unresolved limits, and evidence. A discarded attempt becomes a lesson only when its cause and reusable prevention are understood (`../core/lessons-learned.md`). Do not generate lessons or modify canonical skills from a numeric threshold.

The ledger records work; it does not grant more retries, spending, commits, or permission to remove workspaces. Those follow the active task budget and `../core/execution-policy.md`.

# Hypothesis Exploration

Load when the user requests alternative approaches, or when the same issue persists after repeated relevant recovery and a different mechanism is worth testing. A failed check count is a prompt to reassess the cause, not an unconditional launch signal. Do not activate for an obvious fix or a noisy metric delta.

## Scope and budget

Use the task's existing aggregate attempt and cost budget. Each hypothesis consumes an attempt; exploration never replenishes retries. For a comparison round, reserve all intended attempts before starting. If the existing workflow requires a multi-candidate round and fewer than two attempts remain, preserve evidence and report the unresolved result instead of starting a partial round. Explicit user, runtime, and workflow limits take precedence; this guide does not add turn quotas or file-count caps.

## Procedure

1. Inspect prior failure evidence. State distinct causal hypotheses and the smallest experiment that distinguishes them. Use only as many alternatives as the uncertainty and budget justify; the active workflow may specify the candidate count.
2. Identify ownership, baseline revision, required checks, comparison metrics, and artifact paths before editing. Include tests and related files needed to evaluate the mechanism; a fixed file limit must not hide impact.
3. Isolate alternatives. In authorized multi-agent work, use separate prepared workspaces and preserve the plan task ID and unique run ID in each result. In sequential work, preserve a baseline and apply only the experiment's scoped diff. Do not use a blanket stash, reset, checkout, or cleanup against a workspace with unrelated changes.
4. Run relevant checks and comparable measurements under `quality-score.md`. Missing evidence remains missing; a candidate with failing required checks cannot win through a higher aggregate score.
5. Select a candidate only when it satisfies the required behavior and comparison criteria. Apply its owned changes to the target, then refresh affected verification on the integrated result. Preserve diagnostic evidence from other candidates; do not delete unrelated work or an active workspace.
6. Record the comparison and decision in `experiment-ledger.md`. If no candidate resolves the issue, report the evidence and remaining limitation. Continue only within the existing recovery budget and authorized scope.

## Workflow integration

`/work`, `/orchestrate`, and `/ultrawork` retain their dispatch, required review, and recovery limits. They call this procedure after reassessing a repeated failure, then re-evaluate the failed gate using actual acceptance evidence. This guide does not start a workflow or delegate work by itself.

Use `../runtime/result-contract.md` for task/run/claim identity and `../core/execution-policy.md` for authorization, clarification, and completion.

# Quality Measurements

Load when the task or active workflow needs a measured baseline or experiment comparison with defined metrics. The mere presence of tests or lint does not activate a scoring phase. Ordinary verification uses the applicable checks directly.

## Preserve independent acceptance gates

Tests, authorization/security requirements, and project acceptance criteria remain independent gates. A performance improvement cannot offset a correctness or security failure. Do not assign default weights, convert checklist completion into a security score, or trigger rollback from an arbitrary grade.

<!-- oma-docs:ignore-start -->
OMA does not implement a loader for `.agents/config/quality-score.yaml` or a universal composite scorer. If a project already provides a scoring command, record its formula, inputs, applicability, and output; do not infer configuration support from a sample path. A project composite may supplement the evidence but cannot waive mandatory checks.
<!-- oma-docs:ignore-end -->

## Measure a comparable baseline

1. Define the behavior, metric, units, direction, scope, and acceptance threshold from the task or project. Use the same command, dataset, environment, and measurement method before and after.
2. Reuse still-current verification artifacts. When new measurements are needed, capture the command's exit status and structured output (JSON, JUnit, SARIF, LCOV, or project equivalent). Do not infer totals from truncated console text.
3. Record the artifact path and revision/run identity with the result. Mark unavailable measurements as missing with a reason; estimates are labeled and excluded from measured comparisons.
4. Compare each applicable measure and check result. For noisy metrics, use the project's sampling/tolerance method; a single timing sample does not prove a regression.
5. Keep a change when it meets required behavior and comparison criteria. Investigate a regression before deciding whether to repair or discard the experiment; preserve unrelated work. Record a material tradeoff when requirements permit it.

For an actual experiment, use `experiment-ledger.md`. Ordinary test passes do not require a ledger row or a new measurement at every phase. Re-measure only affected metrics after relevant changes, failures, or uncertainty.

## Evidence record

Use existing result artifacts or a compact table such as:

| Measure | Baseline | Candidate | Method / evidence | Verdict |
|---|---|---|---|---|
| Required tests | pass | pass | exact command, exit status, report paths | pass |
| Request p95 latency | measured ms | measured ms | same workload, environment, sample method | within target / regression / inconclusive |
| Security finding | finding ID | remediation status | reproduction and verification paths | resolved / unresolved |

Report missing evidence without inventing a score. Authorization, builds, verification, and completion follow `../core/execution-policy.md`.

# API Contracts

This directory owns the optional contract template, not generated API specifications. Reuse the project's existing OpenAPI, schema, or other authoritative contract before creating a parallel Markdown specification.

## When a contract artifact helps

Create or update a contract when a changed API boundary needs coordination between producers and consumers, or the task explicitly requests a specification. A scoped implementation against an existing contract does not require a PM task or a new document.

| Artifact | Location |
|---|---|
| Transient coordination contract | `.agents/results/api-contracts/{domain}.md` |
| Durable project specification, if no existing location applies | `docs/plans/contracts/{domain}.md` |
| Reusable format example | `template.md` in this directory |

Use task/session-specific domain names for independent runs that could otherwise overwrite the same transient file. Follow assigned artifact paths when supplied. Read and write with available file tools; no memory MCP is required.

## Authoring and use

1. Identify the authoritative schema and affected consumers. The assigned API owner can define the contract; PM involvement is useful for unresolved product requirements.
2. Document changed operations: method/path, input fields and validation, success/error schemas, authentication and authorization, and relevant compatibility requirements.
3. Share the contract path with affected assigned agents through the workflow's authorized coordination channel. Resolve incompatible expectations before dependent implementation. A separate approval ceremony is unnecessary when the existing contract and task already settle them.
4. Implement and verify producer/consumer compatibility with applicable schema checks, generated clients, or integration tests. If a contract changes, update its source and affected consumers within scope.

## Completion

The changed boundary is explicit, consumers can use it, and applicable compatibility checks support the result. Include pagination, rate limits, timestamp formats, and migration/deprecation behavior only when relevant. Do not invent JWT authentication, CRUD endpoints, a database, or error codes merely because the template includes an example.

Follow `../execution-policy.md` for authorization and verification. Transient artifacts do not belong in this source directory; durable specifications follow the repository's tracking policy.

# {Domain} API Contract

> Author: {assigned-owner} | Session: {session-id} | Date: {YYYY-MM-DD}

Use only the operations and fields needed by the actual contract. The CRUD, UUID, ownership, and pagination examples below are placeholders, not required architecture.

## Base URL
`/api/{domain}`

## Authentication
{Actual authentication scheme, public operations, and per-operation authorization rules. Preserve the project contract; do not assume JWT.}

---

## Endpoints

### POST /api/{domain}
- **Description**: Create a new {resource}
- **Auth**: Required
- **Request Body**:
  ```json
  {
    "field1": "string (required)",
    "field2": "number (optional, default: 0)"
  }
  ```
- **Response 201**:
  ```json
  {
    "id": "uuid",
    "field1": "string",
    "field2": 0,
    "created_at": "2024-01-01T00:00:00Z"
  }
  ```
- **Errors**: 401, 422

### GET /api/{domain}
- **Description**: List {resources} (paginated)
- **Auth**: Required
- **Query Params**: `page` (default: 1), `size` (default: 20)
- **Response 200**:
  ```json
  {
    "items": [...],
    "total": 100,
    "page": 1,
    "size": 20
  }
  ```

### GET /api/{domain}/{id}
- **Description**: Get single {resource}
- **Auth**: Required
- **Response 200**: Single resource object
- **Errors**: 401, 404

### PATCH /api/{domain}/{id}
- **Description**: Update {resource}
- **Auth**: Required (owner only)
- **Request Body**: Partial update fields
- **Response 200**: Updated resource object
- **Errors**: 401, 403, 404, 422

### DELETE /api/{domain}/{id}
- **Description**: Delete {resource}
- **Auth**: Required (owner only)
- **Response 204**: No content
- **Errors**: 401, 403, 404

---

## Data Models

### {Resource}
| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | UUID | auto | Primary key |
| field1 | string | yes | Description |
| field2 | number | no | Default: 0 |
| user_id | UUID | auto | Owner (FK to users) |
| created_at | datetime | auto | ISO 8601 |
| updated_at | datetime | auto | ISO 8601 |

---

## Notes
- Pagination: offset-based (page/size)
- Sorting: `?sort=created_at&order=desc` (optional)
- Filtering: `?field1=value` (optional)

# Clarification Protocol

Apply [Execution Policy](execution-policy.md) before deciding whether to ask.

| Situation | Action |
|-----------|--------|
| Goal and authorization are clear; routine details remain | Use repository conventions, state consequential assumptions, proceed |
| A preference could improve the result | Ask one concise optional question and continue independent work |
| Missing information materially changes correctness or scope | Ask the specific question; pause only the dependent work |
| An action requires authorization not already given | Prepare the reviewable result, explain the action and reason, then ask |

Check prior messages, project configuration, and existing behavior before asking. The presence of tradeoffs, an existing-code conflict, or subjective wording alone is not a blocker. Do not add authentication, a database, or a framework solely because a generic checklist has a default.

## Question transport

- When clarification, a preference, or approval is needed, prefer the runtime's asynchronous user-question tool when available and permitted for that purpose. Otherwise use an available question tool or one concise plain-text question, respecting the runtime's mode and tool restrictions.
- Ask one focused question at a time. For approval, first present the concrete result and explain which action needs authorization and why; the question must be understandable on its own.
- Continue independent, authorized work while an answer is pending. Pause only the work that depends on required information or approval.
- Silence, elapsed time, and a preselected option are not approval. Keep required questions pending until the user explicitly answers; do not repeat an unanswered question through multiple channels.
- For an optional preference, allow a reasonable opportunity to answer, then state an assumption and proceed if necessary. An optional question must not become an approval gate.
- Vendor execution protocols map this policy to tools exposed in the current session; do not assume every vendor provides the same tool name.

Subagents report the exact missing fact and any independent work completed in a structured `blocked` or `partial` result. The coordinator answers from existing context where possible and asks the user only when necessary.

# Common Code Quality Checklist

Use applicable sections for a cross-domain review. Check the changed behavior and affected boundaries; this list does not expand the task or override project checks. Authorization and verification scope follow `execution-policy.md`.

## Correctness and maintenance
- [ ] Behavior matches acceptance criteria and existing contracts.
- [ ] Names and structure follow the project; complexity does not obscure the changed behavior.
- [ ] No required behavior is left as a stub or unresolved task-created TODO. Existing tracked debt does not block unrelated work.
- [ ] Generated artifacts are updated through the project's generator when required.

## Error handling
- [ ] Failure paths propagate to an appropriate handler or error boundary; no swallowed rejection or silent data loss.
- [ ] User-facing errors are actionable without exposing internal or sensitive data.
- [ ] Recovery, cancellation, and cleanup work where the change depends on them.

## Security
- [ ] No secrets in code, logs, error messages, or staged files.
- [ ] Untrusted input is validated and handled safely at SQL, shell, and HTML boundaries.
- [ ] Protected operations enforce authentication and authorization at the relevant trust boundary.

## Verification
- [ ] Tests assert affected behavior and relevant failure or boundary cases.
- [ ] Required project checks pass, or missing evidence and its impact are reported.
- [ ] For changes where automated tests add little value, applicable inspection or static checks support the result.

## Change hygiene
- [ ] Changes stay within scope and preserve unrelated work.
- [ ] When committing is requested, staging follows repository rules, including whether generated artifacts are tracked.

# Context Budget

Keep the context relevant to the current task. Prefer scoped search and reads for large files; read a whole file when its size or the review scope makes that useful. Re-read changed content when needed to verify current behavior.

## Measure actual loading

`bun scripts/measure-skill-context.ts` reports file-size estimates using UTF-8 bytes / 4. It is not an Astra tokenizer or a live usage measurement. The routed tier is the entry skill; resource tiers are comparison scenarios, not instructions to preload those files. Generated vendor aliases are not independent prompt copies.

Measure the assembled prompt separately when changing injection. Include the owning entry, runtime contracts, task instructions, and any references actually supplied. The runtime retains a required entry even if it exceeds its soft budget and reports the overrun. Reduce that entry or select a narrower skill; do not silently drop its contract.

Use `oma skill audit` to find routing overlap and unusually large entries. Counts and thresholds are diagnostics, not proof of task utility. Keep measured summaries current rather than copying fixed claims about which skills exceed a limit.

## During a task

- Process bulk logs, datasets, and manifests through tools; return relevant excerpts, counts, and artifact paths.
- Load one platform or operation at a time. Error playbooks, examples, and experimental protocols remain conditional.
- Record important decisions and remaining work in durable state for long tasks. Do not maintain a separate file-read ledger for a trivial edit.
- Before compaction or a restart, save completed work, remaining acceptance criteria, relevant paths, verification status, and material assumptions.
- Continue from that checkpoint. A turn estimate or elapsed time is not completion and does not require renewed approval.
- Restart an agent only for observed loss of useful context or stalled progress. Preserve its partial result and avoid duplicating a live attempt.

Authorization and completion follow `execution-policy.md`; run/claim identity follows `../runtime/result-contract.md`.

# Context Loading

Read the routed skill once. Its references are an index, not a preload list.

## Selecting context

- Start with the task, acceptance criteria, and the owning skill's SKILL.md.
- Read a supporting document only when its stated trigger applies. Existing project conventions take precedence over starter examples.
- Load execution-protocol.md when its selected operation needs command, schema, or recovery detail; its existence alone is not a trigger.
- Load only the selected framework or platform reference. Generated stack/ files describe the project; variants/ are starter material for /stack-set, not mandatory reading for existing-code edits.
- Consult relevant checklist sections for unresolved verification requirements. Read an error playbook only for the observed failure.
- Read difficulty-guide.md for decomposition decisions, not before every task. Difficulty does not activate extra skills or full checklists.
- Runtime transport, authorization, and result contracts are injected once by CLI dispatch. Native agents read those contracts if not already supplied.

## Conditional shared resources

| Resource | Load when |
|---|---|
| `../conditional/quality-score.md` | The task needs a defined baseline or experiment comparison |
| `../conditional/experiment-ledger.md` | Recording an actual experiment |
| `../conditional/exploration-loop.md` | Repeated recovery fails on the same issue and alternative mechanisms merit testing within budget, or the user requests exploration |
| `common-checklist.md` | A broad cross-domain review needs its applicable checks |
| `context-budget.md` | Managing a long task or diagnosing context overhead |
| `../runtime/memory-protocol.md` | Coordinating agents or resuming durable task state |

## Runtime selection

The graph records references, not permission to load them. The context loader injects the owning entry skill and defers its supporting references. Cross-routes to another specialist do not inject that specialist's body.

Difficulty selects a soft size budget. If the entry skill exceeds it, retain the entry and report the overrun. Do not replace it with smaller unrelated documents. Explicitly requested supporting references are checked against the graph and loaded within the remaining budget; deferred paths remain available for scoped reads.

## Subagent prompts

Pass the task ID, scope, acceptance criteria, material constraints, and relevant artifact paths. Identify the owning skill by path; do not paste its body if the runtime injects it. Pass a reference or guide section only when this task needs it. Preserve injected run/claim identity. Use `prompt-structure.md` when composing an unfamiliar handoff, not as another mandatory preload.

# Task Decomposition

Use this guide when scope or dependencies make the execution approach unclear. Routine changes do not need a difficulty report or a separate planning phase.

## Choose the amount of planning

| Task characteristics | Approach |
|---|---|
| Clear change following an existing pattern | Inspect the affected behavior, implement, and run relevant checks |
| Several coupled changes or a material design choice | Identify dependencies and acceptance criteria; resolve the choice before dependent edits |
| Multiple independently verifiable outcomes or uncertain boundaries | Break work into deliverables, each with its own implementation and verification |

File counts and estimated turns are hints, not gates. Many repetitive file edits can be straightforward; a one-line authorization change can need careful regression testing. The CLI's Simple/Medium/Complex classifier selects a context size budget; it does not prescribe review depth or activate other agents.

## Decompose around behavior

- Use as many deliverables as dependencies require, without a fixed sprint count or turn quota.
- Keep tests and error handling with the behavior they verify. For authentication plus CRUD, verify authentication with its endpoints before integrating and testing protected CRUD paths.
- Use the project's declared checks and `test-approach.md` where applicable. A broad audit may use relevant sections of `common-checklist.md`; complexity alone does not require every check.
- Delegate only when authorized and when independent work can proceed. Follow `skill-routing.md` for ownership, not a compulsory agent chain.

## Adjust during execution

If new dependencies emerge, update the remaining work and continue within the authorized scope. If the task is simpler than expected, finish without preserving unnecessary phases. For long tasks, checkpoint completed work and remaining evidence as described in `context-budget.md`.

Completion and authorization follow `execution-policy.md`. A turn estimate does not end the task or require renewed approval.

# Execution Policy

This is the shared OMA policy for authorization, clarification, verification, and completion. Workflows define steps; skills define domain methods; vendor protocols define transport. They refer here instead of defining different stop/approval rules. System and developer instructions and the user's current request take precedence over OMA defaults.

## Authorization and clarification

- Carry the user's requested work through implementation. Existing authorization persists; a plan review or proposed fix does not require another approval when that work is already authorized.
- Resolve routine, reversible implementation choices from repository conventions. State material assumptions and continue independent work while a question is pending.
- Ask only for information that changes the outcome or for an action outside the authorized scope. Pause only the dependent action. Before requesting new approval, prepare the concrete result for review.
- Use the [Clarification Protocol](clarification-protocol.md) for question transport: prefer an available asynchronous question tool and keep independent work moving. Never treat silence, elapsed time, or a default selection as approval.
- Do not infer permission to send messages, publish, spend beyond an agreed budget, destroy data, or expand scope. Conversely, do not request that permission again when explicitly granted.
- Never build, compile, bundle, or package software until the user explicitly asks for a build. Type checking without emission and relevant tests do not authorize a build.

## Resource budgets, cancellation, and monitoring

- Before expensive batches, downloads, installations, or long-running subprocesses, record the execution deadline, parallelism, attempt/cost limits, and available host resource limits. Use existing configured limits and a small reversible probe when costs are unknown; preserve the requested scope and do not invent a new approval step.
- Apply one absolute deadline to preparation, installation, execution, retries, and verification, reserving time for shutdown. Propagate the remaining budget to children and retain it across resume; a new process, task ID, or retry does not reset it. CPU, RAM, and storage limits are enforced only where the host or provider supports them. Metadata alone is not enforcement.
- User cancellation takes precedence over completion gates and recovery. Check cancellation and the remaining deadline before every dispatch, review, verification, recovery action, and resumed admission. On cancellation or deadline exhaustion, stop admitting new work, agents, retries, failover, reviews, and verification. Allow only bounded shutdown, termination checks, and an accurate partial handoff. Do not repair a gate or replay work to keep a cancelled run alive.
- Record resources as they are created: run identity, process identity/group, provider or container IDs, and generated file/cache paths. Stop only resources whose ownership is established. Request cancellation through the host API for native agents; use verified process-tree termination for CLI-owned workers. Retain ownership records until termination is confirmed and report any surviving resources or unsupported cancellation capability.
- Stopping computation does not authorize deletion. Preserve logs, results, worktrees, caches, images, volumes, credentials, and user data unless deletion is requested or an explicit retention policy applies. Check shared use before removing a registered resource; never replace scoped cleanup with a global prune or name-only process kill.
- Prefer completion notifications and host-supported waits. When polling is necessary, designate one coordinator, increase the interval within the remaining deadline, and read only changed status or short log tails. Keep cancellation delivery independent of the polling interval; avoid duplicate watchers and repeated unchanged updates.

## Verification and completion

- Verification belongs to the project's local CI and push hooks. Do not run test suites, type checks, linters, or builds on your own initiative; run a check only when the user or the active workflow explicitly requires it.
- Add a regression test with a bug fix. Do not invent implementation-mirroring tests for prose or configuration edits.
- Prefer hermetic tests. A test that needs Docker, containers (including testcontainers), or another external service is a last resort: isolate it in the project's opt-in integration task and keep it out of default hooks and required gates.
- Use the shared [result contract](../runtime/result-contract.md) for agent handoffs. Process exit zero and a Markdown file alone are not verified completion.
- Report completed, partial, blocked, or failed accurately, with remaining work and verification limits. A gate failure means repair evidence/work within the existing scope and retry; it is not automatically a new permission requirement.

Repository check: `cli/platform/execution-policy.test.ts`.

# Lessons Learned

Consult a relevant lesson after an observed failure or when prior evidence matches the current task. This file defines lesson handling; it is not a mandatory startup read or a destination for automatic edits to installed skills.

## Capture a useful lesson

Record a lesson when a repeated failure, consequential incident, or requested retrospective exposes a reusable cause and prevention method. Fix and verify routine failures directly. An expected failing regression test, a clarification question, or an experiment with a lower score does not by itself require an RCA.

Use the configured coordination store's `lessons-{sessionId}.md` (default `.agents/state/memories/`), or link an existing incident/result artifact. Include only information that helps prevent recurrence:

- The affected session/task/run and evidence paths.
- Observed behavior and impact; separate facts from suspected causes.
- Relevant framework version, configuration, or operating conditions.
- The fix and checks that support it.
- The prevention action and when it applies.

If the cause is unknown, say so. Do not invent a lesson merely to close a session. If the user says not to repeat a mistake, apply that correction to ongoing work immediately; record the reusable part without delaying the fix for a template.

## Applying lessons

- Check applicability against the installed version and actual configuration. A fix for one framework or runtime is not a universal rule.
- Review generated migrations against intended schema changes; generator success alone does not establish correctness.
- For integration failures, compare the actual producer and consumer contract: field names, types, authentication, timestamps, and error responses.
- Verify a suspected lifecycle, hydration, or redirect defect with the failing path before prescribing a hook, wrapper, or address substitution.
- Review findings need evidence. A disagreement alone does not establish an error; a separate reviewer does not guarantee correctness. Select runtime checks when the behavior requires them and record any verification limits.

## Promoting and maintaining lessons

Recurring evidence may justify a change to an owning skill, checklist, or project rule. Within an authorized source-maintenance task, prepare the scoped change, preserve exact version/trigger conditions, and verify it. Otherwise retain a proposal in the session artifact; do not mutate installed definitions during an unrelated run.

Read an experiment ledger only when it exists and a result needs analysis. A negative delta is a candidate for investigation, not proof of a reusable lesson. Keep successful and failed cases when comparing a proposed rule. Archive or remove historical lessons only under the repository's retention policy or the user's direction.

Use `session-metrics.md` for optional session evidence and `execution-policy.md` for authorization and completion.

# Task Handoffs

Use this guide when composing a task for another agent or when the requested outcome is unclear. A simple direct request does not need to be rewritten into a template.

Include the information the recipient needs:

| Element | Content |
|---|---|
| Goal | Observable behavior or artifact to change |
| Context | Relevant paths, existing patterns, errors, and prior decisions |
| Constraints | Actual scope, compatibility, ownership, and authorization boundaries |
| Done when | Acceptance criteria and proportionate verification |

Infer routine details from the request and repository. Do not invent constraints or ask the user to fill headings. Ask only about a missing fact that materially changes the result; follow `execution-policy.md` and `clarification-protocol.md`.

For implementation, include affected tests or examples when they clarify the contract. For a bug, describe the failure and expected behavior. For verification, name applicable project checks; a build is included only when explicitly requested by the user.

For dispatched tasks, preserve injected task/run/claim identity and required artifact paths from `../runtime/result-contract.md`. Reference the owning skill rather than pasting instructions already supplied by the runtime. Ownership and dependencies matter more than a decorative report format.

# Session Evidence

Consult this guide for a requested retrospective, a substantive correction, or a review finding worth preserving. Routine work does not need a second log alongside existing progress and result artifacts.

## Record observations, not penalty scores

Do not assign points to questions, corrections, or review judgments. The former Clarification Debt (CD) and Evaluator Accuracy (EA) weights and thresholds are retired; they were prompt instructions, not CLI-computed metrics. Necessary clarification is not a failure, a user changing scope is not an agent error, and a disputed QA finding is not an established false positive.

When useful, record an event with session/task/run identity, an evidence path, observed impact, cause if known, and the action taken. Reuse the existing result or progress artifact. For a separate session summary, use the configured coordination store's `session-metrics-{sessionId}.md` (default `.agents/state/memories/`). Distinguish:

| Observation | Evidence to retain |
|---|---|
| Material clarification | Missing decision and how the answer changed dependent work |
| User scope change | New request and affected acceptance criteria |
| Agent mistake or rework | Requirement missed, affected behavior, correction, and verification |
| Blocked action | Exact missing input or authority and independent work completed |
| Review error or useful finding | Finding, reproduction or counterexample, adjudicated outcome, and impact |

A disputed finding stays unresolved until code, tests, or other evidence settle it. Judge severity by impact and exposure, not diff size. Keep useful catches, false positives, and missed defects separate; do not net them into an agent score or claim an accuracy rate without a defined labeled evaluation set.

## Respond to the cause

Correct the affected work and verify it. Pause only actions dependent on unresolved information or authorization, under `execution-policy.md`. Do not stop a session, trigger another agent, or require an RCA because a counter crossed a threshold.

For repeated failures, a consequential incident, or a user-requested retrospective, record the known cause and prevention evidence using `lessons-learned.md`. An ordinary failed check, including an expected RED test, does not require a separate RCA. Propose durable instruction changes only when evidence supports them; edit canonical definitions within an authorized source-maintenance task.

## Measurements and tooling

- Prefer observed task outcomes, relevant check results, retries, elapsed time, and recorded usage over inferred scores. State the measurement source and limits; progress updates are not a reliable turn counter, and checkpoint counts are not reset counts.
- `oma stats` reports the CLI's productivity and recorded usage/cost summaries. It does not parse this Markdown into CD or EA scores. Cost estimates are not billing receipts.
- `oma retro` groups recorded `gate.failed`, `blocker.raised`, and `decision.missing` events into harness suggestions. Those suggestions still need causal review; they do not automatically modify prompts.
- If the active workflow uses quality measurement or experiments, link the actual artifacts from `../conditional/quality-score.md` and `../conditional/experiment-ledger.md`. Do not fabricate missing measurements or mix them with conversational event counts.
- For model or prompt comparisons, use `../../oma-skill-creation/resources/prompt-evaluation.md`. Compare equivalent tasks and report correctness, interruptions, latency, and measured cost separately.

Keep existing historical logs as evidence. This guide neither schedules deletion nor imposes a retention deadline.

# Skill Routing

Use this map when selecting a specialist or coordinating an authorized multi-agent task. Route by the requested outcome and concrete dependencies, not a keyword alone. Choosing a skill does not start a workflow or authorize delegation.

## Context and ownership

The runtime exposes skill names and descriptions for discovery. Read the owning SKILL.md when the task matches its scope or the user invokes it, under the host's skill rules. Load supporting references only when needed (`context-loading.md`). Use one owning specialist for a scoped task; add a handoff only when it supplies a distinct needed capability.

| Requested outcome | Owning skill |
|---|---|
| Application API, server auth, service/data access logic | `oma-backend` |
| Schema, migration design, indexing, query tuning, database operations | `oma-db` |
| Web components, pages, forms, client state | `oma-frontend` |
| Mobile screens, widgets, platform integration | `oma-mobile` |
| System boundaries, architecture tradeoffs, ADR | `oma-architecture` |
| Diagnose and fix incorrect behavior | `oma-debug` |
| Review correctness, security, accessibility, performance | `oma-qa` |
| Visual system, DESIGN.md, redesign direction | `oma-design` |
| Requested ideation or alternative exploration | `oma-brainstorm` |
| Requirements, dependencies, acceptance criteria | `oma-pm` |
| Requested parallel specialist execution | `oma-orchestration` |
| Requested manual multi-agent coordination | `oma-coordination` |
| Git branches, commits, merges, repository history | `oma-scm` |

For other domains, select from the installed skill descriptions. Preserve explicit task ownership and vendor/model configuration.

## Dependencies

- Resolve an unsettled architecture or product decision before work that depends on it. Existing decisions do not need to pass through architecture, brainstorming, or PM again.
- Backend and client work may proceed independently when their shared API contract is usable. If it is missing, settle that boundary with the relevant owners; use `api-contracts/README.md` when a contract artifact is needed.
- Review can start on a completed, reviewable portion while independent implementation continues. Do not review an unstable artifact as final evidence.
- A bug found during implementation can be fixed by its owner. Add debug assistance when diagnosis warrants it; there is no required implementation-to-debug chain.
- For a cross-domain defect, identify the failing contract and responsible owner from evidence. An API mismatch does not automatically mean the backend must change.

## Dispatch and completion

Use `vendor-detection.md` when dispatch details are unresolved. Parallelize only independent work within the authorized task and available runtime. Each dispatched task needs ownership, acceptance criteria, relevant context, and the runtime's result contract.

A difficulty estimate does not impose fixed per-agent turn limits. Respect explicit user/runtime budgets and preserve partial progress when a real limit is reached. Completion and unresolved decisions follow `execution-policy.md`.

# Per-Task Test Approach & TDD Evidence

Opt-in, per-task test strategy carried in the PM plan (`plan-{sessionId}.json`).
PM assigns it (see `oma-pm/resources/execution-protocol.md` Step 3); implementation
agents honor it; QA and Ultrawork gates verify evidence **only** for tasks marked `tdd`.

## Approaches

| `test_approach` | Meaning | Agent obligation |
|---|---|---|
| `tdd` | Deterministic, high-risk behavior (validation, authorization, state transitions, calculations, error handling) | Write and run the focused test **before** the production change (RED), make the minimal change (GREEN), refactor only if needed. Record a `TDD_EVIDENCE` block in the result file. |
| `test_after` | Automated tests required, but a useful isolated RED state is impractical | Write tests with/after the implementation. No evidence block required. |
| `not_applicable` | Automated tests inappropriate | Perform the plan's `alternative_verification` and report its outcome. Plan must carry `test_approach_rationale`. |

Tasks without a `test_approach` field behave as today (tests per the agent's
normal protocol). Refactor tasks never use `tdd` — they keep the
characterization-test safety net (`oma-refactor`).

## Coverage applicability rule

`test_approach` does not silently waive coverage. Use the project's declared
baseline, changed-code target, or task-specific risk target when coverage is
applicable. When it is not, the plan must state risk-focused tests or an
alternative verification method and its limits. Do not impose a global numeric
floor on projects that have not defined one.

## TDD_EVIDENCE block format

Append to the task/run-scoped result report, one entry per `tdd` task:

```
TDD_EVIDENCE:
- task: task-2
  test_command: bun test src/services/discount.test.ts
  red: "expected 400, received 200" (before implementation)
  green: 12 pass, 0 fail (after implementation)
```

Requirements (enforced by `oma verify agent <agent>` → "TDD Evidence" check):

1. Block starts with the literal marker `TDD_EVIDENCE:`
2. Every `tdd` task id from the plan appears in the block
3. At least one `red:` entry (the observed failure before the change) and one
   `green:` entry (the passing result after the change)

A task may opt out with `tdd_evidence_required: false` in the plan (e.g., the
RED state is demonstrated in a linked CI run instead); the rationale belongs in
the task description.

## Debug parity

Debug regression tests follow the same discipline where feasible: run the
regression test before applying the fix, record the failing output (RED) and
the post-fix pass (GREEN) in the bug report / result file.

# Vendor Detection Protocol

When executing a workflow, determine your runtime environment using this priority order.
Then resolve the target vendor for each agent from `.agents/oma-config.cue` or `.agents/oma-config.yaml`. Apply project-local configuration overlays. With `model_preset: free`, use `oma agent spawn` so the FreeLLMAPI route is applied. Otherwise explicit `agents:` model overrides take priority. With `model_preset: auto` (the default for new installs), unconfigured agents follow the current runtime's native agent definitions and model settings; do not substitute a fixed vendor preset or inject a model/effort override. When the runtime cannot be detected, use `default_cli` (or `claude` when omitted). Fixed built-in and custom presets continue to resolve model slugs (`<owner>/<slug>`) to their owning vendors. See `web/docs/guide/per-agent-models.md` for details.

Important:
- Do **not** choose one spawn strategy for the entire workflow based only on the main runtime vendor.
- Decide per agent:
  - `current_runtime_vendor`
  - `target_vendor_for_agent`
  - whether that exact runtime can invoke that target vendor natively
- If native invocation is not available for that agent, fall back to `oma agent spawn`.

## Identify the current runtime

Use explicit host identity and runtime configuration first. Inspect the native dispatch capability actually exposed in the session. Shared tool names such as `apply_patch`, an `@` syntax, or a skill-directory path do not uniquely identify a vendor.

If identity or native dispatch is unresolved, use the configured fallback; do not guess that the runtime is Codex or Antigravity from a generic tool. For OpenCode, an explicitly identified session with native `task` can dispatch through it even when `apply_patch` is also available.

## Vendor-Specific Spawn Methods

| Vendor | Spawn Method | Result Handling |
|:---|:---|:---|
| Claude Code | `Agent` tool with `.claude/agents/{name}.md` | Return value (foreground) or completion notification (`run_in_background=true`) |
| OpenCode | Same session: native `task` tool with `subagent_type: {agent-id}` (the only path that shows as a native child task in the active OpenCode GUI/TUI). External fallback: `oma agent spawn`, which creates a temporary primary wrapper that delegates to the `mode: subagent` agent — `opencode run --agent {subagent}` alone is rejected and falls back to the default agent. | Native task return / result file poll |
| Kilo Code | Same as OpenCode (Kilo is an OpenCode fork): native `task` tool with `subagent_type: {agent-id}` for `.kilo/agents/{name}.md`; external fallback `oma agent spawn` uses the same temporary primary wrapper (`kilo run --agent {subagent}` alone is rejected the same way). Kilo sets `KILO=1` in tool subprocesses. | Native task return / result file poll |
| Codex CLI | Current-session native dispatch with custom agents in `.codex/agents/{name}.toml` when available, otherwise `oma agent spawn`. A role file without `model` runs on the parent's model; spawn `docs-curator` and `research-explorer` with the fastest model the spawn tool lists as available, and leave `model` unset for other roles and for files that pin one. Never invent a model name. | JSON output |
| Antigravity | Prefer `oma agent spawn` unless a native role-subagent path is explicitly verified for the target vendor | MCP memory poll |
| CLI Fallback | `oma agent spawn {agent} {prompt} {session} -w {workspace}` | Result file poll |

## Dispatch Rule

For each agent:

1. Resolve `target_vendor_for_agent` from config
2. If `target_vendor_for_agent === current_runtime_vendor` and that runtime has a verified native role-subagent path for that vendor, use the vendor variant agent definition
3. Otherwise, use `oma agent spawn`

Example:
- Runtime: Claude Code
- Resolved models: `frontend` → `anthropic/…`, `backend` → `anthropic/…`, `qa` → `openai/…` (owning vendors: claude, claude, codex)
- Result:
  - `frontend` -> native Claude subagent
  - `backend` -> native Claude subagent
  - `qa` -> external Codex spawn via `oma agent spawn`

### OpenCode specifics

- If `current_runtime_vendor == opencode` and `target_vendor_for_agent == opencode` and the `task` tool exists, use native `task(subagent_type: "<agent-id>")`.
- Do **not** use `oma agent spawn` for same-session OpenCode subagents — it is an external fallback and will not appear as a native child task in the active OpenCode GUI/TUI.
- Before using the `oma agent spawn` fallback, confirm that native same-runtime dispatch is genuinely unavailable.
- Kilo Code follows the same rules with `kilo` in place of `opencode`.

# Memory Protocol (CLI Mode)

> **Note**: This file documents the default (direct-file) memory protocol. Vendor-specific execution protocols are injected automatically by `oma agent spawn` from `execution-protocols/{vendor}.md`.

When running as a CLI subagent, follow this protocol.

## Tool Reference

Coordination artifacts are read and written as plain files using your native file tools.
Tool names remain configurable via `mcp.json → memoryConfig.tools`; this file
memory contract is independent of any code-intelligence provider.
- `[READ]` → default: `Read`
- `[WRITE]` → default: `Write`
- `[EDIT]` → default: `Edit`
- `[LIST]` → default: directory listing (e.g. `ls` / `list_dir`)
- `[DELETE]` → default: file delete (e.g. `rm`)

Memory base path is configurable via `memoryConfig.basePath` (default: `.agents/state/memories`). Create the directory if it does not yet exist.

> **CRITICAL**: Do NOT use Serena's MCP `write_memory` / `read_memory` for workflow state or result reports. Use file tools (`Read`/`Write`/`Edit`): session and progress notes go under `{memoryConfig.basePath}/`, and `result-*.md` reports always go under the project-root `.agents/results/`. Create `.agents/results/` if it does not yet exist. Verification gates check these durable files and their receipt bindings.

---

## Path Resolution (CRITICAL)

Use the injected `claimPath` for structured result claims and do not rename or
move it. Keep existing flat session artifacts for consumer compatibility. Add
task and run identity to human-readable reports:

```
{memoryConfig.basePath}/
  orchestrator-session-{sessionId}.md
  task-board-{sessionId}.md
  progress-{agentId}-{taskId}-{runId}-{sessionId}.md
  lessons-{sessionId}.md
  experiment-ledger-{sessionId}.md

.agents/results/
  result-{agentId}-{taskId}-{runId}-{sessionId}.md
```

`taskId` and `runId` are required for plan-based work. A manual run may use a
generated local run ID. Never use workspace or agent ID alone as artifact
identity. Receipts remain in `.agents/state/agent-runs/` and retain their
existing structured schema. Reports are independent of `memoryConfig.basePath`.

Existing reports under `.agents/state/memories/`, the configured memory base, or `.serena/memories/` remain read fallback until migration. Write new reports only to `.agents/results/`, preserving task/run/session filenames. Do not move structured claims or manually rewrite verification receipts when relocating reports.

## On Start

1. `[READ]("task-board-{sessionId}.md")` to confirm your assigned task
2. `[WRITE]("progress-{agentId}-{taskId}-{runId}-{sessionId}.md", initial progress entry)` with Turn 1 status

## During Execution

- Every 3-5 turns: `[EDIT]("progress-{agentId}-{taskId}-{runId}-{sessionId}.md")` to append a new turn entry
- Include: action taken, current status, files created/modified

## On Completion

- `[WRITE](".agents/results/result-{agentId}-{taskId}-{runId}-{sessionId}.md")` with final result including:
  - Status: `completed`, `partial`, `blocked`, or `failed`
  - Summary of work done
  - Files created/modified
  - Acceptance criteria checklist

## On Failure

- Still create the run-scoped result with an accurate status.
- Include detailed error description and what remains incomplete

---

## Experiment Tracking (Optional Extension)

When a task compares an actual experiment, use `../conditional/experiment-ledger.md` for recording and `../conditional/quality-score.md` for comparable measurements. Routine verification does not require a ledger.

Use `experiment-ledger-{sessionId}.md` under the configured coordination store. The coordinator merges task/run-scoped agent results into this shared artifact; parallel workers do not append concurrently. Link baseline and candidate evidence, required check results, the decision and reason, and owned paths. No composite score, fixed row layout, or agent ranking is required.

---

## Example with Default Tools (Direct File)

```
# On Start
Read(".agents/state/memories/task-board-session-20260405-100835.md")
Write(".agents/state/memories/progress-backend-task-api-run-01-session-20260405-100835.md", initial_content)

# During Execution
Edit(".agents/state/memories/progress-backend-task-api-run-01-session-20260405-100835.md", turn_update)

# On Completion
Write(".agents/results/result-backend-task-api-run-01-session-20260405-100835.md", final_result)
```

## Example with Custom Tools

If `memoryConfig.tools` is configured differently:

```json
{
  "memoryConfig": {
    "tools": {
      "read": "fs_read",
      "write": "fs_write",
      "edit": "fs_patch"
    }
  }
}
```

Then use:
```python
fs_read("task-board-session-20260405-100835.md")
fs_write("progress-backend-task-api-run-01-session-20260405-100835.md", initial_content)
fs_patch("progress-backend-task-api-run-01-session-20260405-100835.md", turn_update)
fs_write(".agents/results/result-backend-task-api-run-01-session-20260405-100835.md", final_result)
```

# Agent Result Contract

`oma agent spawn` and `oma agent parallel` create a unique run and inject its result path. Use `--task-id` on spawn to match the session plan. A process exiting zero without a valid claim is `partial`, and a failed process is `failed`.

Native agents use the same contract:

1. Define the acceptance contract in the session plan (see below), then run `oma agent begin <agent-id> <task-id> <session-id>` from the project root. Save `runId` and `claimPath` from its JSON output.
2. Load task references with `oma agent context <agent-id> --difficulty Medium`, perform the task, and execute its pinned checks with `oma agent verify <run-id> --required`. Spawn/parallel already inject this graph-selected context. Individual exact commands can also be recorded with `oma agent verify <run-id> -- <executable> <args...>`. Run checks serially per run. No build unless explicitly requested.
3. Write the claim file with `status` (`completed`, `partial`, `blocked`, or `failed`), `changedFiles` (relative paths), `unresolved` (descriptions), and `artifacts` (relative paths to reports). A non-executable review may use `verificationSkipped` with a specific reason; it cannot override failed checks.
4. Native agents call `oma agent finish <run-id> <claim-file>`. Spawned processes leave finalization to the parent. The parent captures the actual exit code.

Example claim:

```json
{"status":"completed","changedFiles":["src/parser.ts"],"unresolved":[],"artifacts":[".agents/results/result-qa-s1.md",".agents/results/plan-s1.json",".agents/state/memories/session-ultrawork.md"]}
```

Receipts live in `.agents/state/agent-runs/`. They contain session/task/run IDs, vendor, workspace, timestamps, actual command argv and exit codes, working tree hashes, artifact hashes, unresolved work, and final status. These records prevent accidental reuse of stale evidence; they are local files, not a security boundary against an agent that intentionally edits receipts. Human-readable workflow reports are supplemental files at `.agents/results/result-{agentId}-{taskId}-{runId}-{sessionId}.md` for every dispatch path; the injected claim path and receipt schema remain unchanged. Preserve report filenames. Existing reports under configured coordination memory, `.agents/state/memories/`, or `.serena/memories/` remain read fallback until migration; write new reports only to `.agents/results/`.

The Ralph gate requires QA and REFINE task IDs in a nonempty plan, current successful executable checks, and report/plan/phase artifacts bound to those runs. A waiver alone does not pass this gate. Record a justified REFINE exception as `REFINE skipped: <specific reason>` before QA finalizes its evidence. Old Markdown-only reports remain readable but must be reverified to pass the gate.

Checks cover the Git working tree (HEAD, tracked contents/modes and nonignored untracked files). Generated `.agents/state`, `.agents/results`, and `.serena/memories`, and generated `.opencode/agents/oma-spawn-*.md` / `.kilo/agents/oma-spawn-*.md` wrappers are excluded from the tree hash and artifacts are hashed separately. In an unversioned directory, all files except those generated directories, `.git`, and `node_modules` are covered. External services, ignored dependencies, and malicious receipt tampering require separate controls.

## Artifact storage

- `docs/decisions/`: retained approach proposals, architecture decisions, and ADRs intended for version control; update `docs/decisions/index.md`. `design` names UI work. Read `docs/plans/designs/` and `docs/design-docs/` only as legacy fallback until migration.
- `docs/plans/work/`: execution plans and technical debt; `docs/plans/contracts/`: adopted API contracts. Keep their existing lifecycle and promotion rules.
- `.agents/results/`: run-specific reports, research, explanations, and draft contracts. When promoting a selected document into `docs/`, leave its destination in the source report.
- `.agents/state/`: coordination, resumption, and verification records. State notes link reports instead of holding duplicate report bodies.

If a report is already bound to a verification receipt, preserve its original path and bytes. Create a maintained copy and link it from the documentation index instead of editing or moving the bound report. Migration reports these files as deferred and never rewrites receipts, claims, or frozen plans.

Preview existing files with `oma docs migrate --dry-run --json`; apply the migration with `oma docs migrate`. The report lists moved and rewritten paths, skips, and conflicts.

## Coordination notes

Use native file tools and `memoryConfig.basePath` (default `.agents/state/memories/`) at the project root for optional coordination notes. Read an assigned task board and report progress for long tasks. Use the unique progress names from [Memory Protocol](memory-protocol.md); keep the injected claim path unchanged. Write all `result-*.md` reports in the project-root `.agents/results/`, independently of `memoryConfig.basePath`. Their presence alone does not prove completion. Include unresolved work in the claim even after failure.

Read-only dispatch returns `OMA_RESULT_JSON: {claim}` as one final stdout line. The parent persists that inspection. A `verificationSkipped` inspection remains distinguishable from executable checks and does not pass Ralph.

Repository checks: `cli/state/agent-results.test.ts`, `cli/state/artifact-verifier.test.ts`.

## Acceptance and recovery

Each executable task declares `acceptance_criteria: [{id, description}]` and `required_checks: [{id, criteria: [criterionId], command: [executable, ...args], cwd: "."}]`. Every criterion needs a check. Check IDs and executable/argv/cwd selectors must be unique. The run snapshots this contract. Another command/directory, a removed requirement or an uncovered criterion cannot count as acceptance evidence. Legacy plans without required checks remain readable but need a new contract/run to pass executable completion.

`oma agent verify RUN_ID --affected PATH...` executes tests selected from actual graph references and records outcomes. They count toward acceptance only when matching pinned checks. Use `--required` for all declared checks. Unmatched paths and empty graph test selections fail explicitly.

Optional `inputs` narrows hashing to declared project-relative files/directories and must include all relevant source, tests, configuration and dependency inputs. Missing/deleted inputs change the hash. No globs or symlink traversal are supported. Omit it for whole-tree hashing. The contract is checked independently of scope. Task dependency IDs use the existing `dependencies` array.

`oma agent resume SESSION_ID --dry-run` reports reused, ready, running and blocked tasks. Without `--dry-run`, it retries ready tasks in dependency order, persists outcomes, and revalidates evidence before success. Changed dependencies invalidate dependent reuse even if their own inputs match. Live managed processes and native attempts without liveness evidence are not duplicated.

Automatic replay requires `retry_policy: "safe"` and a prompt/agent in the plan or saved dispatch. The default is `manual`. `--max-attempts` defaults to 3 including the original attempt. A session lease prevents duplicate coordinators and permits recovery after the local owner dies. Mark interrupted native attempts partial/failed through their result contract before resuming. Keep the JSON plan fixed while recovery runs; record progress separately. This resumes tasks, not the model's interrupted conversation.

## Plan lineage and evidence failures

The plan's optional `lineage_id` defaults to its session ID. Keep it stable for the same workflow goal across resumed sessions. A task's optional `goal_id` defaults to its task ID; predeclared alternative task IDs for the same logical work must share it. Plan `max_attempts` defaults to 3 including the original attempt. Direct begin/spawn and resume count all runs for that lineage/goal, including runs without `resumedFrom`. `resume --max-attempts` can lower, but cannot raise, the frozen plan's bound.

First dispatch pins the full plan under `.agents/state/agent-plans/`. Task IDs, prompts, dependencies, checks, scope, and retry limits are immutable. JSON formatting/key order and transport `session_id`/`created` metadata do not change the contract. Progress belongs in coordination notes. Unknown task IDs and dependency cycles fail before dispatch. Contract changes require an explicitly separated new session and lineage; record why the scope changed and preserve the prior run's outcome. Never use a new lineage to evade a retry limit or repair metadata for the same goal. Do not insert tasks that recursively regenerate/review the executing plan.

- `PRODUCT_FAILURE`: executable checks failed, product inputs changed, or product work remains unresolved. Retry only within the shared goal budget.
- `WORKFLOW_EVIDENCE_FAILURE`: current product checks pass but a claim, artifact binding, or completion metadata is invalid. Automatic resume blocks product replay and returns the run/claim paths. One direct begin/spawn for that existing task may repair metadata within the same attempt budget; the run is marked `evidenceRepair`, and completion rejects product-input changes. Correct claim/report bindings and reverify the pinned checks, without changing the plan or dispatching planners/reviewers. A second evidence repair is rejected. If unresolved, stop with `partial`, distinguish product success from workflow failure, and provide the exact diagnostic and correction needed.

Keep mutable task boards, phase logs, receipts, and repair notes under `.agents/state/`, `.agents/results/`, or `.serena/memories/`, which are excluded from product hashes. They may be bound as separate artifacts only after their final update; a later metadata edit invalidates that binding without making it a product failure. Do not narrow product `inputs` to hide relevant source changes.

# DB Agent - Anti-Pattern Review Guide

Use this file for DB review, schema/query critique, remediation, or changes involving business logic in DB objects or ORM hooks. Review the affected scope; a query/index task does not require a database-wide inventory or rewrite.

## 1. ISO 27001 / 27002 / 22301-Oriented Suggestions

When any of the following are weak or absent, recommend improvements explicitly:

- access control and least privilege
- encryption in transit and at rest
- audit logging for privileged or sensitive data changes
- backup, restore drill, and retention evidence
- data classification and PII handling
- schema change management and migration traceability
- secure credential handling
- deletion and archive controls for cold data
- privileged access review and segregation of duties
- secure configuration baselines and hardening guidance
- business continuity planning assumptions for critical databases
- recovery objectives such as RTO / RPO and restore evidence
- failover, redundancy, and backup dependency visibility

## 2. Logical Design

- **CSV in one column**
  - Smell: comma-separated IDs/tags/statuses stored in `VARCHAR`
  - Fix: separate dependent table or junction table for many-to-many
- **Naive tree**
  - Smell: `parent_id` only, but complex subtree queries and moves are required
  - Fix: choose the tree model that matches the workload and relationship semantics
- **Surrogate-key obsession**
  - Smell: every table gets generic `id` without regard to natural/composite identity
  - Fix: use natural key or composite key when it models the domain better; prefer descriptive PK names like `bug_id`
- **Missing foreign keys**
  - Smell: referential integrity delegated to application code
  - Fix: add FK constraints and let the DB reject invalid references
- **EAV**
  - Smell: generic `entity_id`, `attr_name`, `attr_value`
  - Fix: use subtype modeling, JSON only when true schema flexibility is required
- **Polymorphic association**
  - Smell: one FK-like column can point to multiple parent tables
  - Fix: simplify relationships, use junction tables, or introduce a shared supertype
- **Repeated columns for multi-value attributes**
  - Smell: `tag1`, `tag2`, `tag3`
  - Fix: store one value per row in a dependent table
- **Metadata as schema**
  - Smell: table names or column names encode data values such as year or tenant
  - Fix: use partitioning, proper normalization, or vertical split

## 3. Physical Design

- **Floating-point money**
  - Smell: `FLOAT/REAL/DOUBLE` for currency or exact quantities
  - Fix: use `NUMERIC/DECIMAL`
- **Hard-coded enum in schema without governance**
  - Smell: volatile code sets trapped inside enum/check definitions
  - Fix: use reference tables when values are expected to evolve
- **Ghost file**
  - Smell: DB stores only file path while lifecycle, backup, authorization, and restore are unmanaged
  - Fix: use BLOB/object storage only with clear lifecycle, permissions, and backup/restore design
- **Index shotgun**
  - Smell: indexes created by guesswork or duplicated heavily
  - Fix: MENTOR the indexes:
    - Measure
    - Explain
    - Nominate
    - Test
    - Optimize
    - Rebuild

## 4. Query Design

- **NULL misuse**
  - Smell: NULL treated like a normal value or fake defaults used instead of NULL
  - Fix: use `IS NULL`, `IS NOT NULL`, `COALESCE`, and proper nullability semantics
- **Ambiguous grouping**
  - Smell: non-grouped columns selected beside aggregates
  - Fix: obey the single-value rule or rewrite with proper subqueries/window logic
- **Random sort**
  - Smell: `ORDER BY RAND()` on large tables
  - Fix: choose a sampling strategy that avoids full random sort
- **Poor-man's search engine**
  - Smell: broad `%keyword%` or regex search on large text
  - Fix: use full-text indexing or a dedicated search engine
- **Spaghetti query**
  - Smell: forcing every rule into one giant SQL statement
  - Fix: split into stages or multiple queries when it improves correctness and maintainability
- **Implicit columns**
  - Smell: `SELECT *` in production paths
  - Fix: list required columns explicitly

## 5. Application Integration

### Application Ownership Policy

Strongly recommend managing business decisions, workflow orchestration, and external side effects explicitly in application domain/services and jobs. Apply this recommendation to **new and existing** implementations, including triggers, stored procedures, stored functions, views, DB events/schedulers, and ORM hooks/entity listeners. This is a design default, not a blanket ban on those mechanisms.

For each affected existing implementation, identify its callers and writers, explain the risk, recommend a concrete replacement, and state the transition priority. Existing usage or convention alone is not a reason to retain it. If retention is justified, record the application alternative, the technical reason (such as atomicity across writers, measured performance, or audit requirements), and the relevant tests, privileges, deployment, and recovery approach. Recommend a staged transition when replacement is appropriate; implementation remains within the authorized scope.

### Patterns to Improve

| Pattern and failure mechanism | Strongly recommended alternative |
| --- | --- |
| Business policy in triggers, procedures, functions, views, or large `CASE` expressions: pricing, fees, eligibility, visibility, and approval rules become duplicated or tied to DB deployments | Give the policy an application owner and tests; version changing policies with effective dates and record the version/inputs used for decisions that must be reproduced |
| Implicit workflow: setting a status or saving an entity silently triggers payment, approval, shipment, or chained writes | Use explicit commands, validated state transitions, and service orchestration; moving the same hidden behavior into ORM hooks is not an improvement |
| External I/O inside DB execution, or a DB commit followed by an independent broker/API write: failures leave partial effects that rollback cannot undo | Record the integration event in an outbox in the same transaction as business data; publish through a worker with idempotency and bounded retry, and define compensation for partial external success |
| Business scheduling in DB events/jobs without execution ownership: settlement, expiry, and notifications lack retry limits, checkpoints, or failure visibility | Manage execution in an application job/workflow with observable runs and recovery; validate deadlines at action time rather than trusting a delayed expiry batch |
| Trigger-driven cache/search synchronization or cross-system projections without replay/reconciliation | Use explicit events or CDC with consumer deduplication, ordering requirements, lag monitoring, replay, and reconciliation |
| Policy bypass through multiple writers or procedures that modify another service's tables | Identify API, batch, admin, import, and direct SQL writers; align write permissions and policy enforcement, and use the owning service's API/events across service boundaries |
| Cascading deletion of independently owned objects or trigger-driven soft delete without retention/restore rules | Manage deletion intent, authorization, retention, and restoration in the application; define explicit coordination across ownership boundaries |
| Business authorization and audit context hidden in DB session state or routines | Give business authorization and action intent an application owner; propagate verified actor/tenant context and record decision reasons, while preserving DB enforcement and DB change/access audit |

### Database Responsibilities to Preserve

- Keep declarative integrity (`PK`, `FK`, `UNIQUE`, `CHECK`, `NOT NULL`, and exclusion constraints where supported), atomic conditional updates, transaction boundaries, isolation, and locking. Application prechecks alone cannot enforce invariants under concurrent or independent writes.
- Preserve RLS and DB privileges where they enforce access restrictions; RLS can be the primary enforcement point. Verify the connection roles, bypass privileges, and actor/tenant context rather than replacing RLS with application filtering.
- Keep DB access/change audit where required. Application business audit does not cover every direct SQL, administrator, or batch change. Audit triggers can be justified, but their coverage and privileged bypass limits must be explicit.
- `ON DELETE CASCADE` can be appropriate for dependent components with the same lifecycle. Flag cascades across independent ownership or retention boundaries, rather than treating every cascade as an anti-pattern.
- Keep efficient joins, aggregates, pure SQL functions/views, generated values, and set-based bulk operations in the DB where appropriate. Application ownership of a job does not require fetching all rows into an application loop. DB-local summaries or routines may be retained with correctness and performance evidence.
- Backup, replication, vacuum/statistics, partition maintenance, and storage TTL remain database operations. Separate physical cleanup from business expiry and retention decisions.
- CDC transports committed changes; it does not automatically supply the business intent of a domain event. An application can write an outbox and use CDC to publish it. Neither approach removes the need to handle duplicates, ordering, or consumer recovery.

For an accepted transition, use `resources/migration-playbook.md` §Moving Business Logic into the Application. Preserve all writers and invariants, and prevent the old and new paths from executing the same external effect twice.

### Other Integration Anti-Patterns

- **Readable passwords**
  - Smell: plaintext or reversibly encrypted passwords
  - Fix: salted one-way hash, secure transport, no password recovery by disclosure
- **SQL injection**
  - Smell: unchecked input concatenated into SQL
  - Fix: parameterized queries, allowlists for dynamic clauses, code review
- **Surrogate-key gap paranoia**
  - Smell: trying to fill deleted ID gaps
  - Fix: treat surrogate keys as identifiers, not row numbers
- **Ignoring DB return values and executed SQL**
  - Smell: no error handling or no visibility into actual SQL
  - Fix: inspect execution results, logs, and emitted SQL
- **SQL exempt from engineering discipline**
  - Smell: no version control, no tests, no review, no documentation
  - Fix: treat DB artifacts like code
- **Active Record leakage**
  - Smell: controllers calling persistence methods directly
  - Fix: keep service/repository boundaries and avoid mixing UI flow with DB access

## 6. Vector Retrieval / RAG

- **Treating vector DB as a drop-in search replacement**
  - Smell: lexical search removed entirely after adding embeddings
  - Fix: default to hybrid retrieval; combine keyword/BM25, metadata filters, ANN, and reranking
- **Embedding everything without a versioning strategy**
  - Smell: multiple models/dimensions/chunk rules with no metadata or migration plan
  - Fix: version embeddings like schema, store model/config metadata, and support rolling re-embedding
- **Ignoring chunking strategy**
  - Smell: fixed-size chunks and naive splitting regardless of document structure
  - Fix: use document-aware chunking, overlap, and hierarchical structure where needed
- **Blind ANN defaults**
  - Smell: HNSW/IVF/PQ defaults used without recall-latency benchmarking
  - Fix: benchmark real workloads and tune ANN parameters deliberately
- **Assuming metadata filtering scales for free**
  - Smell: high-cardinality filters piled onto vector search with no partition strategy
  - Fix: design pre-segmentation, partitioned indexes, or tenant-specific collections where justified
- **Unplanned real-time writes**
  - Smell: continuous inserts degrade recall and latency over time
  - Fix: design buffers, compaction, tiered indexing, and ingestion/search separation
- **Storing raw documents in vector DB**
  - Smell: vector DB becomes source of truth for full documents
  - Fix: keep canonical docs in object store or primary DB and store references in vector DB
- **No retrieval observability**
  - Smell: only infra metrics exist; no recall or semantic drift monitoring
  - Fix: add golden queries, offline evaluation, drift tracking, and token-waste monitoring
- **Over-trusting similarity scores**
  - Smell: one cosine threshold is treated as universally meaningful
  - Fix: calibrate per domain/model and use reranking for precision-sensitive cases
- **Believing vector DB equals RAG architecture**
  - Smell: only index layer is optimized while chunking, orchestration, and feedback loop are ignored
  - Fix: treat retrieval as a pipeline, not a single database decision

# DB Agent - Self-Verification Checklist

Run through every item before submitting your work.

## Modeling
- [ ] DB type selection justified from workload, not preference
- [ ] Relational models normalized to at least 3NF, or denormalization explicitly justified
- [ ] NoSQL models aligned to concrete access patterns and aggregate boundaries
- [ ] Graph model used only where variable-depth traversal or relationship-pattern queries dominate; relational adjacency preferred for shallow fixed-depth cases
- [ ] Vector retrieval is used only where semantic similarity is actually required
- [ ] Keys, cardinality, and ownership clearly defined
- [ ] No obvious anti-patterns remain: CSV-in-column, repeated columns, EAV, polymorphic FK, missing FK

## Schema Documentation
- [ ] External schema documented
- [ ] Conceptual schema documented
- [ ] Internal schema documented
- [ ] Naming standards and field definitions documented
- [ ] Glossary / terminology dictionary updated

## Integrity & Concurrency
- [ ] Entity, domain, referential, and business-rule integrity addressed
- [ ] Transaction boundaries documented
- [ ] Isolation level selected for critical flows
- [ ] Locking / optimistic concurrency strategy documented
- [ ] Transparency assumptions called out when using distributed systems

## Application Ownership (Affected Scope)
- [ ] Affected triggers, stored procedures/functions, views, DB jobs, and ORM hooks reviewed for business policy, implicit workflows, and external side effects
- [ ] New and existing findings receive a strong application ownership recommendation with a concrete alternative and transition priority; retained implementations have technical evidence beyond existing convention
- [ ] Proposed design preserves DB integrity, concurrency, RLS/privileges, audit coverage, justified dependent cascades, and efficient set-based processing
- [ ] Where relevant, policy versions/decision context, action-time expiry checks, and all API/batch/admin/import/direct SQL writers are addressed
- [ ] Integration events are committed atomically with business data when needed; CDC intent/coverage, idempotency, ordering, bounded retry, replay, and compensation are explicit
- [ ] Planned transitions verify policy behavior and writer coverage, use one owner per side effect, and define rollback without lost or duplicate effects

## Performance & Operations
- [ ] Indexes match hot queries and join/filter patterns
- [ ] Partitioning / sharding strategy justified if introduced
- [ ] Hot vs cold data separation considered
- [ ] Full and incremental backup strategy documented
- [ ] Restore test or recovery validation approach documented
- [ ] Query anti-patterns reviewed: `SELECT *`, random sort, ambiguous grouping, full wildcard search without proper tooling
- [ ] Slow-query fixes verified with before/after execution plans on production-like data (estimate vs actual rows checked)
- [ ] Schema changes on live tables follow expand-contract with lock-aware DDL, lock timeouts, and batched, resumable backfill
- [ ] Destructive (contract) steps deferred to a separate deploy after a soak window, with a final backup taken first

## Vector Retrieval
- [ ] Vector DB is not being used as the canonical document store
- [ ] Hybrid retrieval considered where exact match, explainability, or compliance filtering matter
- [ ] Embedding model, dimension, normalization, chunking, and version metadata documented
- [ ] Re-embedding / re-index migration plan exists for model or chunking changes
- [ ] ANN index choice and tuning parameters benchmarked on production-like data
- [ ] Metadata filtering behavior and high-cardinality filter impact understood
- [ ] Similarity score thresholds calibrated per domain or reranking added for precision-critical paths
- [ ] Retrieval observability defined: golden queries, recall drift, semantic drift, token waste

## Capacity
- [ ] Capacity sheet updated by object
- [ ] Tablespace/storage estimate updated
- [ ] Disk estimate includes online, batch, backup, and growth assumptions
- [ ] Data type choices reviewed for storage efficiency

## Delivery
- [ ] Assumptions, tradeoffs, and risks are explicit
- [ ] Migration impact or rollout steps included when schema changes are proposed
- [ ] ISO 27001 / 27002-friendly suggestions included where design affects access control, logging, encryption, backup, recovery, or change management
- [ ] ISO 22301-friendly suggestions included where design affects resilience, failover, restore testing, RTO, or RPO

# DB Agent - Deliverable Templates

Use this structure when the user asks for modeling or DB architecture outputs.

## 1. Decision Summary

```md
## Database Decision Summary
- Workload type:
- Recommended engine/model:
- Reason:
- Consistency target:
- Availability target:
- Retention target:
- RPO / RTO:
```

## 2. Schema Documentation

```md
## External Schema
- Consumer / view:
- Required fields:
- Access pattern:

## Conceptual Schema
- Entity / aggregate:
- Description:
- Relationships:
- Cardinality:

## Internal Schema
- Object name:
- Type: table / collection / index / partition
- Primary key / shard key:
- Constraints:
- Access path:
- Storage / tablespace:
```

## 3. Data Standards

```md
| Standard Item | Value |
| --- | --- |
| Naming convention | |
| Definition rule | |
| Data type / format rule | |
| Allowed value rule | |
| Nullability rule | |
| Default value rule | |
```

## 4. Glossary

```md
| Term | Definition | Synonym / Forbidden Term | Owner |
| --- | --- | --- | --- |
```

## 5. Capacity Estimation

```md
| Object | Daily Txn | Online Workload | Batch Workload | Row / Doc Size | Growth | Retention | Primary Storage | Backup Storage |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
```

Add these summary sections after the table:
- tablespace/storage allocation
- disk total with growth buffer
- hot vs cold data split
- backup type and cadence

## 6. Concurrency & Integrity

```md
## Integrity
- Entity integrity:
- Domain integrity:
- Referential integrity:
- Business-rule integrity:

## Concurrency
- Critical transaction:
- Isolation level:
- Locking method:
- Retry / idempotency strategy:
```

## 7. Vector Retrieval Design

```md
## Vector Retrieval Design
- Use case:
- Why vector retrieval is needed:
- Why lexical search alone is insufficient:
- Hybrid retrieval approach:
- Canonical document store:
- Vector store contents:
- Embedding model / dimension / normalization:
- Chunking policy / overlap:
- ANN index type / key tuning params:
- Metadata filtering strategy:
- Reranking strategy:
- Re-embedding migration plan:
- Retrieval evaluation metrics:
```

# DB Agent - Error Playbook

## Symptom: Relational model is breaking 3NF everywhere
- Re-check whether reporting columns, derived values, and snapshots are being mixed into OLTP tables
- Split transactional source-of-truth from read models or marts
- Keep denormalization only where query cost justifies it

## Symptom: NoSQL model keeps requiring cross-document joins
- Re-evaluate aggregate boundaries and access patterns
- If strong cross-entity consistency dominates, switch recommendation toward relational storage

## Symptom: Isolation level is unclear
- Start from anomaly prevention required by the business flow
- Document what must be prevented: dirty read, non-repeatable read, phantom, lost update
- Choose the lowest level that still blocks unacceptable anomalies

## Symptom: Capacity estimate is unreliable
- Separate online traffic, batch traffic, retention, and backup retention
- Estimate by object first, then aggregate to tablespace and disk totals
- Add growth and reindex/maintenance headroom

## Symptom: Backup plan exists but restore confidence is low
- Add restore drill frequency
- Define sample recovery scenarios and max tolerable data loss
- Distinguish snapshot/full backup from incremental/log-based recovery

## Symptom: Deadlocks in production
- Read the engine's deadlock log to identify the two lock cycles, not just the victim
- Enforce consistent lock ordering: all transactions touch shared tables/rows in the same order
- Shorten transactions: move reads, external calls, and computation outside the transaction boundary
- Index foreign keys involved in the cycle; unindexed FK checks escalate to scans and widen lock footprints
- Add bounded retry with backoff at the application layer for the surviving unavoidable cases

## Symptom: Replication lag growing
- Look for long-running transactions on the primary holding back apply/vacuum
- Check for unbatched bulk writes (backfills, cascading deletes) and throttle them per `resources/migration-playbook.md`
- Verify large-table DML has supporting indexes on the replica side
- If reads require read-your-writes, route those sessions to the primary or gate on replica LSN/GTID instead of hoping lag stays low

## Symptom: Connection pool exhausted
- Compare app pool size x instance count against the DB's max connections; oversubscription starves everyone
- Hunt leaked connections: sessions idle-in-transaction, missing release on error paths
- Scope sessions to the request/transaction, never to long-lived objects (see backend rule: no shared mutable ORM sessions)
- Front high-instance-count deployments with a server-side pooler (e.g. pgbouncer) instead of raising max connections

## Symptom: Migration failed midway
- Determine what actually applied: transactional DDL engines roll back cleanly; others leave partial state to inspect object-by-object
- Make migration steps idempotent (`IF NOT EXISTS`, guarded DML) so re-running is safe
- Resume batched backfills from their checkpoint rather than restarting from zero
- If the failure was a lock timeout, that is the playbook working: retry at a quieter window per `resources/migration-playbook.md`

# DB Agent - Examples

## Example 1: Relational Order Management

**Input**: "Design the database for ordering, payment, shipment, and refund flows."

**Output**:
```md
## Model Choice
- PostgreSQL chosen for strong integrity, cross-entity transactions, and reporting joins

## Conceptual Schema
- Customer 1:N Order
- Order 1:N OrderItem
- Order 1:N Payment
- Order 1:N Shipment
- Order 1:N Refund

## Internal Schema
- orders(id PK, customer_id FK, status, ordered_at, total_amount, currency_code)
- order_items(id PK, order_id FK, product_id FK, qty, unit_price, line_amount)
- payments(id PK, order_id FK, payment_method_code, status, approved_at, amount)
- shipments(id PK, order_id FK, warehouse_id FK, status, shipped_at)
- refunds(id PK, payment_id FK, reason_code, refunded_at, amount)

## Integrity / Concurrency
- 3NF satisfied
- FKs enforce referential integrity
- Unique(order_id, product_id) for line uniqueness
- Payment capture uses READ COMMITTED with idempotency key
- Inventory reservation uses SELECT ... FOR UPDATE

## Operations
- Hot data: last 12 months in primary partition
- Cold data: monthly archive partitions after 12 months
- Daily full backup + 15-minute incremental/WAL archiving
```

## Example 2: NoSQL Activity Feed

**Input**: "Model a high-write social feed with fan-out reads."

**Output**:
```md
## Model Choice
- NoSQL chosen because feed reads follow aggregate access by user timeline and tolerate eventual consistency

## Conceptual Schema
- User aggregate
- Post aggregate
- TimelineEntry aggregate

## Internal Schema
- users collection keyed by user_id
- posts collection keyed by post_id
- timelines collection keyed by owner_user_id + created_at

## Consistency / BASE
- Timeline fan-out is eventually consistent
- Post delete creates compensating cleanup event
- Source-of-truth post document retained separately from denormalized timeline entry

## Optimization
- Partition key: owner_user_id
- TTL on ephemeral recommendation cache
- Cold feed archive after 180 days
- Nightly full snapshot + hourly incremental backup
```

## Example 3: Data Standards & Capacity Update

**Input**: "Add subscription billing and keep the glossary and sizing docs current."

**Output**:
```md
## Data Standards
| Name | Definition | Format | Rule |
| --- | --- | --- | --- |
| subscription_status_code | Subscription lifecycle state | VARCHAR(20) | ACTIVE, PAUSED, CANCELED only |
| billing_cycle_day | Day of month for billing | SMALLINT | 1-28 only |

## Glossary Additions
- Subscription: recurring billing agreement between customer and service
- Invoice: billable statement generated for a subscription cycle

## Capacity Update
- subscriptions: 1.2M rows / year
- invoices: 14.4M rows / year
- payments: 16M rows / year
- Estimated primary storage: 180 GB / year
- Backup storage multiplier: 2.5x including retention
```

## Example 4: Enterprise RAG Retrieval

**Input**: "Design a policy search platform using a vector DB for enterprise RAG."

**Output**:
```md
## Model Choice
- PostgreSQL remains source of truth for document metadata and lifecycle
- Object storage holds canonical documents
- Vector DB is used only as semantic retrieval index

## Retrieval Architecture
- Hybrid retrieval = BM25 + ANN + metadata filter + cross-encoder rerank
- Filters applied for tenant_id, region, policy_type, effective_date
- Exact compliance terms still supported by lexical search

## Embedding Governance
- embedding_model: text-embedding-3-large
- embedding_version: v2026_03_policy_chunks_v2
- chunking: heading-aware, 350-600 tokens, 80-token overlap
- Shadow index used during re-embedding migration

## ANN / Operations
- HNSW selected after recall-latency benchmark
- search cluster separated from ingestion cluster
- Hot vectors: last 90 days of updated policies
- Cold vectors: older embeddings compacted to lower-cost tier

## Observability
- Golden query set maintained for recall regression
- Similarity score distribution monitored by policy domain
- Prompt token waste tracked for bad retrieval
```

## Example 5: Anti-Pattern Remediation on a Live Table

**Input**: "Our `products` table stores tags as a comma-separated column, prices as FLOAT, and category lookups are slow. Fix it without downtime."

**Output**:
```md
## Findings (anti-pattern review)
- CSV-in-column: `products.tags VARCHAR` holds comma-separated tags -> junction table
- Floating-point money: `products.price FLOAT` -> NUMERIC(12,2)
- Slow category lookup: Seq Scan confirmed via EXPLAIN (ANALYZE); estimated 200 rows vs actual 1.8M -> stale stats + missing composite index

## Remediation Plan (expand-contract, four deploys)
1. Expand (additive only)
   - CREATE TABLE product_tags(product_id FK, tag_id FK, PRIMARY KEY(product_id, tag_id))
   - ALTER TABLE products ADD COLUMN price_amount NUMERIC(12,2) (nullable)
   - CREATE INDEX CONCURRENTLY idx_products_category_active ON products(category_id, is_active)
2. Migrate (dual-write + backfill)
   - App writes tags to both column and junction table; price to both columns
   - Backfill in 10k-row PK batches with checkpointing; replication lag watched, throttled at threshold
   - Parity: row-count + sampled checksum comparison recorded
3. Switch (reads behind flag)
   - Reads flip to product_tags / price_amount; old paths kept for rollback; 1-week soak
4. Contract (separate deploy, after soak + final backup)
   - Drop products.tags and products.price; VALIDATE NOT NULL on price_amount via CHECK NOT VALID first

## Verification
- ANALYZE products; EXPLAIN (ANALYZE, BUFFERS) before/after recorded: Seq Scan -> Index Scan, p95 420ms -> 6ms
- lock_timeout = '2s' + retry on every DDL; no COPY-algorithm DDL on the hot table
```

## Example 6: New Order Approval Flow

**Input**: "Add a trigger that approves orders, charges the card, and sends email when status becomes READY."

**Recommendation**: Strongly recommend an explicit application command for approval and a versioned eligibility policy. Persist the validated transition and an outbox event in one transaction; use a worker for payment/email with idempotency, bounded retry, and a defined recovery path for partial success. Keep FK/UNIQUE/CHECK constraints and concurrency protection in the DB. A status update or ORM save hook must not silently become the workflow entry point.

## Example 7: Existing Procedure and Expiry Job

**Input**: "The existing settlement procedure calculates fees, and a DB job marks subscriptions expired. Keep using them because that is our convention."

**Recommendation**: Strongly recommend application ownership of fee policy and business job execution; existing convention alone is not retention evidence. First inspect API, batch, admin, and direct SQL writers. Version fee policies and record which version produced a settlement; validate `expires_at` when an action occurs rather than relying on the expiry job having run.

**Transition**: Compare old/new decisions without charging or notifying twice, coordinate one execution owner across old app instances and DB routines, and preserve a tested rollback path during the soak window. Keep DB constraints, atomic updates, RLS, change audit, and efficient SQL aggregation. An audit trigger that records every affected writer may remain with documented coverage and bypass limits. Retain a DB calculation routine only with a concrete reason, such as measured data-local processing cost, plus correctness/concurrency tests and deployment/recovery evidence.

# DB Agent - Execution Protocol

## Preparation
Use the task's scope, existing project conventions, and acceptance criteria. Follow `../references/_shared/core/execution-policy.md` when it has not already been supplied. Read only references needed by the selected operation; consult lessons or recovery guides for an observed issue. Expand planning depth only when the change requires it.

## Step 1: Explore
- Identify actors and external views that need data
- Capture entities, aggregates, reference data, events, and lifecycle states
- List access patterns:
  - point lookup
  - range scan
  - aggregation/reporting
  - full-text/search
  - semantic retrieval / RAG
  - batch ingestion
- Decide relational vs non-relational:
  - Prefer relational when joins, strong integrity, and transactional consistency dominate
  - Prefer NoSQL when aggregate reads/writes, massive scale, flexible schema, or distribution tolerance dominate
  - Prefer a graph model only when variable-depth traversals or relationship-pattern queries dominate the workload; adjacency (`parent_id`, junction tables) in a relational engine handles shallow, fixed-depth traversals fine
  - Prefer vector retrieval only for semantic similarity workloads; do not replace keyword/search engines blindly
- Screen for anti-pattern signals early:
  - comma-separated values in one column
  - tag1/tag2/tag3 style multi-column attributes
  - generic EAV tables
  - polymorphic foreign keys
  - missing FK constraints
  - blind surrogate-key usage
  - business policies, implicit workflows, or external side effects in affected triggers, stored procedures/functions, views, DB jobs, or ORM hooks
- For the affected logic, identify callers and all writers (API, batch, admin, import, direct SQL); use `resources/anti-patterns.md` §5 to distinguish application responsibilities from DB enforcement
- Record assumptions and open risks
- If vector retrieval is needed, define:
  - hybrid vs pure vector retrieval
  - exact-filter requirements
  - embedding model and dimension
  - chunking strategy
  - reranking need

## Step 2: Design
- Produce **external schema**:
  - user-facing views, APIs, reports, or producer/consumer contracts
- Produce **conceptual schema**:
  - entities/aggregates, relationships, cardinality, optionality, ownership
- Produce **internal schema**:
  - tables/collections, keys, indexes, partitions, tablespaces, storage layout
- SQL path:
  - normalize to **3NF** minimum
  - define PK, FK, unique, check, default, nullability
  - document transaction boundaries and target isolation level
  - apply the application ownership policy in `resources/anti-patterns.md` §5 to both new and existing business logic; recommend alternatives and transition priorities, and document technical evidence for any retention
- NoSQL path:
  - model around access patterns and aggregate boundaries
  - define sharding/partition keys, secondary indexes, TTL, duplication policy
  - document consistency model, conflict strategy, and repair/reconciliation approach
- Graph path:
  - model nodes/edges from the traversal questions the business actually asks, not from the ERD
  - define edge direction, cardinality, and properties; decide which lookups still need secondary indexes on node properties
  - document traversal depth expectations and whether the engine's strength (index-free adjacency) is actually exercised
  - keep source-of-truth entities in the primary store when the graph is a derived projection; document the sync path
- Vector path:
  - define embedding model, dimension, normalization, and metadata versioning
  - define chunking policy, overlap, and document-type-specific chunk rules
  - design hybrid retrieval: lexical + ANN + metadata filtering + reranking
  - define canonical document store vs vector index boundary
  - document ANN index choice and key tuning parameters
- Apply data standards:
  - naming, definition, format, code set, validation rule
- Review security/compliance posture:
  - least privilege support
  - audit trail needs
  - encryption at rest / in transit expectations
  - backup/restore traceability
  - retention and deletion obligations
  - control guidance alignment for ISO 27001 / 27002
  - continuity and recovery alignment for ISO 22301

## Step 3: Optimize
- Validate hot paths against indexes and partitions; tune with `resources/query-tuning.md` (measure -> explain -> nominate -> test, never guesswork)
- If optimization requires schema changes on live tables, plan them via `resources/migration-playbook.md` (expand-contract, lock-aware DDL, batched backfill)
- If moving existing DB business logic into the application, use the same playbook's ownership cutover steps to preserve writer coverage and avoid duplicate side effects
- For vector systems:
  - benchmark recall vs latency on production-like queries
  - measure ANN parameter tradeoffs, filter pushdown cost, and write amplification
  - design re-embedding and shadow-index migration flow
  - separate ingestion/search clusters or hot/cold vector tiers when write load is high
- Separate hot and cold data:
  - recent operational data stays online
  - historical/archive data moves to cheaper storage or archive partitions
- Define backup and recovery:
  - full backup cadence
  - incremental backup cadence
  - retention window
  - restore validation frequency
- Review anti-patterns in five buckets:
  - logical design
  - physical design
  - query design
  - application integration
  - vector retrieval / RAG
- Update capacity estimation:
  - object-by-object volume
  - tablespace/storage allocation
  - disk estimate
  - online workload
  - batch workload
  - backup volume

## Step 4: Verify
- Run `resources/checklist.md`
- For a schema-design task, confirm affected existing artifacts were updated;
  for scoped tuning, record SQL/plan evidence instead of creating this full set:
  - glossary
  - standards table
  - capacity estimate
  - backup/recovery notes
- For vector systems, confirm updated:
  - embedding version metadata
  - chunking policy
  - retrieval evaluation set
  - re-index / re-embedding migration notes
- Confirm anti-pattern findings were either fixed or explicitly accepted with rationale
- For affected DB business logic, confirm a strong improvement recommendation and concrete application alternative were given even when implementation is deferred; retention needs technical evidence, and planned transitions need writer coverage, preserved DB guarantees, effect ownership, and rollback
- Call out ISO 27001 / 27002-relevant risks and recommended controls when applicable
- Call out ISO 22301-relevant continuity and recovery gaps when applicable
- Call out any intentional denormalization or weaker consistency with reason

## On Error
See `resources/error-playbook.md` for recovery steps.

# DB Agent - ISO 27001 / 27002 / 22301 Guide

Use this file when the task involves database controls, audit readiness, resilience, or continuity requirements.

## Positioning

- **ISO 27001**: management-system and control objectives view
- **ISO 27002**: practical control guidance view
- **ISO 22301**: business continuity and recovery view

Do not claim compliance from schema work alone. Use these standards to improve database design decisions, control coverage, and operational evidence.

## 1. ISO 27001 / 27002 for Databases

Recommend improvements when the database design weakens:

- access control and least privilege
- separation of duties for admin, operator, developer, and read-only access
- encryption at rest and in transit
- key and secret management
- audit logging and tamper-resistant log retention
- change management and schema migration traceability
- backup security and retention handling
- secure configuration and baseline hardening
- data classification, masking, and PII handling

Typical DB-level suggestions:

- role-based access with least privilege
- dedicated break-glass admin path
- column or application-layer protection for sensitive fields where needed
- immutable or centralized audit log export
- secret rotation and non-human service identities
- explicit approval path for destructive schema changes

## 2. ISO 22301 for Databases

Recommend improvements when the design weakens business continuity:

- RTO / RPO definition
- failover readiness
- backup coverage and retention
- restore validation frequency
- dependency mapping for upstream/downstream systems
- data replication strategy
- hot/warm/cold standby decisions
- archival and cold-data recovery assumptions

Typical DB-level suggestions:

- define tiered continuity expectations by database criticality
- document backup cadence aligned to RPO
- document restore and failover process aligned to RTO
- test restore drills regularly, not only backup completion
- make derived indexes rebuildable from canonical data
- separate source-of-truth data from caches and secondary indexes

## 3. Review Questions

Ask these when standards matter:

- Who can read, write, alter schema, and administer the database?
- Are privileged actions logged and retained?
- Are secrets and keys managed outside code and migration files?
- What evidence exists that backups can actually be restored?
- What is the target RPO and RTO?
- Which systems depend on this database to continue service?
- Can cold/archive data be recovered within required time?
- Are vector indexes or search indexes rebuildable from canonical data?

## 4. Output Pattern

When relevant, add a short section like:

```md
## ISO Control Notes

### ISO 27001 / 27002
- Access control gap:
- Logging / traceability gap:
- Encryption / secret gap:
- Recommended control:

### ISO 22301
- Criticality:
- RPO / RTO gap:
- Backup / restore gap:
- Failover / dependency gap:
- Recommended continuity improvement:
```

## 5. Guardrails

- Keep standards comments practical and database-specific
- Prefer implementable controls over abstract policy language
- Distinguish between source-of-truth data and rebuildable derived data
- If the issue is infrastructure-only, involve `tf-infra-engineer` (`oma-tf-infra`)
- If the issue is org-wide audit/process ownership, involve QA or PM

# DB Agent - Live Schema & Data Migration Playbook

Use this file when a schema or data change must be applied to a live database: altering hot tables, backfilling data, changing types, splitting/merging tables, or cutting over to a new structure.

Scope boundary: `oma-refactor` plans the code-side expand-contract sequence and routes the **database mechanics** here. This file owns lock behavior, backfill execution, cutover verification, and rollback of the data layer.

## Core Position

- Deployment, not commit, is the unit of incrementality for stateful change.
- `git revert` does not restore data. Every phase must be independently deployable and reversible until the contract step.
- Never ship a destructive schema change in the same deploy as the code that requires it.
- A migration that cannot state its lock impact on the largest affected table is not ready to run.

## Expand-Contract for Schemas

Run live changes in four separately deployed phases:

1. **Expand** - additive only
   - Add new columns as nullable (or with a non-volatile default), new tables, new indexes
   - Old code keeps working untouched; new structures are invisible to it
2. **Migrate** - dual-write + backfill
   - New code writes to both old and new structures
   - Batched backfill copies historical data (see below)
   - Verify parity: row counts, checksums, or sampled field comparison between old and new
3. **Switch** - move reads
   - Flip reads to the new structure behind a feature flag
   - Keep dual-write and the old read path available for instant rollback
   - Soak: hold the flag on for an agreed window while monitoring errors and drift
4. **Contract** - remove old
   - Only after the soak window passes and no consumer reads the old structure
   - Drop old columns/tables in a later, separate deploy; take a final backup first

Rollback rule per phase: Expand -> drop the additions; Migrate -> stop dual-write, keep old as truth; Switch -> flip the flag back; Contract -> restore from backup only (this is why contract waits).

## Moving Business Logic into the Application

Use these steps when replacing existing trigger/procedure/function/job business behavior. The ownership recommendation and retention criteria live in `resources/anti-patterns.md` §5; this section owns the cutover mechanics.

1. **Inventory the affected execution paths**: identify API, batch, admin, import, and direct SQL writers, chained DB routines, privileges, transaction boundaries, and externally visible effects. Resolve writers that would bypass the new policy before cutover.
2. **Prepare the application path**: implement explicit commands, domain policies, or jobs with equivalent invariants, policy/decision versioning where required, idempotency, and bounded retry. Keep required DB constraints, RLS, auditing, atomic updates, and isolation. Write outbox events in the business transaction when external delivery is needed.
3. **Verify without duplicating effects**: compare policy decisions on representative inputs and test concurrency, direct writers, rollback, and failure recovery. Shadow evaluation must not execute payments, notifications, or other external effects.
4. **Switch execution ownership**: coordinate application rollout with DB behavior changes so each effect has one owner. Account for old app instances, in-flight transactions, queued jobs/events, and deduplication keys. Do not let the trigger and application both execute the same effect. Dual-writing old/new storage representations within one transaction may still be needed; it is distinct from executing effects twice.
5. **Soak and recover**: monitor errors, decision parity, processing lag, and missing/duplicate effects. Define how to restore the old execution owner and reconcile already committed or published work; disabling a feature flag alone cannot undo external effects.
6. **Contract later**: remove obsolete DB routines only after callers/writers and queued work have migrated and the soak window passes, in a separate deployment with versioned definitions and the required backup/recovery evidence.

## Lock-Aware DDL

Know what each statement locks on your engine and version before running it against a hot table.

- Always set a lock timeout so DDL waits fail fast instead of queueing behind long transactions and blocking all traffic behind them (e.g. PostgreSQL `SET lock_timeout = '2s'` + retry loop).
- PostgreSQL highlights:
  - `CREATE INDEX CONCURRENTLY` instead of plain `CREATE INDEX` on live tables (not inside a transaction)
  - Add `NOT NULL` via `CHECK (... IS NOT NULL) NOT VALID` then `VALIDATE CONSTRAINT` (validation takes a weaker lock)
  - Column type changes that rewrite the table (`ALTER TYPE`) need a dual-column expand-contract instead
  - Adding a column with a constant default is metadata-only on modern versions; volatile defaults rewrite
- MySQL highlights:
  - Check whether the change supports `ALGORITHM=INSTANT` or `INPLACE`; anything forcing `COPY` on a hot table should go through an online schema change tool (gh-ost, pt-online-schema-change)
- Any engine:
  - Run risky DDL at low-traffic windows even when "online"
  - One DDL concern per migration file; do not mix heavy DDL and DML in one transaction

## Batched Backfill

- Batch by primary-key range with a bounded batch size; never one `UPDATE ... WHERE new_col IS NULL` over the whole table
- Make each batch idempotent and record a checkpoint (last key processed) so the job is resumable after failure
- Throttle between batches and watch replication lag; pause when lag exceeds the agreed threshold
- Do not wrap the whole backfill in one transaction: long transactions block vacuum/purge and hold locks
- Backfill runs as application-level code or a migration job, not inside the DDL migration file

## Cutover Verification

Before the switch phase:

- Parity check old vs new (counts, checksums, or sampled diffs) with results recorded
- Read-path comparison in shadow mode where feasible (serve old, compare new, log mismatches)
- Confirm every consumer of the old structure is identified: application paths, reports, ETL, triggers, views

## Migration Hygiene

- Prefer forward-only migrations; if down migrations exist, they must be tested, not decorative
- Test the migration against production-scale data volume, not an empty dev schema
- Record expected duration and lock impact in the migration description
- Schema change management and traceability requirements from `resources/iso-controls.md` apply: approvals for destructive changes, audit trail of who ran what when

## Failure Modes Quick Table

| Failure | Cause | Prevention |
| --- | --- | --- |
| Site-wide stall during DDL | DDL queued behind a long transaction, everything queues behind the DDL | lock timeout + retry, kill long transactions first |
| Backfill causes outage | unbatched update, lock escalation, replication lag | bounded batches, throttle, lag monitoring |
| Cannot roll back after cutover | old structure dropped too early | contract only after soak window + final backup |
| Data drift between old and new | dual-write bug or missed writer | parity checks before switch, single write path per field owner |
| Migration dies halfway | non-idempotent steps, no checkpoint | idempotent batches, resumable checkpointing, transactional DDL where the engine supports it |

# DB Agent - Query Tuning & Index Design Guide

Use this file when the task involves slow queries, execution plans, index selection, or query rewrites. Anti-pattern smells (`SELECT *`, `ORDER BY RAND()`, poor-man's search) live in `resources/anti-patterns.md`; this file owns the tuning method.

## Triage Order

Never tune from guesswork. Follow MENTOR (Measure, Explain, Nominate, Test, Optimize, Rebuild) concretely:

1. **Measure** - find the actual offender
   - PostgreSQL: `pg_stat_statements` by total/mean time; MySQL: slow query log / `performance_schema`
   - Rank by total time, not just per-call time: a 10ms query at 1k QPS beats a 2s report query
2. **Explain** - read the real plan
   - PostgreSQL: `EXPLAIN (ANALYZE, BUFFERS)`; MySQL: `EXPLAIN ANALYZE`
   - Run with production-representative parameters; plans change with parameter selectivity
3. **Nominate** - identify the dominant cost node before changing anything
4. **Test** - reproduce the slowness on production-like data volume
5. **Optimize** - index, rewrite, or schema change (in that order of preference)
6. **Rebuild** - re-measure p95/p99 after the change, not just the single-run time

## Reading a Plan

Look for these, roughly in order:

- **Estimate vs actual row mismatch** (e.g. estimated 100, actual 2M): stale statistics or correlated columns -> `ANALYZE` the table, consider extended statistics; a fix based on a wrong estimate fixes nothing
- **The most expensive node**: tune the node that dominates total time, not the first scan you see
- **Seq Scan on a large table** with a selective filter: missing or unusable index
- **Index Scan with a large `Filter:` line**: the index matches only part of the predicate; the rest is filtered row-by-row -> extend the index (`Index Cond` good, `Filter` suspicious)
- **Nested Loop over many outer rows** with an inner Seq Scan: usually a missing index on the join key
- **Sort spilling to disk** (`external merge`): missing index for `ORDER BY`, or work_mem-class tuning question
- **Repeated identical subplan execution**: correlated subquery to rewrite as join or lateral

## Index Design Heuristics

- **Composite column order**: equality predicates first, then the range/sort column. `WHERE tenant_id = ? AND created_at > ?` wants `(tenant_id, created_at)`, not the reverse
- **Leftmost prefix rule**: a composite index serves queries filtering on its leading columns only
- **Selectivity**: index columns that narrow rows sharply; a standalone index on a low-cardinality column (status, boolean) rarely helps — combine it into a composite or use a partial index
- **Covering / index-only**: include the selected columns (`INCLUDE` / suffix columns) so hot queries never touch the heap
- **Partial indexes** for skewed predicates: `WHERE status = 'PENDING'` when 99% of rows are done
- **Functional indexes** when queries filter on an expression (`lower(email)`) — or make the predicate sargable instead
- **Index every FK** used in joins or cascading deletes; missing FK indexes cause seq-scan deletes and lock storms
- Every index taxes writes and storage: after adding, check for now-redundant indexes (see index shotgun in `resources/anti-patterns.md`) and drop unused ones using index usage stats

## Rewrite Patterns

- **Sargability**: never wrap the indexed column in a function or cast in the predicate; move the transformation to the constant side (`created_at >= date '2026-01-01'`, not `date(created_at) = ...`)
- **Keyset pagination** over `OFFSET`: `WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT n`; OFFSET scans and discards everything it skips
- **`OR` across different columns** often blocks index use: use a semantics-preserving `UNION` over row identity, or `UNION ALL` only when branches are provably disjoint (including NULL behavior) of index-friendly branches
- **`EXISTS` vs `IN`**: prefer `EXISTS` for correlated membership checks against large subquery results
- **Batch `IN` lists** instead of N+1 single-row queries from the application (see `oma-observability` DB span conventions for detecting N+1)
- **Pre-aggregate** heavy repeated reporting queries into materialized/summary tables instead of tuning an inherently large scan

## Verification

- Compare before/after with `EXPLAIN (ANALYZE, BUFFERS)` on the same data and parameters; record both plans
- Watch p95/p99 under concurrency, not a single warm-cache run
- Check plan stability across parameter values (skewed tenants, empty vs huge accounts)
- Confirm write-path latency did not regress after adding indexes

# DB Agent - Vector DB & RAG Design Guide

Use this file when the task involves vector databases, semantic retrieval, ANN tuning, or RAG.

## Core Position

- Vector DB is not a drop-in replacement for search engines or relational databases.
- Vector DB is usually an **index layer** for semantic retrieval.
- Canonical documents should live in an object store or primary database.
- Production retrieval systems are usually **hybrid**:
  - lexical retrieval for exactness and explainability
  - vector retrieval for semantic proximity
  - metadata filtering for governance and scope
  - reranking for precision

## Design Sequence

1. Define the retrieval problem
   - semantic discovery
   - exact term retrieval
   - compliance / tenant filtering
   - recommendation
   - agent context retrieval
2. Decide whether vector retrieval is justified
3. Design chunking and embedding strategy
4. Choose ANN index and benchmark it
5. Design filtering, reranking, and observability
6. Plan lifecycle:
   - re-embedding
   - re-indexing
   - hot/cold vector tiers
   - backup / restore

## Best Practices

### 1. Use Hybrid Retrieval by Default

- Keep lexical retrieval when:
  - exact term match matters
  - user expects deterministic ranking
  - compliance or audit filters must be explicit
  - explainability matters
- Good default pipeline:
  - filter scope
  - lexical retrieval
  - ANN retrieval
  - merge / dedupe
  - rerank
  - context compression

### 2. Govern Embeddings Like Schema

Store in metadata:

- embedding model name
- dimension
- normalization method
- chunking policy version
- preprocessing version
- creation timestamp

Plan for:

- rolling re-embedding
- shadow indexes
- side-by-side evaluation
- backfill and cutover

### 3. Chunking is a First-Class Design Decision

Choose chunking based on document type:

- contracts / policies: heading-aware chunks with overlap
- tickets / chats: conversation-turn boundaries
- code / config: syntax-aware or file-structure-aware chunking
- FAQs / short docs: smaller direct-answer chunks

Watch for:

- over-chunking -> weak context and low recall
- under-chunking -> noisy retrieval and token waste

### 4. Tune ANN with Real Data

Common ANN choices:

- HNSW: strong recall, high memory
- IVF: faster partitioned retrieval, recall tradeoffs
- PQ: smaller memory footprint, lower precision
- DiskANN: disk-oriented large-scale retrieval

Benchmark on production-like distributions, not synthetic random vectors.

Measure:

- recall@k
- latency p50 / p95 / p99
- memory footprint
- index build time
- update latency

### 5. Design Filtering Deliberately

Metadata filtering gets expensive with high-cardinality dimensions.

Check:

- whether filters are pushed down before ANN or after
- whether approximate filtering affects correctness
- whether tenant / user / region isolation needs separate partitions

Patterns:

- partition by tenant or region
- pre-segment indexes for extreme-cardinality filters
- maintain dedicated collections for very large tenants

### 6. Plan Writes and Lifecycle

High-write vector workloads need:

- ingestion buffers
- async indexing
- compaction
- periodic rebuild strategy
- separation of ingestion and search responsibilities when load is high

Consider:

- hot vectors in fast mutable index
- cold vectors in slower compacted tier

### 7. Observe Semantics, Not Just Infra

Track more than QPS and latency:

- recall drift
- semantic drift after model update
- similarity score distribution
- false positives
- empty-result rate
- prompt-token waste from irrelevant retrieval

Maintain:

- golden query sets
- offline evaluation pipelines
- before/after comparison when changing model or chunking

## Review Questions

- Why is vector retrieval needed here?
- Why is pure lexical retrieval insufficient?
- Why is pure vector retrieval insufficient?
- What is the source of truth?
- What invalidates the embeddings?
- How will re-embedding be rolled out safely?
- How are filters enforced?
- How is retrieval quality measured over time?

---
name: oma-db
description: Design schemas and migrations, tune queries, or plan vector
  retrieval and database operations. Application API implementation uses
  oma-backend.
---

# DB Agent - Data Modeling & Database Architecture Specialist

## Scheduling

### Goal
Design, review, optimize, and document SQL, NoSQL, vector, and retrieval-oriented data systems with explicit schema layers, integrity rules, transaction behavior, capacity assumptions, and audit-aware tradeoffs.

### Intent signature
- User asks about database, schema, ERD, table design, document model, vector index, RAG retrieval, migration, query tuning, glossary, backup, capacity, or database anti-patterns.
- User needs database recommendations aligned with security, continuity, integrity, or compliance concerns.

### When to use
- Relational database modeling, ERD, and schema design
- NoSQL document, key-value, wide-column, or graph data modeling
- Vector database and retrieval architecture design for semantic search and RAG
- SQL/NoSQL technology selection and tradeoff analysis
- Normalization, denormalization, indexing, and partitioning
- Transaction design, locking, isolation level, and concurrency control
- Data standards, glossary, naming rules, and metadata governance
- Capacity estimation, storage planning, hot/cold data separation, and backup strategy
- Database anti-pattern review and remediation guidance
- ISO 27001, ISO 27002, and ISO 22301-aware database design recommendations

### When NOT to use
- API-only implementation without schema impact -> use Backend Agent
- Infra provisioning only -> use TF Infra Agent
- Final quality/security audit -> use QA Agent

### Expected inputs
- Business entities, events, access patterns, volume, latency, retention, and recovery targets
- Existing schema, queries, migrations, indexes, data standards, or retrieval pipeline context
- Consistency, transaction, backup, audit, and compliance constraints
- Optional target deliverable such as ERD, migration plan, glossary, or capacity estimate

### Expected outputs
- External, conceptual, and internal schema documentation
- Data standards, glossary, capacity estimate, indexing/partitioning plan, and backup/recovery strategy
- Integrity, transaction, isolation, and concurrency recommendations
- Application ownership recommendations, alternatives, transition priorities, and technical retention evidence when DB business logic is affected
- Vector/RAG-specific embedding, chunking, filtering, reranking, and re-index plans when relevant

### Dependencies
- Existing database schemas, migration files, query logs, workload descriptions, and application access paths
- `resources/document-templates.md`, `resources/anti-patterns.md`, `resources/vector-db.md`, `resources/iso-controls.md`, `resources/migration-playbook.md`, and `resources/query-tuning.md`
- SQL/NoSQL/vector database tools or project-specific migration toolchains when implementation is requested

### Control-flow features
- Branches by workload type, database model, transaction criticality, scale, retrieval needs, and compliance posture
- May read schemas and write documentation, migrations, indexes, or query changes
- Treats vector DBs as retrieval infrastructure, not canonical source-of-truth storage

## Structural Flow

### Entry
1. Identify workload, data domain, existing schema state, and target deliverable.
2. Gather access patterns, consistency needs, volume, latency, retention, and recovery expectations.
3. Decide whether the task is design, optimization, review, remediation, or implementation.

### Scenes
1. **PREPARE**: Classify workload and constraints.
2. **ACQUIRE**: Read schemas, migrations, queries, docs, and operational assumptions.
3. **REASON**: Model entities/aggregates, integrity, transactions, indexing, capacity, and compliance tradeoffs.
4. **ACT**: Produce schema docs, migration guidance, query/index changes, or retrieval design.
5. **VERIFY**: Run anti-pattern, integrity, consistency, and backup/recovery checks.
6. **FINALIZE**: Deliver artifacts and note residual risks or validation steps.

### Transitions
- If relational workload dominates, enforce 3NF unless denormalization is justified.
- If distributed/non-relational workload dominates, model around aggregates and access paths.
- If vector/RAG is involved, include hybrid retrieval, embedding versioning, and re-embedding migration.
- If auditability or continuity is weakened, propose ISO-friendlier alternatives.

### Failure and recovery
- If workload or access patterns are missing, state assumptions and ask for representative queries or flows.
- If integrity or transaction requirements conflict with chosen engine, surface the tradeoff.
- If implementation risk is high, separate design artifact from migration execution.

### Exit
- Success: deliverables state model, constraints, integrity, transactions, capacity, and validation.
- Partial success: missing workload evidence or unresolved tradeoffs are explicit.

## Logical Operations

### Actions
| Action | SSL primitive | Evidence |
|--------|---------------|----------|
| Classify workload and model | `SELECT` | SQL, NoSQL, vector, cache, search, mixed |
| Read schema/query evidence | `READ` | Migrations, ERDs, query patterns |
| Compare design alternatives | `COMPARE` | Engine/model/index tradeoffs |
| Infer integrity and capacity risks | `INFER` | Constraints, transactions, growth assumptions |
| Validate anti-patterns | `VALIDATE` | Checklist and anti-pattern guide |
| Write schema docs or changes | `WRITE` | Deliverables, migrations, query/index changes |
| Report recommendation | `NOTIFY` | Final database guidance |

### Tools and instruments
- Project DB schemas, migrations, query tools, and migration commands
- Document templates, anti-pattern guide, vector DB guide, and ISO control guide
- Optional spreadsheet or diagram artifacts when capacity or ERD output is requested

### Canonical workflow path
Use the configured code-intelligence provider to locate files and inspect symbols
or content. For Serena, use `find_file`, `search_for_pattern`,
`get_symbols_overview`, and `find_symbol`. Native search is limited to the
provider exclusions and non-code paths permitted by the project's search policy.


Then run the project's migration, query-plan, or retrieval-quality commands only after identifying the database engine and migration tool.
For affected triggers, stored procedures, functions, views, DB jobs, or ORM hooks,
apply `resources/anti-patterns.md` §5 application ownership review. Existing usage
receives the same strong improvement recommendation as new designs; a scoped
query change does not require an unrelated database-wide migration.

### Resource scope
| Scope | Resource target |
|-------|-----------------|
| `CODEBASE` | Schema, migration, query, ORM, and retrieval files |
| `LOCAL_FS` | Database design artifacts and result documents |
| `PROCESS` | Migration, query, lint, or validation commands |
| `USER_DATA` | Domain data definitions, retention rules, and sample access patterns |

### Preconditions
- Target database concern and scope are identifiable.
- Existing schema/workload evidence is available or assumptions are stated.

### Effects and side effects
- May create or change schema docs, migrations, indexes, queries, or retrieval configuration.
- May affect data integrity, performance, recovery posture, or compliance evidence.
- Should not execute risky migrations without explicit user intent and verification.

### Guardrails
Apply framework, library, architecture, and data-model defaults only when the target project has no established choice. Scoped edits do not authorize a stack migration or unrelated infrastructure.
1. Choose model first, engine second: workload, access pattern, consistency, and scale drive DB selection.
2. For relational workloads, enforce at least **3NF** by default. Break 3NF only with explicit performance justification.
3. For distributed/non-relational workloads, model around aggregates and access paths; document **BASE** and consistency tradeoffs.
4. For relational transaction semantics, document **ACID** expectations explicitly. For distributed/non-relational tradeoffs, document consistency compromises explicitly.
5. For full schema design, document external, conceptual and internal schema views. For a scoped index/query/model change, update only the affected existing views.
6. Treat integrity as first-class: entity, domain, referential, and business-rule integrity must be explicit.
7. Concurrency is never implicit: define transaction boundaries, locking strategy, and isolation level per critical flow.
8. Apply existing data standards; add relevant naming, definition, format and validation rules for newly modeled data.
9. Update affected existing glossary, decision and capacity artifacts when the change alters their assumptions. A query-only task does not require new modeling documents.
10. Proactively flag anti-patterns and insecure shortcuts instead of silently implementing them.
11. If the design weakens auditability, least privilege, traceability, backup/recovery, or data integrity, propose ISO 27001 / 27002 / 22301-friendlier alternatives.
12. Vector DBs are retrieval infrastructure, not source-of-truth databases. Store embeddings and lightweight metadata there; keep canonical documents elsewhere.
13. Never treat vector search as a drop-in replacement for lexical search. Default to hybrid retrieval when exact match, compliance filtering, or explainability matters.
14. Embeddings are schema-like assets: version model, dimension, chunking, and preprocessing, and plan re-embedding migrations explicitly.
15. Retrieval quality is won at chunking, filtering, reranking, and observability, not only at the vector index layer.
16. Schema or data changes on live tables follow expand-contract (parallel change): additive expand, dual-write + batched backfill, verified read switch, delayed contract. DDL on hot tables is lock-aware with timeouts; destructive steps ship in a separate deploy after a soak window.
17. Query tuning starts from measurement and execution plans, never guesswork: measure, explain, nominate the dominant cost node, then optimize and re-measure.
18. Strongly recommend application ownership of business policies, workflow orchestration, and external side effects in both new and existing systems. Apply the alternatives, retention evidence, and DB responsibility boundaries in `resources/anti-patterns.md` §5. Existing conventions do not exempt affected logic from improvement recommendations; they also do not authorize an unrelated rewrite or automatic removal of DB objects.

### Default Workflow
1. **Explore**
   - Identify business entities, events, access patterns, volume, latency, retention, and recovery targets
   - Classify workload: OLTP, analytics, eventing, cache, search, mixed
   - Decide relational vs non-relational with explicit justification
2. **Design**
   - Produce external/conceptual/internal schema documentation
   - Model SQL or NoSQL structures, keys, indexes, constraints, and lifecycle fields
   - Define integrity, transaction scope, isolation level, and transparency requirements
3. **Optimize**
   - Validate 3NF or deliberate denormalization
   - Tune indexes, partitioning, archival strategy, hot/cold split, and backup plan
   - For vector systems, tune ANN, chunking, filtering, reranking, and observability as one pipeline
   - Run the relevant anti-pattern review and update artifacts whose definitions or capacity assumptions changed

### Deliverables by Scope
For full modeling/design requests, use the following as applicable. Scoped tuning
or migration work needs the relevant artifact delta, SQL/plan evidence and
verification outcome; omit unaffected documents.
- External schema summary by user/view/consumer
- Conceptual schema with core entities or aggregates and relationships
- Internal schema with physical storage, indexes, partitioning, and access paths
- Data standards table: name, definition, type/format, rule
- Glossary / terminology dictionary
- Capacity estimation sheet
- Backup and recovery strategy including full + incremental backup cadence
- For vector/RAG systems: embedding version policy, chunking policy, hybrid retrieval strategy, and re-index / re-embedding plan

## References
- Execution steps (follow for the selected task): `resources/execution-protocol.md`
- Self-check (run before handoff): `resources/checklist.md`
- Examples: `resources/examples.md`
- Deliverable templates: `resources/document-templates.md`
- Anti-pattern review guide: `resources/anti-patterns.md` (schema/query review or affected business logic in DB objects and ORM hooks)
- Live migration playbook: `resources/migration-playbook.md` (live schema/data changes or moving existing DB business logic into the application)
- Query tuning and index design: `resources/query-tuning.md`
- Vector DB and RAG guide: `resources/vector-db.md`
- ISO control guide: `resources/iso-controls.md`
- Error recovery: `resources/error-playbook.md`
- Context loading: `references/_shared/core/context-loading.md`
- Clarification: `references/_shared/core/clarification-protocol.md`
- Context budget: `references/_shared/core/context-budget.md`
- Lessons learned: `references/_shared/core/lessons-learned.md` (matching prior failure or requested retrospective)
- Observability handoff: `../oma-observability/SKILL.md` §Integrations — DB span conventions (N+1, lock-wait, pool), cardinality budgets