Overview

Voltro's data layer is opinionated. One database (Postgres), one connection layer (@effect/sql), one schema DSL (built into the framework). No second ORM, no second query builder, no codegen step you have to remember to run.

This section covers everything about how the framework reads + writes data.

#The shape of it

┌───────────────────────────────────────────────────────────┐
│  Schema DSL  (apps/api/database/*.entity.ts)              │
│   table(...) + columns + mixins                           │
└────────────────┬──────────────────────────────────────────┘
                 │  voltro migrate
                 ▼
┌───────────────────────────────────────────────────────────┐
│  Postgres                                                 │
└────────────────┬──────────────────────────────────────────┘
                 │
                 ▼
┌───────────────────────────────────────────────────────────┐
│  Runtime data layer  (ctx.store inside every executor)    │
│   - select/insert/update/delete builders                  │
│   - dependency tracking for subscriptions                 │
│   - tenant-mixin AND-merging                              │
└───────────────────────────────────────────────────────────┘

You declare tables once. The migrator produces SQL; the runtime gives you a typed ctx.store; the reactive engine watches the same rows.

#A first table

// apps/api/database/notes.entity.ts
import {
  table, id, text, integer, timestamp,
} from '@voltro/database'
import { tenant } from '@voltro/plugin-multitenancy'

export const notes = table('notes', {
  id:        id(),
  title:     text(),
  body:      text(),
  views:     integer().default(0),
  createdAt: timestamp().default('now'),
  updatedAt: timestamp().default('now').onUpdate('now'),
})
  .with(tenant())   // adds tenantId + scoping — mixins chain via .with(), not spread

After voltro migrate:

• A notes table exists in Postgres with the columns above + tenant column from the mixin.
• A typed ctx.store.select('notes') etc. is available everywhere.
• Subscriptions reading notes get auto-scoped to the caller's tenant.

#What's in this section

#Schema basics

• Column types — every column function, their SQL type, modifiers
• Mixins — tenant(), audit(), softDelete(), writing your own
• Indexes — single-column, composite, partial, expression, GiST, HNSW
• Migrations — the migration story end-to-end, including production

#Specialized columns

• Enums (dbEnum) — postgres-native ENUM types
• Generated columns — DB-computed values
• Arrays + intervals — native postgres types + cross-dialect codec
• JSON columns — typed jsonb, indexing, paths
• Vector columns — pgvector, embedding, ANN search, HNSW
• PostGIS — geography/geometry for location-aware apps
• Full-text search — tsvector + GIN + .matching()

#Reading data

• Query builder — select, where, aggregates, raw SQL escape hatch
• Joins & relations — explicit joins, eager loading, the runtime's tracking
• Aggregations — count / sum / groupBy / having + window functions
• Sub-queries — inSubquery / exists predicates
• Set operations — union / intersect / except
• DISTINCT + DISTINCT ON — dedup + one-per-group
• Self-joins — .as(alias) + .innerJoin(table, alias, on)
• Recursive CTEs — WITH RECURSIVE for tree walks
• SQL views — view(name, columns, select) read-only named SELECTs

#Writing data

• Transactions — how ctx.store works inside mutations + workflows
• Bulk operations — updateMany / upsert / insertIgnore

#Conventions enforced by the framework

	Why
One table per database/*.entity.ts file + a database/index.ts barrel	Discovery walks *.entity.ts / *.schema.ts / schema.ts; the entity-per-file model is the primary convention.
All columns NOT NULL by default; .nullable() to opt in	Three-valued logic is the source of half of all SQL bugs. The default is the safer one.
id columns default to TypeID (<prefix>_<ulid>), not serial integers	Sortable by time, URL-safe, branded to the table type, no leaking sequential row counts.
Timestamps are timestamptz	Always UTC, never naive. The wire format is ISO 8601.
Mixins are chained via .with(mixin())	Keeps the table declaration readable + composable; the dep resolver dedups shared mixins.