hashgraph-online/awesome-codex-plugins / plugins/alpoxdev/hypercore/skills/hyper-database
hyper-database
Unverified[Hyper] Use this skill when the user asks for database work - designing a data model or schema, reviewing or writing migrations, tuning slow queries and indexes, choosing an engine, or DBA operations such as backup and restore, replication and failover, connection pooling, security, upgrades, and monitoring - on PostgreSQL, MySQL or MariaDB, SQLite, Redis, Valkey, MongoDB, DynamoDB, Cassandra or ScyllaDB, or distributed and serverless SQL. It detects the engines from repository files, applies the matching engine guide bundled in this skill, and saves a report under `.hyper/database/[slug]/`. Do not use it for application code that changes no schema, query, index, or database setting.
rules/data-modeling.md
View on GitHub# Data Modeling
Use this rule in design, review, and choose modes, and whenever a key, constraint, tenancy, or cross-store consistency question comes up. It is engine-neutral; each engine guide adds the engine's own types, limits, and syntax. Findings cite the `DM-*` IDs below.
## 1. Method
Work in this order and write each step's result into the report.
1. **Access-pattern table.** List every read and write: who calls it, the filter, the sort, the page size, the frequency, and the latency target. A design without this table is a guess (DM-01).
2. **Entities and invariants.** Name each entity, its identifier, its cardinality to the others, and the rules that must never break (unique email per tenant, one active subscription, balance never negative).
3. **Engine fit.** Check the table against §7 before committing to an engine. An existing engine stays unless a hard requirement fails.
4. **Logical model.** Normalize to third normal form (or BCNF) first, then denormalize only for a named access pattern that needs it, and write down which copy is the source of truth (DM-02).
5. **Physical model.** Types, keys, constraints, indexes, partitioning or key layout, retention, and the expected size of each table or key family after one year.
6. **Operations consequences.** Backup size, restore time, hot-spot risk, and the monitoring each new structure needs (`rules/operations.md`).
For key-value, document, and wide-column engines, steps 4-5 invert: the access-pattern table *is* the model. Shape each item, document, or partition so one request reads what one access pattern needs (DM-03).
Normalization smells to flag in review (DM-04): a comma-separated ID list or JSON array of foreign keys in one column, numbered columns (`phone1`, `phone2`), a non-key column that depends on part of a composite key, and the same fact stored in two tables with no named writer for the copy.
## 2. Identifiers
| ID | Severity | Rule |
|---|---|---|
| DM-10 | high | Every table or collection has a primary key that never changes. Use a surrogate key when the natural key can change (email, phone, name) and enforce the natural key with a separate unique constraint. |
| DM-11 | medium | Prefer a 64-bit integer identity for internal, single-writer, high-write tables: 8 bytes, ordered inserts, compact secondary indexes. |
| DM-12 | medium | Prefer a time-ordered UUID (UUIDv7, RFC 9562) over a random UUIDv4 when IDs are generated by clients or several writers, merged across systems, or exposed in URLs. Random v4 keys scatter B-tree inserts and inflate index size and write I/O; v7 keeps inserts near the right edge. |
| DM-13 | medium | Do not expose sequential integers where enumeration leaks business data or enables scraping; expose a UUID or an opaque public ID beside the internal key. This is information hiding only: an ID is never an authorization capability, so every access is still checked. UUIDv7 embeds a millisecond timestamp, so do not use it where creation time is sensitive. |
| DM-14 | high | In distributed or partitioned stores, a monotonic key (timestamp, sequence, UUIDv7 as the *partition* key) concentrates writes on one shard or partition. Use a hashed, high-cardinality partition key and keep time in the sort or clustering key. |
| DM-15 | medium | Store UUIDs in a native 16-byte type (`uuid`, `BINARY(16)`), not as 36-character text. |
## 3. Integrity
| ID | Severity | Rule |
|---|---|---|
| DM-20 | high | Enforce every invariant in the database when the engine can: `NOT NULL`, `UNIQUE`, `CHECK`, foreign keys, exclusion constraints. Application checks race under concurrency. |
| DM-21 | high | A uniqueness rule that depends on state (one active row per user) needs a partial or filtered unique index, not an application check. Remember that `UNIQUE` treats `NULL`s as distinct by default, and a `CHECK` passes when its expression is `NULL`; add `NOT NULL` or `NULLS NOT DISTINCT` where that matters. |
| DM-22 | medium | Foreign keys need an index on the referencing column in engines that do not create one (PostgreSQL); otherwise deletes on the parent scan the child. |
| DM-23 | medium | Where foreign keys are not enforced (most sharded MySQL setups, NoSQL), state who guarantees the reference and how orphans are found and cleaned. |
| DM-24 | high | Money is an exact type: `numeric`/`DECIMAL` with fixed scale, or an integer of minor units with the currency beside it. Never a binary float. |
| DM-25 | high | Store instants as UTC in a timezone-aware type (`timestamptz`) or as UTC by convention with the convention documented; store the user's time zone name separately when local time matters. |
| DM-26 | medium | Use a lookup table or a `CHECK` list for values that grow or carry attributes; a native enum is acceptable only for small, stable sets whose changes you can deploy as migrations. |
| DM-27 | medium | Soft delete (`deleted_at`) is a decision with costs, not a default: it breaks unique constraints, aggregates, and pagination, conflicts with erasure requests, and grows the table. Use it only with partial unique indexes and a view or default scope, or move deleted rows to an archive table. |
| DM-28 | medium | A JSON column is fine for optional, sparse, or schemaless attributes that are read whole. Fields that are filtered, joined, constrained, or updated often become real columns. |
| DM-29 | low | Name consistently: one casing style, singular or plural chosen once, `<entity>_id` for references, and `created_at`/`updated_at` everywhere they apply. |
## 4. Transactions and Concurrency
| ID | Severity | Rule |
|---|---|---|
| DM-30 | high | Name the isolation level each multi-statement write needs and the anomaly it prevents (lost update, write skew, phantom). Default `READ COMMITTED` does not prevent write skew. |
| DM-31 | high | Read-modify-write on a contended row uses `SELECT ... FOR UPDATE`, an atomic `UPDATE ... SET x = x + 1`, or a version column with a conditional update. Never read, compute in the app, and write back unguarded. |
| DM-32 | high | Every externally retried write (payments, webhooks, queue consumers) carries an idempotency key with a unique constraint. |
| DM-33 | high | Do not call external services inside a database transaction; keep transactions short. To publish an event with a write, use a transactional outbox table read by a relay. |
| DM-34 | medium | Code that runs at `SERIALIZABLE`, or against engines that abort on conflict (CockroachDB, optimistic stores), retries the whole transaction on serialization failures with a bounded backoff. |
| DM-35 | medium | Acquire row locks in a consistent order across code paths to avoid deadlocks; handle the deadlock error by retrying once. |
## 5. Multi-Tenancy
| Model | Use when | Costs |
|---|---|---|
| Shared tables with `tenant_id` | Many small tenants, one schema | Every query, unique constraint, and index leads with `tenant_id`; isolation depends on the filter or on row-level security |
| Schema or database per tenant | Few large tenants, per-tenant restore or residency | Migrations fan out to every tenant; connection and catalog overhead grows with tenant count |
| Shard by tenant | Tenants outgrow one node | Cross-tenant queries become scatter-gather; rebalance plan required |
| ID | Severity | Rule |
|---|---|---|
| DM-40 | critical | In shared tables, tenant isolation is enforced below the application when the engine allows it (PostgreSQL row-level security with a session variable, or a mandatory repository filter covered by tests). A missing filter is a data leak. |
| DM-41 | medium | Composite keys and indexes start with `tenant_id`, and unique constraints include it (`UNIQUE (tenant_id, email)`). |
## 6. Derived Stores and Consistency
| ID | Severity | Rule |
|---|---|---|
| DM-50 | high | Every cache, search index, read replica, or denormalized copy names its source of truth, its staleness bound, and how it is rebuilt from scratch. |
| DM-51 | high | Cache-aside writes update the source first, then delete (not set) the cache key; deleting avoids racing a stale write over a fresh one. |
| DM-52 | medium | Replica reads are stale by the replication lag; route read-your-writes paths to the primary or wait for the replica to catch up. |
| DM-53 | medium | A queue or stream feeding another store needs idempotent consumers and a dead-letter path; at-least-once delivery is the default assumption. |
## 7. Engine Choice
| Workload signal | Fits | Poor fit |
|---|---|---|
| Relational data, ad hoc queries, transactions across entities, reporting on the same data | PostgreSQL, MySQL | Key-value stores |
| Embedded, single-writer, local-first, edge reads | SQLite (libSQL, D1 when served at the edge) | Many concurrent writers |
| Sub-millisecond reads of derived data, counters, rate limits, sessions, queues, leaderboards | Redis, Valkey | System of record for data you cannot rebuild |
| Aggregate documents read and written whole, flexible attributes, one entity per request | MongoDB | Many-to-many joins and cross-document invariants |
| Known access patterns at large, spiky scale with managed operations | DynamoDB | Ad hoc querying, unknown patterns |
| Very high write throughput, time-ordered partitions, multi-region active-active | Cassandra, ScyllaDB | Joins, transactions, frequent schema change |
| Time-series ingest (sensors, metrics, events) with recent-window reads per entity | TimescaleDB or native partitioning on PostgreSQL for moderate rates; Cassandra/ScyllaDB with time-bucketed partitions for very high write rates; ClickHouse when reads are aggregates and inserts can be batched. Dedicated time-series engines (InfluxDB, QuestDB) are outside the bundled guides | Row-at-a-time updates of old points |
| Horizontal SQL with strong consistency across regions | CockroachDB, YugabyteDB, TiDB, Vitess | Small single-region apps (operational cost) |
| Analytics over large append-mostly data | ClickHouse, a warehouse | OLTP point updates |
| ID | Severity | Rule |
|---|---|---|
| DM-60 | high | A relational engine is the default system of record unless a hard requirement in the access-pattern table fails on it; add a specialized store beside it for the pattern that needs it. |
| DM-61 | medium | Each extra engine is justified by an access pattern it serves and costs a backup, a monitoring, and an on-call surface; write that cost in the report. |
| DM-62 | medium | Check the license and the managed-service availability of each candidate in the engine guide's dated facts before recommending it. |
## Sources
> Checked 2026-11-07.
| Claim | Source |
|---|---|
| UUIDv7 layout and its time-ordered, sortable property; the timestamp it exposes | <https://www.rfc-editor.org/rfc/rfc9562.html> |
| B-tree index locality, column order, and the cost of indexes on writes | <https://use-the-index-luke.com/> |
| Isolation levels and the anomalies each permits | <https://www.postgresql.org/docs/current/transaction-iso.html> |
| Row-level security policies | <https://www.postgresql.org/docs/current/ddl-rowsecurity.html> |
| The transactional outbox pattern | <https://microservices.io/patterns/data/transactional-outbox.html> |
| Access-pattern-first modeling and partition key distribution for NoSQL stores | <https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-general-nosql-design.html> |
| The method order, the IDs, and the severities | this package's chosen design, not a validated optimum |
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| rea-analysis-optimizationmorluto/reaOptimize REA providers and shared analysis workflows for agent context, latency, CPU, and memory while preserving correctness and Evidence. Use for analysis bottlenecks and cache or query design; CI/test-suite optimization belongs to rea-ci-tests-audit. | Stars: 464k | Updated | Claude CodeCodex | Verified | Install |
| rea-ci-tests-auditmorluto/reaAudit REA CI and test suites for correctness, fixture drift, false passes, and measured performance; use for suite or workflow investigations and efficiency changes, rather than ordinary feature testing or a single test failure. | Stars: 464k | Updated | Claude CodeCodex | Verified | Install |
| rea-tool-designmorluto/reaAudit, investigate, design, or change REA investigation tools, CLI/MCP contracts, provider capabilities, and Evidence semantics. | Stars: 464k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-auditDietrichGebert/ponytailQuality audit of the whole repo: bugs, security, real load, missing tests, speed, and what to delete. Most important first. | Stars: 192k | Updated | Claude CodeCodex | Verified | Install |