Substratum.cloud

Appearance

ADR 09: Database and ORM Selection (SeaORM) ​

Status: Accepted Date: 2026-04-10

Context ​

We need a relational database to store SaaS metadata, billing information, and cached Passport Receipts.

Per our architectural boundaries (see ADR 02: Ports and Adapters and the original Technical Specification), the Rust Gateway/API is the sole owner of the database. The SaaS UI (Mithril.js) is strictly an API consumer and must not connect to the database directly. This strict API contract ensures that the UI, the local Sidecars, and any third-party developers all use the exact same REST/RPC endpoints.

Because the database access layer resides entirely in the Rust core, we need a foolproof way to enforce multi-tenancy and data isolation using our AT Protocol identities (did:plc) without relying on proprietary serverless Postgres extensions.

Decision ​

  1. Database: We will use DigitalOcean Managed PostgreSQL.
  2. Data Access: We will use SeaORM in Rust. SeaORM is an async, dynamic ORM built on top of SQLx. It provides a fluent API for building queries and managing migrations, offering excellent developer velocity while maintaining Rust's performance. Versioned catalog DDL lives in crates/migration; gateway boot runs Migrator::up via bootstrap_database on DATABASE_ADMIN_URL.
  3. Security (RLS): We will enforce Native PostgreSQL Row-Level Security (RLS).

Instead of relying on application-level filtering (e.g., manually appending WHERE owner_did = ? to every query), the Rust API will use SeaORM's underlying SQLx connection to execute raw SQL for the session variable, and then use the ORM for the query:

  1. Verify the AT Proto JWT to extract the user's did:plc.
  2. Open a database transaction.
  3. Execute a raw SQL command to set the Postgres session variable (e.g., SET LOCAL app.current_user_did = $1).
  4. Execute the ORM query (e.g., PassportReceipt::find().all(&txn)), allowing the Postgres engine itself to enforce the RLS policies based on the session variable.

Consequences ​

  • Positive:
    • Strict API Contract: The UI is decoupled from the data layer, enforcing a clean separation of concerns.
    • Foolproof Security: Native RLS guarantees that even if a developer forgets a .filter() clause in a Rust query, the database engine will block cross-tenant data leaks.
    • Developer Velocity: SeaORM provides a familiar, fluent API and built-in migration management, significantly reducing the verbosity of writing raw SQL.
    • Infrastructure Consolidation: Keeps our infrastructure centralized on DigitalOcean (alongside our Droplets and Spaces).
  • Negative:
    • Macro Heavy: SeaORM relies heavily on Rust macros for entity generation, which can increase compile times.
    • Loss of Compile-Time SQL Verification: Unlike raw SQLx, SeaORM does not verify queries against the live database at compile time; it relies on the Rust structs matching the DB schema.
    • Transaction Overhead: Every authenticated request requires opening a transaction to securely set the LOCAL session variable before querying, which adds slight overhead compared to simple connection pooling.