[Product Gap] Add evidence-backed 3NF, functional-dependency, and hot-partition assessment

[seonghobae]

Buyer-visible problem

pg-erd-cloud can already introspect database objects, lint naming, detect wide tables, and export schema artifacts. It cannot yet give an architect a defensible answer to three questions that enterprise buyers routinely ask:

1. Which relations appear to violate normalization or mix independent facts?
2. Which findings are certain, inferred, or intentionally excepted?
3. Which metadata, queue, audit, snapshot, or target-schema objects are likely to become hot partitions under the expected workload?

Protected main@8dc746920c12988f082e914879d95e13c9693535 also stores immutable schema payloads and queue payloads as JSONB. Those may be justified evidence envelopes, but JSON storage must not be described as third-normal-form proof. A complete product needs an explicit analysis contract and a documented exception boundary.

Product contract

Add a versioned, non-destructive Schema Quality & Operability Assessment that works on both pg-erd-cloud's own metadata database and authorized imported snapshots.

Normalization and dependency evidence

• model candidate keys, superkeys, foreign keys, uniqueness, nullability, generated columns, domains, and check constraints;
• infer only dependencies supported by catalog evidence, profiling evidence, or user-declared rules;
• distinguish observed, declared, inferred, proposed, and waived findings;
• detect repeated independent facts, partial-key dependency candidates, transitive dependency candidates, multi-valued attributes, and overloaded polymorphic columns without asserting a theorem from column names alone;
• provide source object references, rationale, confidence/evidence class, false-positive caveats, and a concrete next action;
• support signed waivers with owner, reason, review date, scope, and expiry;
• never auto-normalize or execute DDL from an advisory finding.

Hot-partition and growth evidence

• model write/read concentration by tenant/project/status/time key;
• identify queue, audit, snapshot, share-link access, and event tables that may concentrate writes or unbounded retention;
• emit partition/index/retention candidates only after workload evidence or an explicit capacity profile;
• verify partition-pruning assumptions with generated EXPLAIN/EXPLAIN ANALYZE fixtures on a real PostgreSQL database;
• include planning-time and memory caveats for excessive partition counts;
• preserve global uniqueness and foreign-key semantics when a partition strategy is proposed.

Product output

A versioned report must contain:

assessment_run
assessment_scope
schema_fingerprint
finding_record
evidence_record
waiver_record
capacity_profile
partition_candidate
remediation_action

Database object names must remain two-or-more-word snake_case. The report must be available as JSON, accessible HTML/exact-value table, and a buyer-facing summary. Findings must link to the ERD object and explain the customer's next action.

Required tests

• known 1NF/2NF/3NF/BCNF examples and deliberately denormalized but justified schemas;
• composite-key partial dependency and transitive dependency fixtures;
• catalog evidence that is insufficient to infer a dependency;
• multilingual/quoted identifiers and schema-qualified collisions;
• target tables with JSONB evidence envelopes that are explicitly waived rather than falsely normalized;
• time-, tenant-, hash-, and status-skew workloads;
• PostgreSQL 14–18 partition-pruning and uniqueness constraints;
• deterministic output under catalog row reordering;
• large-schema resource bounds and cancellation;
• production statement/branch coverage 100% and public API docstrings 100%.

Acceptance evidence

• ADR for evidence classes, normalization claim boundary, JSONB exceptions, and partition decision policy;
• 3NF relational persistence for assessment metadata;
• real seeded PostgreSQL integration tests and workload simulation;
• exact-current-head checks and independent review;
• baseline, architecture, PRD/TRD, data model, doctoring, and changelog updated.

References — APA 7th

Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377–387. https://doi.org/10.1145/362384.362685

Fagin, R. (1977). Multivalued dependencies and a new normal form for relational databases. ACM Transactions on Database Systems, 2(3), 262–278. https://doi.org/10.1145/320557.320571

PostgreSQL Global Development Group. (2026). PostgreSQL 18 documentation: Table partitioning. https://www.postgresql.org/docs/18/ddl-partitioning.html