data-modeling

Files (1)

• SKILL.md

SKILL.md (first lines)

# data-modeling

Model for the queries you'll run, constrain what must never happen, and ship schema with its indexes.

## Activate when

- Designing tables/documents, constraints, indexes, or reviewing data-layer changes (engine-agnostic).

## Do NOT activate for

- Engine operations (see databases/*); domain rules (see domain-modeling).

## Inspection

Collect the access patterns (reads, writes, hot paths), growth estimates, consistency needs, and the current schema + slow-query evidence.

## Decision rules

- Identity: stable surrogate keys for references; natural keys only with uniqueness proof.
- Constraints in the database for invariants that must survive application bugs (uniqueness, FKs, CHECKs, not-null).
- Indexes matched to EXPLAIN output, not guessed; every index pays write cost — drop unused ones on evidence.
- Lifecycle: created/updated timestamps, versioning where concurrent edits collide, soft vs hard delete decided per audit needs, audit trail where compliance demands.
- Transactions sized to the workflow; migrations expand→migrate→contract.

## Procedure

1. Write the queries first, then the schema that serves them; EXPLAIN before merging.
2. Add constraints + indexes with the migration; include rollback.
3. Cover invariants with DB-level tests where the engine allows, else integration tests.
4. Verify: migration up+down on scratch data, EXPLAIN clean, no N+1 on hot paths.

## Failure modes

- EAV soup; missing FKs "for performance" without measurement; unbounded JSON blobs queried by regex; migrations without rollback; soft-delete breaking uniqueness.

## Escalation

Engine specifics → databases/postgres-operations, mysql, redis, mongodb; roles/security → databases/database-security.

## References

- Related: `../domain-modeling/`, `../../backend/database-design/`, `../../backend/migrations/`.

From chnetajibc/skill-repo, MIT.

More from chnetajibc/skill-repo