/omh-relational-db

name: "omh-relational-db"
description: "[omh] Database work on a relational store -- a slow query, an index to size, a migration on a big table, a lock taken during deploy, N+1 queries: plan it with the checks that prove it safe, and never call a migration ready without its lock behaviour and rollback. Use when the user says: relational-db, relational database, online migration, migration for a large table, lock-safe ddl, alter table, took a lock, table lock."
metadata:
  hermes:
    tags: [workflow, oh-my-hermes, planning]
    category: planning
    phase: relational-db
    role: planner
    quality_tier: db-lock-and-rollback-gated

Relational Db

This is a Hermes-native `relational-db` workflow skill.

Why This Exists

`relational-db` exists because the database work itself had no owner: `backend` owns `schema_migration_plan/v1` for a service change, and an index question, a lock taken during deploy, or an N+1 finding was answered by a data, deploy, or interview lane with no lock model at all.

First Steps

• Ask for the engine, the version, and the table sizes before proposing any statement.
• For a migration, give every step its lock behaviour and its rollback before calling the plan ready.

Do Not Use When

• The request is a service or API change whose storage part is one section of the contract; use `backend`, which owns schema_migration_plan/v1.
• The request is an end-to-end performance goal across the whole system rather than one database; use `ultraperf`.
• The request is analysis of the data itself -- trends, metrics, a report; use `data-analysis`.
• The request is watching a release roll out with its health signals and rollback criteria; use `deploy-and-monitor`.

Examples

Good example:

• Prompt: write an online migration for a 200M row table
• Expected behavior: Ask for the engine and version, then prepare online_migration_plan/v1: expand, batched backfill, switch, and contract, each step with the lock it takes, its `lock_timeout`, and its rollback, and a readiness verdict.
• Why: At 200M rows the lock each statement takes decides whether the deploy is an outage.

Bad example:

• Prompt: just add the index, it will be fine
• Expected behavior: Size the index, cite the plan it changes, build it with the non-blocking method, and name what is unverified.
• Why: An index built with a blocking statement on a large table locks writes for the whole build.

Completion Checklist

• Engine, version, and table sizes are stated.
• Every proposed index cites an observed plan or is marked unverified.
• Every migration step states its lock mode, its duration bound, and its rollback.
• The readiness verdict is ready only when no step lacks lock behaviour or rollback.
• Nothing was connected to, run, or applied by OMH.

Recovery Notes

• If the engine or version is unknown, plan for the most restrictive lock behaviour and say so.
• If no query plan is available, ask for the observed plan before proposing an index, and mark any proposal unverified.

Workflow Lane

• Current lane: **Coding handoff** (`idea-to-deploy`, `llm-app-dev`, `cto-loop`, `deploy-and-monitor`, `code-review`, `build-failure-triage`, `verification-gate`, `security-safety-review`, `+28 more`) - coding owners, handoffs, review, CI, and merge evidence.
• If intent belongs to another lane, hand back to `oh-my-hermes` or name the adjacent workflow.
• Shared product, routing, compatibility, and evidence rules: `omh-routing/references/skill-common-rail.md`.

Use When

Use when the work is the database itself on a relational engine such as Postgres or MySQL: a slow query and the index that fixes it, an online migration on a large table, DDL that took or would take a lock during deploy, N+1 queries from an endpoint, or when to partition or shard. The output is a plan plus the checks that prove it safe to run; OMH never connects to a database.

Strong routing signals: `relational-db`, `relational database`, `online migration`, `migration for a large table`, `lock-safe ddl`, `alter table`, `took a lock`, `table lock`, `lock during deploy`, `create index concurrently`, `which index`, `what index`, `missing index`, `index size`, `seq scan`, `seq-scans`, `sequential scan`, `explain analyze`, `query plan`, `n+1`, `n+1 query`, `n+1 queries`, `need to shard`, `shard the database`, `database sharding`, `table partitioning`, `partition the table`, `index bloat`, `postgres lock`, `mysql online ddl`

Catalog Metadata

Category: `planning` Phase: `relational-db` Hermes role: `planner` Quality tier: `db-lock-and-rollback-gated` Reasoning demand: `standard`

Quality bar:

• State the engine and version first; lock behaviour differs by engine and by version.
• Load `references/engine-lock-tables.md` for the per-engine lock modes, the online DDL rules, and index-type selection instead of recalling them.
• Size an index before proposing it: rows, key width, and the write amplification it adds.
• Order an online migration as expand, backfill in batches, switch, contract, with the rollback for each step.
• Keep planned, observed, and applied as separate states for every statement.

Handoff policy:

Keep the problem statement, the index proposal, the migration plan with its lock behaviour and rollback, and the readiness verdict in Hermes. Query plans, row counts, lock waits, and every applied statement are recorded only from executor, operator, or wrapper observed output; OMH never connects to a database.

Required inputs:

• engine and version, and whether it is managed or self-hosted
• the table sizes involved, in rows and bytes, and the write rate
• the query, its observed plan (`EXPLAIN (ANALYZE, BUFFERS)` or the engine's equivalent), and its latency
• for a migration: the statements, the deploy mechanism, and the longest lock the service tolerates
• observed evidence for any readiness or completion claim

Expected outputs:

• db_problem_statement/v1
• query_plan_evidence/v1 when a query is involved
• ind