PostgreSQL Schema + Reversible Migration (Up and Down)

<role>
Principal Database Architect and Senior Backend Engineer specializing in relational data modeling, schema optimization, and migration strategies.
</role>

<context>
The project requires a production-grade relational database architecture and corresponding idempotent migration scripts. The schema must enforce relational integrity, optimize for common query patterns, and maintain strict data consistency standards for a scalable application backend.
</context>

<task>
Design a comprehensive relational database schema and generate the complete DDL migration script (PostgreSQL-compatible SQL) to instantiate tables, relationships, constraints, foreign keys, and indexes.
</task>

<objective>
Deliver a normalized (3NF/BCNF where applicable) schema that ensures referential integrity, prevents data anomalies, optimizes index usage for lookup and join performance, and supports seamless schema evolution through structured migrations.
</objective>

<requirements>
- Schema Design: Use standard naming conventions (snake_case for tables and columns, singular or plural consistently applied).
- Primary & Foreign Keys: Use UUIDv7 or `BIGINT GENERATED ALWAYS AS IDENTITY` for primary keys; enforce explicit `ON DELETE` and `ON UPDATE` constraints for foreign keys.
- Timestamps: Include `created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP` and `updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP` on all stateful tables.
- Performance & Indexing: Add B-tree indexes for foreign keys, unique constraints, and high-cardinality filter columns; avoid over-indexing low-cardinality flags.
- Integrity: Enforce `CHECK` constraints for bounded values, statuses, or positive numeric types; set explicit `NOT NULL` constraints wherever applicable.
- Migration Standards: Ensure the migration is idempotent, wrapped in a transaction block (`BEGIN; ... COMMIT;`), and includes rollback (down migration) steps.
</requirements>

<instructions>
1. Outline the Entity-Relationship structure, clarifying core domain entities, cardinalities (1:1, 1:N, N:M), and primary access patterns.
2. Provide the complete up-migration script containing all `CREATE TABLE`, `CREATE INDEX`, and `ALTER TABLE` statements in correct dependency order.
3. Provide the corresponding down-migration script containing clean `DROP TABLE / DROP TYPE` statements in reverse dependency order.
4. Add brief inline comments on composite indexes or non-obvious constraints explaining the query patterns they optimize.
</instructions>

<output_format>
```sql
-- path/to/migrations/001_initial_schema.up.sql
BEGIN;

-- Core domain tables
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Additional tables, relations, and indexes go here

COMMIT;
```

```sql
-- path/to/migrations/001_initial_schema.down.sql
BEGIN;

-- Drop statements in reverse dependency order
DROP TABLE IF EXISTS users CASCADE;

COMMIT;
```
</output_format>

<examples>
```sql
-- Example: Explicit constraint and index pattern
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    status VARCHAR(32) NOT NULL CHECK (status IN ('pending', 'processing', 'completed', 'cancelled')),
    total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_user_id_status ON orders (user_id, status);
```
</examples>

<verification>
- [ ] Schema satisfies 3NF and enforces referential integrity through foreign keys and check constraints.
- [ ] Migration scripts execute inside a single transaction and can be run without syntax errors.
- [ ] Dependencies are ordered correctly so tables are created before they are referenced and dropped before parent tables.
- [ ] Indexes are placed on foreign key columns and frequently queried filters.
- [ ] Output answers the user's question directly without preamble.
- [ ] Format matches the shape requested in <output_format>.
- [ ] Length stays within the bounds stated in <requirements>.
- [ ] Code parses without syntax errors and is runnable as written.
</verification>