​

ADR 0003: SQL Views as Projections of Models

​

Status

​

Date

​

Context

1. The macro split is strict (ADR 0001 0.3.0 update) — server emit must reference sqlx only, embedded emit must reference rusqlite only, no cross-backend code.
2. SQL dialect portability is a lie. Postgres and SQLite agree on CREATE VIEW … AS SELECT syntax but diverge on aggregate casts, window function support, JSON functions, materialized views, and concurrent refresh. A single SQL string cannot drive both backends in general.
3. Banking-grade auditability (banking readiness) — view definitions must be reviewable as SQL, materialization behavior must not silently degrade between environments.

​

Decision

​

Schema surface

view ActiveCustomer from Customer, Order {
  id           Int       @id  @from(Customer.id)
  email        String         @from(Customer.email)
  orderCount   Int
  lastOrderAt  DateTime?

  @@server_sql("""
    SELECT c.id, c.email,
           COUNT(o.id)::int AS order_count,
           MAX(o.created_at) AS last_order_at
    FROM   customer c
    LEFT JOIN "order" o ON o.customer_id = c.id
    WHERE  c.deleted_at IS NULL
    GROUP  BY c.id, c.email
  """)
  @@embedded_sql("""
    SELECT c.id, c.email,
           COUNT(o.id)  AS order_count,
           MAX(o.created_at) AS last_order_at
    FROM   customer c
    LEFT JOIN "order" o ON o.customer_id = c.id
    WHERE  c.deleted_at IS NULL
    GROUP  BY c.id, c.email
  """)

  @@allow("read", auth() != null)
}

• view <Name> from <Model>, <Model>, … declares the source-model dependency list. It is not parsed from the SQL — it is declared explicitly so the parser can validate model names, build a migration ordering graph, and so LSP cross-references work without an SQL parser.
• Every field must either carry @from(Model.field) (to bind the column to its typed source) or be computed, in which case the user declares the Rust type and the macro trusts the SQL.
• @id is required on exactly one field. Views without a natural unique key opt out with @@no_unique, which drops find_unique from the generated delegate.
• @@server_sql("…") and @@embedded_sql("…") are the per-backend SQL bodies. At least one must be present. If only one is declared, the view is backend-specific — building the other target emits a clear error pointing at the missing attribute span.
• @@sql("…") is a shorthand that applies to both backends. The macro emits a cargo warning that single-string portability is the developer’s responsibility.
• @@allow is supported, but only with action "read". Any other action is a parse error.

​

Delegate split

pub struct ViewDelegate<'a, V: 'static, PK: 'static> { /* … */ }

impl<'a, V, PK> ViewDelegate<'a, V, PK> {
    pub fn find_many(&self) -> FindMany<'a, V, PK>;
    pub fn find_unique(&self, id: PK) -> FindUnique<'a, V, PK>;
}

pub trait ReadSource {
    const NAME: &'static str;             // SQL identifier (table or view)
    const COLUMNS: &'static [&'static str];
    type Row: DeserializeOwned;
}

​

Materialized views

error: `@@materialized` is not supported on the embedded backend (SQLite has no
       materialized views). Either gate this view with a feature flag, or split
       it into a server-only schema.
       See ADR 0003 (/internals/views-adr) for the rationale.
       --> schema.cstack:42:3
        |
     42 |   @@materialized
        |   ^^^^^^^^^^^^^^

runtime.views().account_balance().refresh().await?;

​

Migration emission

• CREATE VIEW <name> AS <server_sql> for sqlx builds
• CREATE VIEW <name> AS <embedded_sql> for rusqlite builds
• For @@materialized: CREATE MATERIALIZED VIEW <name> AS <server_sql> and CREATE UNIQUE INDEX <name>_pkey ON <name> (<id_column>)

​

Consequences

​

Positive

• Read-shape definitions live next to model definitions, governed by the same review process.
• @@allow policies apply uniformly to views — no parallel authorization story.
• Type safety end-to-end: declared columns match Rust struct fields, source-field references catch typos at parse time.
• Macro split stays disjoint — server emit never references rusqlite, embedded emit never references sqlx.
• Materialized views remain explicit and opt-in; their consistency contract is honest about the dialect divide.

​

Negative

• Two SQL bodies for cross-backend views is more typing than a single string. The shorthand @@sql("…") exists for cases where the developer is genuinely confident, but it ships with a warning.
• The view’s SQL body is not statically validated against the source models’ columns. A typo in the SQL is a runtime error at view-creation time (CI verification — see ADR 0004 — catches this before production).
• @@materialized schemas cannot be shared between embedded and server contexts unmodified. This is intentional, but it does fragment some otherwise-portable schemas.

​

Deferred

• Time-based refresh (@@materialized(refresh = "5m")) — defer until CrateStack has a scheduler primitive. Spawning tokio::task from the macro is the wrong layer.
• Event-driven refresh — the existing ModelEvent stream could drive it, but “refresh on every write” is wrong (thrash) and “refresh on burst end” needs debouncing config the schema can’t easily express. Defer until there’s a real use case.
• Materialized views on embedded via table+trigger emulation — out of scope. The semantics are different enough that a unified surface would mislead developers.

​

Read Next

1. Views reference — full attribute and syntax reference
2. Materialized views guide — refresh trigger patterns
3. ADR 0004: Schema diff and migration generation — how view changes flow through migrations