Database Schema & Migrations

Relevant source files

The following files were used as context for generating this wiki page:

• 2026-03-03-product-compare-backend-mvp-plan.md
• backend_starting_guide.md
• README.md
• ARCHITECTURE.md
• AGENTS.md

The database layer of the ProductCompare project is designed as a robust PostgreSQL 18 foundation for a modular monolith. It focuses on maintaining typed product data, taxonomy closure trees, and append-only claim histories. The system utilizes specific Ecto conventions to ensure microsecond precision for timestamps and a dual-ID system for internal and external referencing.

Sources: 2026-03-03-product-compare-backend-mvp-plan.md:1-15, backend_starting_guide.md:5-15, README.md:5-15

Core Architectural Conventions

The project enforces several non-negotiable database conventions to ensure consistency across the modular monolith.

Primary Key and Identity Strategy

All relational tables follow a strict ID pattern:

• Internal ID: A bigint primary key (id) used for internal foreign key targets and performance.
• External ID: An entropy_id using uuidv7 generated by PostgreSQL, serving as the unique identifier for external surfaces.
• Exceptions: Explicit UUID-primary-key exceptions are only permitted for specific tables like users_tokens.

Sources: backend_starting_guide.md:10-14, backend_starting_guide.md:117-122

Timestamp Precision

The system is configured to use :utc_datetime_usec across all schemas and migrations. This microsecond precision is critical for maintaining accurate history, such as pricing points and claim transitions.

Sources: backend_starting_guide.md:9, 2026-03-03-product-compare-backend-mvp-plan.md:105-107

Schema Organization and Boundaries

Data models are organized into logical boundaries that match the application's contexts. All Ecto schema modules are strictly "schema-only," meaning they contain only fields, associations, and changesets, while orchestration logic resides in context modules.

Logical Contexts

The schema is divided into the following primary boundaries:

Context	Purpose
Accounts	Users, reputations, and authentication tokens.
Taxonomy	Taxonomies, taxons (categories), and closure trees for hierarchical traversal.
Catalog	Core product anchors and brands.
Specs	Attributes, units, dimensions, and the append-only claim system.
Pricing	Merchant listings and historical price points.
Affiliate	Networks, programs, and referral links.
Discussions	Product threads, posts, and user reviews.

Sources: README.md:25-31, 2026-03-03-product-compare-backend-mvp-plan.md:13-15, 2026-03-03-product-compare-backend-mvp-plan.md:154-177

Migration Strategy and Entity Relationships

Migrations are executed in a dependency-safe order to ensure referential integrity. The order moves from base infrastructure (extensions and users) to complex domain entities like claims and discussions.

Migration Execution Order

1. Extensions: Enable citext and verify uuidv7() support.
2. Base Entities: users, brands, taxonomies.
3. Catalogs: products, units, dimensions, enum_sets.
4. Workflow Data: product_attribute_claims, merchant_products, price_points.
5. Social/Meta: product_reviews, affiliate_links.

Sources: 2026-03-03-product-compare-backend-mvp-plan.md:123-148, 2026-03-03-product-compare-backend-mvp-plan.md:325-337

Entity Relationship Diagram

The following diagram illustrates the relationships between the core catalog and specification entities.

Performance and Indexing

To support fast filtering and complex hierarchical lookups, the schema implements a strategic indexing plan.

Key Indexing Targets

• Taxonomy Traversal: A unique index on taxon_closure(ancestor_id, descendant_id) for subtree lookups.
• Product Filtering:
  • Unique (product_id, attribute_id) on product_attribute_current for fast spec retrieval.
  • Composite indexes on product_attribute_claims for value_num_base, value_bool, and enum_option_id.
• Pricing History: Indexing price_points(merchant_product_id, observed_at DESC) to efficiently fetch the latest prices.

Sources: 2026-03-03-product-compare-backend-mvp-plan.md:183-200

Security and Token Storage

The database layer implements specific security protocols for authentication tokens. Token secrets are never stored in raw form; they must be persisted as SHA3-256 hashes.

Token Table Structure

Tables like users_tokens include:

• id: DB-generated UUIDv7.
• secret_hash: Binary blob of the SHA3 hash.
• context: Enum defining the token's purpose (session, reset, etc.).
• sent_to: Email address for verification flows.

Sources: backend_starting_guide.md:104-115, AGENTS.md:25-35

Conclusion

The Database Schema & Migrations system provides a high-integrity, performant back-end for product comparison. By enforcing strict ID conventions, microsecond timestamping, and a modular schema structure, the project ensures data reliability while supporting complex features like taxonomy closure trees and append-only claim lifecycles.

Sources: 2026-03-03-product-compare-backend-mvp-plan.md:8-15, backend_starting_guide.md:117-133