API · Data (advanced)

A small library/catalog (authors + books) that closes the advanced schema-DSL gaps — the features that are documented but shown in no other api template: the *.entity.ts / *.relations.ts split with eager .with() loading, full-text search, dbEnum, array / generated / encrypted columns, and declarative query result caching. Reach for it as a worked reference when you're modelling something richer than a single flat table. Template id: api-data-advanced.

#Scaffold

voltro create-project acme --api=api-data-advanced

#What ships

apps/acme/api/                              # dir named by the app, not the template
├── app.config.ts                           # type:api, store:'memory', governancePlugin({ fieldEncryption: true })
├── .env                                     # DEV-ONLY VOLTRO_FIELD_ENCRYPTION_KEY (see below)
├── package.json
├── tsconfig.json
├── README.md
├── database/
│   ├── actors.entity.ts                    # core audit-subject table
│   ├── tenants.entity.ts                   # core tenant boundary
│   ├── authors.entity.ts                   # name + .encrypted() bio
│   ├── books.entity.ts                     # dbEnum genre, array tags, generated slug, FTS index
│   ├── authors.relations.ts                # author → many books
│   ├── books.relations.ts                  # book → one author
│   └── index.ts                            # databaseHandle({...}) + relation registration
├── queries/
│   ├── books.search.query.ts(.server)      # FTS via .matching(...) + cache
│   └── authors.withBooks.query.ts(.server) # eager-load via .with({ books: true })
└── seeds/
    └── catalog.seed.ts                      # demo authors + books

store: 'memory' keeps boot zero-infra (no Docker, data resets on restart). The schema DSL is identical across every SQL backend — switch app.config.ts to store: 'postgres' to see the native DDL the migrator emits (CREATE TYPE … ENUM, text[], the STORED generated column, the tsvector + GIN FTS index). On memory the same features run through the in-process store: FTS degrades to a substring scan, arrays round-trip as JS arrays, encryption still applies. See the SQL dialects guide.

#The *.entity.ts / *.relations.ts split

Tables are declared one per file with the *.entity.ts extension. Relations are declared OUTSIDE the table descriptor, in a sibling *.relations.ts file — the framework registers them at boot and the query builder's .with() chain uses them to eager-load.

// database/authors.entity.ts
import { id, table, text } from '@voltro/database'
import { tenant } from '@voltro/plugin-multitenancy/mixin'

export const authors = table('authors', {
  id:   id({ prefix: 'author' }),
  name: text(),
  // Encrypted at rest. Requires governancePlugin({ fieldEncryption: true }).
  bio:  text().encrypted().nullable(),
})
  // tenant() pulls audit() transitively → tenantId + createdAt/updatedAt/createdBy/updatedBy
  .with(tenant())

// database/authors.relations.ts — declared OUTSIDE the table descriptor
import { relations } from '@voltro/database'
import { authors } from './authors.entity'
import { books } from './books.entity'

// An author has MANY books. `foreignKey` auto-derives because exactly one
// reference() column on `books` (`authorId`) points back at `authors`.
export const authorsRelations = relations(authors, ({ many }) => ({
  books: many(books),
}))

// database/books.relations.ts — the inverse, a book belongs to ONE author
import { relations } from '@voltro/database'
import { authors } from './authors.entity'
import { books } from './books.entity'

export const booksRelations = relations(books, ({ one }) => ({
  author: one(authors),
}))

The handle barrel imports the *.relations.ts files (side-effecting) so the eager-load walker can resolve them:

// database/index.ts
import { databaseHandle, type InferRow } from '@voltro/database'
import { actors } from './actors.entity'
import { tenants } from './tenants.entity'
import { authors } from './authors.entity'
import { books } from './books.entity'

// Register relations (side-effecting imports).
import './authors.relations'
import './books.relations'

export { actors, tenants, authors, books }
export type Author = InferRow<typeof authors>
export type Book = InferRow<typeof books>

export const database = databaseHandle({ actors, tenants, authors, books })

See Relations.

#Eager loading — .with({ books: true })

authors.withBooks returns each author with their books attached as an array, in ONE SQL roundtrip (the dialect's JSON-aggregation idiom — postgres jsonb_agg, sqlite json_group_array, mysql JSON_ARRAYAGG, mssql FOR JSON PATH). The per-branch orderBy/limit apply PER author.

// queries/authors.withBooks.query.server.ts — executor (server-only)
import type { AppContext } from '@voltro/runtime'
import { database } from '../database/index'

const execute = (_input: Record<string, never>, _ctx: AppContext) =>
  database.authors.with({
    books: { orderBy: [{ column: 'title', direction: 'asc' }], limit: 50 },
  })

export default execute

The descriptor's output carries the nested books array, and source: ['authors', 'books'] makes the reactive subscription re-run when EITHER table changes — a new book under an author pushes a fresh snapshot:

// queries/authors.withBooks.query.ts — descriptor (browser-safe)
import { defineQuery } from '@voltro/protocol'
import { Schema } from 'effect'

const Book = Schema.Struct({
  id: Schema.String, authorId: Schema.String, title: Schema.String,
  summary: Schema.String, genre: Schema.String,
  tags: Schema.Array(Schema.String), slug: Schema.String, tenantId: Schema.String,
})

export const authorsWithBooks = defineQuery({
  name:   'authors.withBooks',
  source: ['authors', 'books'],
  openAccess:
    'streams the seeded demo catalogue (authors + their books) for the request\'s tenant; the '
    + 'seed holds no personal data. `bio` is `.encrypted()` and still crosses the wire on '
    + 'purpose — encryption at rest is not an exposure marker; `.serverOnly()` is.',
  input:  Schema.Struct({}),
  output: Schema.Struct({
    id: Schema.String, name: Schema.String,
    // `bio` is the .encrypted() column — handlers + the wire see plaintext.
    bio: Schema.NullOr(Schema.String), tenantId: Schema.String,
    books: Schema.Array(Book),   // eager-loaded relation
  }),
})

See Eager loading.

#Full-text search — .fullTextIndex + .matching

books declares one FTS index over title + summary. That single declaration compiles to a different backend per dialect (postgres tsvector + GIN, mysql/mariadb FULLTEXT, sqlite FTS5, mssql ranked-LIKE fallback). The books table also carries the other specialized columns — dbEnum, array(text()), and a stored .generatedAs(...):

// database/books.entity.ts
import { array, dbEnum, id, reference, table, text } from '@voltro/database'
import { tenant } from '@voltro/plugin-multitenancy/mixin'
import { authors } from './authors.entity'

// Native ENUM type — declared module-level as a reusable handle. `as const`
// is required for the literal-union type. postgres → CREATE TYPE … ENUM
// (ADD VALUE is O(1)); mysql/mariadb → native ENUM(...); mssql/sqlite → CHECK.
export const bookGenre = dbEnum('book_genre', [
  'fiction', 'nonfiction', 'fantasy', 'sciFi', 'mystery', 'biography',
] as const)

export const books = table('books', {
  id:       id({ prefix: 'book' }),
  authorId: reference(() => authors),                  // FK → authors, auto-indexed
  title:    text(),
  summary:  text(),
  genre:    bookGenre.column().default('fiction'),     // native ENUM column
  tags:     array(text()).default([]),                 // text[] on postgres; JSON codec elsewhere
  // DB-computed STORED column derived from `title` on INSERT + UPDATE.
  // `stored: true` persists it on disk so it's indexable. Same-row columns
  // only. Raw SQL emitted verbatim — keep it portable (quoted identifier).
  slug:     text().generatedAs(`lower("title")`, { stored: true }),
})
  .fullTextIndex('bookSearch', ['title', 'summary'], {
    config:  'english',
    weights: { title: 'A', summary: 'B' },
  })
  .with(tenant())

The query executor compiles the FTS predicate via .matching('bookSearch', q) — resolving the index's covered columns + config from the declaration on the table. No tenant filter is added by hand; books carries tenant(), so the runtime AND-merges eq('tenantId', subject.tenantId):

// queries/books.search.query.server.ts — executor (server-only)
import type { AppContext } from '@voltro/runtime'
import { database } from '../database/index'

const execute = (input: { q: string }, _ctx: AppContext) =>
  database.books.matching('bookSearch', input.q).limit(50)

export default execute

See Full-text search, Enums, Arrays & intervals, and Generated columns.

#Field encryption — .encrypted() + the DEV-ONLY .env

authors.bio is flagged .encrypted(). The store middleware transparently encrypts it on write (AES-256-GCM) and decrypts on read — handlers always see plaintext while the column stores an opaque enc:v1:… string on disk on every dialect. An .encrypted() column is ciphertext in SQL, so you can't filter or sort by its plaintext — encrypt only what you read back whole (PII, tokens, notes). A book search never touches bio, so this is safe.

The column ONLY works because app.config.ts wires governancePlugin({ fieldEncryption: true }), which registers the cipher. Boot fails loud if an .encrypted() column exists but no cipher is registered.

// app.config.ts
import { defineEnv, envVar } from '@voltro/env'
import { governancePlugin } from '@voltro/plugin-governance'

export const env = defineEnv({
  LOG_LEVEL: envVar.enum(['debug', 'info', 'warn', 'error'], { access: 'public', default: 'info' }),
  // NO `default` on purpose — see the note below.
  VOLTRO_FIELD_ENCRYPTION_KEY: envVar.string({ access: 'secret' }),
})

export default {
  type:  'api' as const,
  name:  '{{capProjectName}}{{capAppName}}',
  store: 'memory' as const,
  env,
  plugins: [
    governancePlugin({ fieldEncryption: true }),   // reads secret VOLTRO_FIELD_ENCRYPTION_KEY
  ],
}

Where the key actually comes from is the load-bearing detail. governancePlugin({ fieldEncryption: true }) resolves the AES-256-GCM key through the Secrets-Resolver — i.e. the process environment — NOT through a defineEnv default. A defineEnv default would satisfy the boot validation gate while the cipher still failed to resolve a key. That's why the env declaration above deliberately has no default, and the template instead ships a .env carrying a DEV-ONLY placeholder so voltro dev boots out of the box:

// The key is OURS to invent, so declare it as minted. `voltro dev` writes a
// unique per-project value into a gitignored `.env.local` before the env gate
// runs — so the cipher resolves a key that exists nowhere else, and no key
// value is ever committed or shipped.
VOLTRO_FIELD_ENCRYPTION_KEY: envVar.secret({ generate: 'hex' }),

Minting is dev-only. serve / build / start have no such step: in production a missing key must remain a boot failure, and the value belongs in your deployment's secret store. Lose the key → lose the data (GCM fails closed, never silent corruption); rotating it makes every existing .encrypted() value unreadable.

Never write a key value into a shipped file — not even an obviously-fake one. A placeholder in a template is a signing key published to everyone who downloads it, and a 64-hex placeholder passes every length check and appears on no blocklist.

See Field encryption and the columns reference.

#Declarative query caching

books.search adds cache to its defineQuery. The framework caches the server snapshot and auto-invalidates it whenever a mutation writes any table the query depends on (here, books) — zero manual busting. scope is REQUIRED and is a security decision: this query is tenant-filtered (the tenant() mixin AND-merges the caller's tenantId), so it depends on the caller → scope: 'subject' (the cache key includes the subject id, no cross-tenant leak). NEVER global on a subject-filtered query.

// queries/books.search.query.ts — descriptor (browser-safe)
import { defineQuery } from '@voltro/protocol'
import { Schema } from 'effect'

export const searchBooks = defineQuery({
  name:   'books.search',
  source: 'books',
  openAccess:
    'full-text search over the seeded demo book catalogue, tenant-scoped; the seed holds no '
    + 'personal data. The result cache is `scope: \'subject\'`, so an open read still cannot '
    + 'serve one caller a snapshot built for another.',
  input:  Schema.Struct({ q: Schema.String }),
  output: Schema.Struct({
    id: Schema.String, authorId: Schema.String, title: Schema.String,
    summary: Schema.String, genre: Schema.String,
    tags: Schema.Array(Schema.String), slug: Schema.String, tenantId: Schema.String,
  }),
  cache: { ttl: '30s', swr: '5m', scope: 'subject' },
})

The authors.withBooks query deliberately has NO cache — it's a join-shaped result, so the template lets the live dispatcher keep it fresh rather than cache a snapshot. See Query result caching.

#Boot seed

catalog.seed.ts is a boot-lifecycle seed that idempotently upsertByUniques a demo tenant, two authors, and three books, so the catalog has data the moment voltro dev boots. The seed store is the RAW store (no authenticated subject), so tenant() auto-fill doesn't fire — it passes tenantId: 'acme' explicitly (matching the dev AuthMiddleware default, which resolves acme from the x-tenant header). It passes bio as plaintext (the middleware encrypts it), tags as a JS array, and OMITS slug (the .generatedAs(...) STORED column is filled by the DB engine). See Seeds.

#Try it

Subscribe from a web app via useSubscription('app', 'books.search', { q: 'magic' }) and useSubscription('app', 'authors.withBooks'), or invoke them over HTTP through POST /_voltro/inspect/invoke for a one-shot snapshot.

#When to use api-data-advanced vs. the variants

You need…	Pick
The smallest reference to extend	api-backend
A worked tour of the advanced schema DSL (relations + eager loading, FTS, enum/array/generated/encrypted columns, query cache)	api-data-advanced
Transactional email wired (React-Email)	api-backend-mail
File storage wired (public + private objects)	api-backend-storage
MariaDB binlog CDC + storage (K8s shape)	api-backend-mariadb

#Pairs well with

• Any web template — api-data-advanced exposes its queries over the standard rpc client.
• @voltro/plugin-governance — already wired here for field encryption; it also ships retention sweeps, GDPR export/erase, and a consent ledger.

#Anti-patterns

• Shipping the placeholder VOLTRO_FIELD_ENCRYPTION_KEY to production. The .env value is a public, DEV-ONLY constant. Generate a real key (openssl rand -hex 32), store it in your deployment's secret store, and never commit it. Lose the key and the ciphertext is gone for good.
• Expecting a defineEnv default to feed the cipher. It won't — the cipher reads the Secrets-Resolver (process env), not the typed-env default. A default would pass the boot gate while the cipher still fails to resolve, so the key must reach process.env (here via .env).
• Filtering or sorting by an .encrypted() column's plaintext in SQL. It's ciphertext on disk — those predicates can't run. Encrypt only fields you read back whole.
• Adding tenantId filters by hand in the query executors. authors and books carry tenant(), so the runtime scopes reads automatically. Doing both works but signals you don't trust the framework.
• Passing slug in a write. It's a .generatedAs(...) STORED column — the DB engine fills it from title. Passing a value fights the generator.