Views / Materialized Views / CTEs

[mwillbanks]

Overview

Modern data-platforms often need more than just standard tables. Views (standard, materialized, and recursive/CTE-based) let you define derived datasets, impose indexing, optimise read-patterns, and encapsulate complex logic.
In the context of ZModel (the schema language for ZenStack), native support for these constructs means tighter integration: migrations, ORM layer, relations, and access policies all aware of these derived entities.

Why it’s needed

• Performance & read optimization: Materialized views allow you to pre-compute data and index it for fast retrieval (rather than compute on every query).
• Logical modeling & abstraction: Views (and CTEs) let you encapsulate complex queries behind a model. That means your ORM/client sees an entity rather than a raw SQL blob.
• Consistency & migration awareness: If you manually create views outside the schema, they may fall out of sync with your model definitions, migrations, or tooling. Native support ensures the schema, ORM, and migrations all know about them.
• Access control and relations: If a view is treated like a model (parts are already in some cases), you can apply policies (read/deny etc), join it to other models, and enforce auth rules directly.
• Cross-dialect considerations: While not all database engines treat views/materialized/CTEs identically (or support materialized views at all), being explicit in the schema helps identify when features are supported or need fallback logic.

Proposed Solution

Here’s how we could structure it in ZModel:

// Example: Materialized view
view MonthlySales {
  id      Int      @id
  month   DateTime
  total   Decimal

  @@index([month])
  @@view(type:"materialized", refreshOnMigrate:true)
  @@query: """
    SELECT
      date_trunc('month', created_at)::date AS month,
      SUM(amount)::decimal AS total,
      ROW_NUMBER() OVER (ORDER BY date_trunc('month', created_at)) AS id
    FROM orders
    GROUP BY 1
  """
  @@allow('read', auth().role == 'ANALYST')
  @@deny('all', auth() == null)
}

// Example: Standard view
view ActiveUsers {
  id        Int     @id
  userId    Int
  lastLogin DateTime

  @@query: """
    SELECT id, user_id AS userId, last_login AS lastLogin
    FROM users
    WHERE is_active = true
  """

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

// Example: Recursive / CTE-based view
view OrgHierarchy {
  id       Int     @id
  parentId Int?
  depth    Int

  @@view(type:"cte", recursive:true)
  @@query: """
    WITH RECURSIVE tree AS (
      SELECT id, parent_id, 1 AS depth
        FROM org_units
        WHERE parent_id IS NULL
      UNION ALL
      SELECT u.id, u.parent_id, t.depth + 1
        FROM org_units u
        JOIN tree t ON t.id = u.parent_id
    )
    SELECT id, parent_id AS parentId, depth
    FROM tree
  """

  @@allow('read', auth().departmentId == id)
}

Key schema-elements

• @@view(type: "..."): declares the view type, when using a view defaults to "standard". Possible types: "standard", "materialized", "cte".
• refreshOnMigrate: true: optional flag for materialized views to trigger a REFRESH MATERIALIZED VIEW (or equivalent) when migrating.
• View must still declare @id (or @@id for composite keys) so that ORM semantics, relations and indexing work correctly.
• @@index([...]): allow defining indexes on materialized views for performance.
• @@query: """ … """: the SQL logic (or in future a higher-level ZModel expression) defining the view.
  • ORM Query API support would be a great way to handle it
  • Kysley Query Builder support could likewise be a nice way of handling
  • With using any of these could potentially link to a file for the definition (i.e. .sql for straight up SQL, .ts for ORM Query API or Kysley Query Builder.
• Access/policy attributes (@@allow, @@deny) apply just like for standard models, so policies work consistently.

Migration & ORM Behavior

• On migration, engine should recognise view models:
  • For standard views: CREATE VIEW … AS …
  • For materialized views: CREATE MATERIALIZED VIEW … AS …, followed by CREATE INDEX … as defined.
  • If refreshOnMigrate is true: after creation (or on subsequent migrations) issue REFRESH MATERIALIZED VIEW ….
  • For CTE/recursive views: treated similar to standard views; indexing may not always apply depending on engine.
• ORM (query client) should treat view models like read-only tables by default (unless DB supports updatable views). Relations, filtering, and policy enforcement should work.
• Access policies: Because view is a first-class model, policy rules using auth() etc apply.
• Cross-dialect support: Since not every DB supports materialized views (or recursive CTEs in the same way), tooling may need to provide warnings, alternate fallback, or disable certain features depending on provider.

Challenges & Considerations

• Database support variability: Some providers (e.g., SQLite) may not support materialized views; recursion may differ. Implementation must detect or degrade.
• Write operations: Generally views are read-only; if an application tries create/update on a view model, either error or route to underlying tables must be clearly documented. Potentially add this to the @@view specification to state if it is writable or not.
• Refresh logic for materialized views: When to refresh? On every migration? On a schedule? The refreshOnMigrate flag covers part but many use-cases need runtime refresh (e.g., incremental).
• Indexing & constraints: Indexing a materialized view is supported in some DBs but may require specific syntax; ZModel tooling must generate correct DDL per provider.
• Relations & joins: If a view references or is referenced by other tables, keys and indexing must be properly defined so the ORM can join correctly. The requirement for @id (or composite @@id) is critical.
• Policy enforcement complexity: Since the underlying dataset is derived, some subtle cases may arise: e.g., view returns aggregated data, policies must consider that; view may join tables with differing permissions.
• Migration drift: If the underlying source tables change (e.g., column rename), the view definition in query: may break; tooling may need to detect and flag invalid views.
• Tooling UX: How to author the @@query: field nicely in schema; whether to support higher-level DSL rather than raw SQL; how to show error diagnostics.
• Versioning & rollback: When a view changes (definition changes), migration engine must drop & recreate or alter; for materialized views, data might need to be refreshed or swapped.