PostgreSQL & Database Migrations

Relevant source files

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

• BY-STACK/postgres.md
• claude-md-snippets/postgres.md
• BY-STACK/async-orm.md
• bugbot-rules/migration-model-drift.md
• claude-md-snippets/universal.md
• MIGRATION-NOTES.md

Introduction

PostgreSQL serves as the primary relational database engine for this project, utilizing SQLAlchemy for ORM capabilities and Alembic for schema migrations. This system is designed to handle complex data relationships, high-precision identifiers, and asynchronous operations while maintaining strict data integrity through constraints and specific query patterns.

The scope of this module includes the management of database schemas, the implementation of atomic updates via Compare-and-Swap (CAS), and the prevention of "migration drift"—a state where the actual database schema diverges from the ORM model definitions. Proper utilization of this system requires adherence to specific technical patterns to avoid common pitfalls such as null-semantic errors in CAS, integer overflows for external IDs, and pagination inconsistencies.

Data Type Management & External IDs

The project identifies several critical areas where data type selection directly impacts system stability, particularly when interfacing with external services.

External ID Precision

Standard Integer types are insufficient for many external identifiers. IDs from platforms like Telegram, Discord, Stripe, and GitHub often exceed the $2^{31}$ limit of a standard 32-bit integer.

• Requirement: Use BigInteger (BIGINT) for all external ID columns.
  Sources: BY-STACK/postgres.md:46-55, claude-md-snippets/postgres.md:10-11

Enum and String Lengths

A common failure point is the discrepancy between StrEnum values and VARCHAR(N) column limits.

• Pattern: Columns defined as String(N) must have an $N$ value greater than or equal to the longest possible Enum string.
• Validation: CI tests should verify that N >= max(len(v) for v in EnumClass).
  Sources: BY-STACK/postgres.md:28-44, claude-md-snippets/postgres.md:7-8, bugbot-rules/migration-model-drift.md:26-27

Schema Migrations & Model Drift

The project uses Alembic for versioned schema changes. A primary architectural goal is ensuring that migrations and ORM models remain synchronized to allow for reliable fresh deployments.

Migration Best Practices

• Mirroring Constraints: Every Index, CheckConstraint, or UniqueConstraint added in a migration file must be reflected in the model's __table_args__.
• Revision IDs: Alembic revision IDs must be kept under 30 characters because the default alembic_version table often uses VARCHAR(32).
• Column Removal: DROP COLUMN operations must be performed in a separate migration only after all application code has stopped referencing the field.
  Sources: BY-STACK/postgres.md:78-100, claude-md-snippets/postgres.md:24-27, bugbot-rules/migration-model-drift.md:1-24

Database Specific Divergence

The architecture accounts for differences between PostgreSQL and other engines like MySQL or SQLite.

• MySQL Limitations: Unlike PostgreSQL, MySQL does not support ADD COLUMN IF NOT EXISTS.
• SQLite Limitations: SQLite often lacks strict length enforcement for VARCHAR, meaning bugs related to string length might only appear once deployed to a PostgreSQL environment.
  Sources: BY-STACK/postgres.md:37, BY-STACK/postgres.md:92, claude-md-snippets/universal.md:31-33

Advanced Query Patterns

Atomic Updates (CAS) and NULL Semantics

Standard equality checks (=) in SQL return NULL when comparing against a NULL value, which is treated as FALSE in WHERE clauses. This breaks Compare-and-Swap (CAS) logic for nullable columns.

# Correct implementation for nullable CAS
if expected_old is None:
    where_clause = Model.col.is_(None)
else:
    where_clause = Model.col == expected_old

result = await session.execute(update(Model).where(where_clause)...)

Sources: BY-STACK/postgres.md:11-26, BY-STACK/async-orm.md:97-111, claude-md-snippets/postgres.md:4-5

Reliable Pagination

To prevent skipped or duplicated records during pagination, all ORDER BY clauses combined with LIMIT and OFFSET must include a deterministic "tiebreaker" column.

Order Requirement	Implementation Pattern
Semantic Sorting	order_by(Model.created_at.desc())
Tiebreaker	order_by(Model.created_at.desc(), Model.id.desc())

Sources: BY-STACK/postgres.md:57-76, claude-md-snippets/postgres.md:13-16

Architecture and Flow

The following diagram illustrates the relationship between the ORM Model, Alembic Migrations, and the actual Database Schema, highlighting the checkpoints required to prevent drift.

This diagram shows the synchronization requirement between models and migrations, as well as the branching logic required for correct NULL handling in PostgreSQL.
Sources: BY-STACK/postgres.md:11-100, bugbot-rules/migration-model-drift.md:1-24

Summary of Postgres Edges

Feature	Hazard	Mitigation
LIKE Queries	Wildcard Injection	Use .startswith(value, autoescape=True)
IN / ANY	Type Mismatch	Explicitly cast arrays (e.g., String to UUID)
Column Ops	Python vs SQL logic	Use func.trim() instead of Python .strip()
Index Direction	postgresql_ops abuse	Use desc("col") instead of operator classes
IDs	32-bit Integer Overflow	Always use BigInteger for external IDs

Sources: BY-STACK/postgres.md:102-148, claude-md-snippets/postgres.md:18-30

Conclusion

PostgreSQL and Database Migrations within this project are governed by a "Strict Sync" philosophy. By enforcing alignment between migrations and models, and utilizing specific SQL patterns for NULL handling and pagination, the system ensures that the database remains a reliable source of truth across various deployment environments.
Sources: MIGRATION-NOTES.md:1-50, BY-STACK/postgres.md:1-10