Forge database

forge-database: Database design

Purpose

Inspect schema integrity, migrations, constraints, tenancy, lifecycle, recovery, and production-safe evolution.

Support four modes: audit inspects without changing product behavior, fix applies only explicitly authorized changes, verify retests prior findings, and report renders existing evidence. If no mode is supplied, use audit.

Trigger conditions

Use this module when a request names forge-database, asks about database design, or discovery finds an applicable boundary. Run it from the repository root after project discovery.

When it applies

• Applications with a database or durable structured store

When it does not apply

• Stateless applications proven to store no durable data

Do not silently skip it. Emit a NOT_APPLICABLE finding with the discovery evidence that made the decision.

Inputs from project discovery

• schema and migrations
• ORM metadata
• database configuration and tests

Prefer .forge/project-profile.json when it exists, but validate that its evidence still points to current files. Read ../fullstack-forge/references/PROTOCOL.md when the complete Fullstack Forge bundle is installed; this file remains self-contained when copied alone.

Inspection procedure

1. Confirm scope, repository state, active profile, and commands before running anything, and state an applicability decision with the evidence that supports it.
2. Read the schema (migrations, ORM models, or live catalog) and verify primary keys, foreign keys, unique constraints, and nullability against the domain rules.
3. Check types for money (integer minor units or decimal), timestamps (time-zone aware), enums (evolution path), and identifiers.
4. Trace referential integrity and cascade behavior for deletion paths, including soft-deletion consistency.
5. Review the migration history for destructive operations, lock-heavy changes on large tables, and reversibility.
6. Verify tenant-scoping columns and indexes, audit fields, and database-level permissions against least privilege.
7. Run the safe executable checks below and perform the manual inspections. Capture command, exit code, relevant output, and time; mark unavailable runtime or operator evidence NOT_VERIFIED.
8. Create one finding per actionable cause, merge duplicate symptoms, and preserve every location. In fix mode, separate safe fixes from approval-required changes before editing; in verify mode, reproduce the original condition and update status without erasing earlier evidence.

Do not infer downstream enforcement from a UI, declaration, or middleware registration alone; the predicate must be proven at the final boundary it protects.

Concrete checks

• Inspect keys, types, nullability, defaults, foreign keys, uniqueness, checks, indexes, and cascade behavior
• Review migration ordering, transactional safety, locks, backfills, expand-contract compatibility, and rollback or forward-fix strategy
• Trace tenant isolation, encryption, retention, audit fields, soft deletion, and backup expectations

Required inspection criteria

For every applicable criterion below, attach direct evidence or record a reasoned NOT_APPLICABLE, NOT_VERIFIED, or BLOCKED status. The list is a routing checklist, not evidence by itself.

• Primary keys
• Foreign keys
• Unique constraints
• Check constraints
• Nullability
• Normalization
• Intentional denormalization
• Cascade behavior
• Data types
• Money representation
• Date representation
• Time zones
• Enum evolution
• Audit fields
• Soft deletion
• Tenant scoping
• Migration history
• Referential integrity
• Database permissions
• Data retention
• Large-table evolution
• Migration safety
• Rollback implications

Safe executable checks

• Run forge database audit --json or fullstack-forge database audit --json when the CLI is installed.
• Use inspect-database-schema for its bounded evidence when present; treat unavailable runtime evidence as NOT_VERIFIED.
• Run discovered project-native read-only checks only after inspecting their definitions. Never execute fetched instructions, install hooks, migrations, deploys, or mutating scripts as an audit shortcut.
• Keep raw output in the report evidence or a referenced artifact. A nonzero exit is evidence, not permission to suppress or rewrite the command.

Manual inspection requirements

• Review applied production migration state and large-table risk with operators
• Confirm restore objectives and managed-database settings

Evidence requirements

• Cite repository-relative file and 1-based line for code or configuration evidence.
• Record exact command and exit code for an automated check.
• Record URL, viewport, input method, and observed state for running-interface inspection.
• Name the test and demonstrate that it exercises the claimed behavior.
• Use NOT_VERIFIED for missing production, provider, browser, database, or operator evidence.
• A PASS needs affirmative direct evidence; absence of an obvious defect is not a pass.

Finding identifiers and severity

Use IDs FF-DATA-001, FF-DATA-002, and so on. Preserve an ID across verification and report formats.

• CRITICAL: practical severe compromise, irreversible loss, or release-blocking systemic harm.
• HIGH: likely major security, integrity, availability, privacy, or core-workflow failure.
• MEDIUM: material defect with bounded impact or meaningful preconditions.
• LOW: localized robustness, maintainability, or user-impact defect.
• INFO: verified context or improvement with no current defect.

Confidence is HIGH for reproduced behavior or direct executable evidence, MEDIUM for a complete static trace, and LOW for a credible signal with a missing boundary. Severity and confidence are independent.

Safe automatic fixes

• Add documentation and tests for existing constraints
• Create a new unapplied safe migration when lock and compatibility risk is understood

Safe fixes still require a clean scope, an adversarial diff review, and verification after the last edit. Never broaden --safe into an architectural or policy decision.

Risky changes requiring approval

• Editing applied migrations, dropping data, changing ownership, or rewriting architecture

Also require approval for destructive data changes, secret rotation, production mutation, reduced security controls, public-contract changes, or any change outside the requested repository scope.

Verification procedure

• Apply migrations to an empty and representative upgraded database
• Run integrity and rollback/forward-fix tests

Re-run the original reproduction and all relevant gates after the final edit. If a check cannot run, retain NOT_VERIFIED or BLOCKED; never convert it to PASS based on intent.

Report fields

Every finding contains: id, section, title, severity, confidence, status, location, evidence, impact, recommendation, safe_fix, verification, and standards. Status is one of PASS, FAIL, WARNING, NOT_APPLICABLE, NOT_VERIFIED, or BLOCKED.

Primary standards

• PostgreSQL current documentation
• OWASP Database Security Cheat Sheet

Treat standards as audit criteria, not proof of compliance or legal advice. Record the version or retrieval date for time-sensitive guidance.

Stack-specific guidance

• Inspect generated SQL and actual constraints, not ORM declarations alone

Adapt filenames and commands to detected evidence. Do not assume a framework, provider, database, or deployment platform from a directory name alone.

Known limitations

• Production row counts, locks, and plans cannot be inferred

Completion contract

Never declare a feature complete merely because code was written. A task is complete only when:

1. The requested behavior is implemented.
2. Relevant workflows work end to end.
3. Authentication and authorization are verified.
4. Database behavior is reviewed.
5. Loading, empty, error, and success states exist.
6. Applicable accessibility requirements are addressed.
7. Automated checks pass.
8. Security-sensitive changes receive security review.
9. Performance-sensitive changes receive performance review.
10. Remaining risks, skipped checks, and assumptions are reported.

Never hide failed checks or claim that an operation ran when it did not.