Skip to content

gracefullight/pkgs / .agents/skills/oma-db

oma-db

Verified

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

resources/execution-protocol.md

View on GitHub
# DB Agent - Execution Protocol

## Preparation
Use the task's scope, existing project conventions, and acceptance criteria. Follow `../../_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
- 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
- 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)
- 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`
- Confirm every schema change updated:
  - 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
- 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.
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall