---
status: draft
last_updated: 2026-05-23
---

# @alkdev/dbtype Architecture

Schema-first multi-dialect database type system. Define your database schema once as a UJSX element tree, validate it with TypeBox, and render it to any Drizzle dialect (SQLite, PostgreSQL, MySQL) — all from a single source of truth.

## Why This Exists

Every project that uses Drizzle ORM across multiple backends defines schemas repeatedly: once per dialect (SQLite for local dev, PostgreSQL for production), once again for input validation (TypeBox, Zod, etc.), and once more for API contracts (GraphQL types, OpenAPI schemas). The existing `drizzle-typebox` only goes one direction (Drizzle → TypeBox) and requires defining schemas in Drizzle's dialect-specific API first.

The gap: there is no schema-first, dialect-agnostic way to define a database schema that simultaneously produces validation schemas, Drizzle table definitions, and (via the operations layer) CRUD operation specs — all from one source of truth.

## Core Principle

**The element tree is the schema. The module is the bundle. The host is the dialect.**

- The UJSX element tree (`<table>`, `<column>`) is the authoring surface — composable, reusable, and TypeBox-validatable
- The `Type.Module` is the schema bundle — all tables, relations, and derived schemas in one namespace with `Type.Ref` resolving cross-table references
- The `HostConfig` is the dialect adapter — render the same tree to `sqliteTable`, `pgTable`, `mysqlTable`, or any future target

## Relationship to Sibling Packages

| Package | Relationship |
|---------|-------------|
| `@alkdev/typebox` | Type system foundation. `Type.Module`, `Type.Ref`, `Value.Check/Diff/Edit`, `FormatRegistry` |
| `@alkdev/ujsx` | Element authoring. `h()`, `createComponent`, `HostConfig`, `createRoot`, reconciler |
| `@alkdev/operations` | CRUD generation. `OperationSpec`, `OperationRegistry`, `from-dbtype` adapter (future) |
| `@alkdev/pubsub` | Event transport. Used by operations call protocol |
| `drizzle-orm` | Peer dependency. Dialect-specific column builders consumed by hosts |

## Current State

**Phase 0: Exploration** — Architecture probing complete. Probe scripts in `scripts/probe-e2e.ts` validate the core architecture. No implementation yet.

### What Exists
- Fork of `drizzle-typebox` with `@alkdev/typebox` support (current `src/`)
- Research docs in `docs/research/` (architecture, type-map, column diffs, typedef kind pattern)
- E2E probe script validating: UJSX → Type.Module → Drizzle rendering pipeline

### What Doesn't Exist Yet
- Core `dbtype` package (the implementation)
- Host configs for SQLite, PostgreSQL, MySQL
- Schema extraction functions (`createSelectSchema`, `createInsertSchema`, `createUpdateSchema` from element trees)
- Repo/CRUD generation adapter (`from-dbtype` for `@alkdev/operations`)
- `@alkdev/ujsx` as a declared dependency (currently dev-only)

## Architecture Documents

| Document | Content |
|----------|---------|
| [schema.md](schema.md) | Table/relation schema structure, schema derivation (insert/update/filter) |
| [module.md](module.md) | Type.Module mechanics: construction, validation, serialization, migration diffing |
| [elements.md](elements.md) | UJSX element definitions, column types, type definitions, defaults, function components |
| [hosts.md](hosts.md) | HostConfig implementations, column type mapping tables, rendering pipeline |
| [repo-adapter.md](repo-adapter.md) | `from-dbtype` adapter for `@alkdev/operations`, filter/schema generation, access control |
| [build-distribution.md](build-distribution.md) | Package structure, sub-path exports, dependencies, tree-shaking |
| [open-questions.md](open-questions.md) | Cross-cutting unresolved questions |

### Design Decisions

| ADR | Decision |
|-----|----------|
| [001](decisions/001-ujsx-as-ir.md) | UJSX element tree as the IR, not a separate DbType builder API |
| [002](decisions/002-type-module-as-bundle.md) | Type.Module as the schema bundle, not a custom registry |
| [003](decisions/003-hostconfig-for-dialects.md) | HostConfig for dialect rendering, not a transform registry |
| [004](decisions/004-format-annotation-only.md) | Column formats are annotations, not validators — register explicitly |
| [005](decisions/005-repo-as-adapter.md) | CRUD generation as an operations adapter, not a core feature |
| [006](decisions/006-references-vs-fk.md) | Column `references` as FK shorthand, explicit `<fk>` for complex FKs |
| [007](decisions/007-inner-escape-hatch.md) | Flat props with `inner` escape hatch for column validation |
| [008](decisions/008-pg-enum-predeclaration.md) | PG enum pre-declaration — return enums and tables from render context |

### Open Questions

All unresolved questions tracked in [open-questions.md](open-questions.md).

## Source Structure (Planned)

```
src/
  core/
    elements.ts          # h() wrappers, createComponent for table/column/index/fk
    schema.ts            # extractTable, createSelectSchema, createInsertSchema, createUpdateSchema
    module.ts            # buildModule, module construction helpers
    column-types.ts      # DbColumnType union, colToTypeBox mapping
    defaults.ts          # Symbolic default resolution ('now', 'uuid', 'autoincrement')
    guards.ts            # Type guards for element types
  hosts/
    sqlite.ts            # HostConfig for drizzle-orm/sqlite-core
    pg.ts                # HostConfig for drizzle-orm/pg-core
    mysql.ts             # HostConfig for drizzle-orm/mysql-core
  repo/
    from-dbtype.ts       # FromDbType adapter for @alkdev/operations
    filters.ts           # Filter schema generation per column type
    handlers.ts          # CRUD handler generation (findMany, insertOne, etc.)
   index.ts
```

## End-to-End Pipeline

The dbtype pipeline has two walks over the same element tree:

1. **Schema extraction** (`extractTable`) — walks the tree to produce TypeBox schemas and column metadata
2. **Host rendering** (`createRoot(host).render()`) — walks the tree to produce Drizzle table definitions

These are separate walks because they serve different purposes: schema extraction feeds the Type.Module (for validation and serialization), while host rendering feeds Drizzle (for database operations). They consume the same element tree but produce different outputs.

### Complete Flow

```typescript
import { Type } from '@alkdev/typebox'
import { h, createComponent, createRoot } from '@alkdev/ujsx'
import { Value } from '@alkdev/typebox/value'

// 1. Define elements
const UsersEl = h('table', { name: 'users' },
  h('column', { name: 'id', type: 'uuid', primaryKey: true, default: 'uuid' }),
  h('column', { name: 'name', type: 'string', notNull: true }),
)

// 2. Schema extraction — produces TypeBox schemas and column metadata
const { name, schema, columns, indexes, foreignKeys } = extractTable(UsersEl)

// 3. Module construction — assemble into Type.Module
const defs = { Users: schema }
const M = Type.Module(defs)
const Users = M.Import('Users')

// 4. Validation — check data against module schema
Value.Check(Users, { id: '...', name: 'alice' })  // true

// 5. Host rendering — produce Drizzle tables
const root = createRoot(sqliteHost, {})
root.render(UsersEl)
const drizzleUsers = root.ctx.tables.users  // sqliteTable result

// 6. Serialization — JSON Schema with $defs (for migration diffing)
const serialized = JSON.parse(JSON.stringify(Users))

// 7. Schema derivation — insert, update, filter schemas (for API validation)
defs.InsertUsers = Type.Object({ name: Type.String() })  // omit auto-generated columns
defs.UpdateUsers = Type.Partial(Type.Ref('Users'))        // all optional
```

### Key Relationships

| Step | Input | Output | Consumer |
|------|-------|--------|----------|
| Extract | UJSX elements | `TableMeta` (schema + metadata) | Module construction, Host rendering |
| Module | `Record<string, TSchema>` | `Type.Module` | Validation, Serialization |
| Import | Module key | `TImport` (resolved schema) | Value.Check, Value.Diff |
| Render | UJSX elements + HostConfig | Drizzle tables (in ctx) | Database queries |
| Derive | Module entries | Insert/Update/Partial schemas | API validation |

## Key Design Decisions

### 1. UJSX Element Tree as the IR, Not a Separate Builder API

The architecture docs in `docs/research/` proposed a `DbTypeBuilder` class with methods like `DbType.String()`, `DbType.Table()`. The probe results show that UJSX elements (`<table>`, `<column>`) with props serve the same purpose — they carry all the metadata (type, notNull, primaryKey, default, references) and compose via function components (`IdCol`, `AuditCols`).

A separate builder API would be redundant: both the element tree and the builder produce the same TypeBox + metadata. The element tree is strictly more capable (function components for composition, reconciler for migration diffing in the future) and already exists as `@alkdev/ujsx`.

**Alternative considered**: `DbTypeBuilder` (the research docs pattern). Rejected because it duplicates what UJSX already provides and cannot compose as naturally.

### 2. Type.Module as the Schema Bundle

All table schemas and their relations live in a single `Type.Module` call. `Type.Ref` resolves cross-table references including mutual/circular ones. This eliminates the circular-import problem that the storage_sqlite pattern solves with a separate `relations.ts` file.

The module is also serializable as JSON Schema with `$defs`, enabling `Value.Diff` for schema migration and `FromSchema` for round-tripping.

**Alternative considered**: A custom registry with separate schema objects. Rejected because `Type.Module` already does everything needed (ref resolution, validation, serialization) and doesn't require a new abstraction.

### 3. HostConfig for Dialect Rendering, Not a Transform Registry

The research docs proposed a `TransformRegistry` with priority-sorted rules. UJSX's `HostConfig` serves the same purpose — each dialect is a host, `createInstance` maps element types to Drizzle column builders, `appendChild` assembles columns into tables.

This leverages existing infrastructure (`createRoot`, `createComponent`, reconciler) rather than building a parallel dispatch system. It also positions the project for future migration support via `prepareUpdate`/`commitUpdate`.

**Alternative considered**: TransformRegistry from `docs/research/architecture.md`. Rejected because HostConfig is the same pattern with more capabilities (reconciler, function components, context) and already exists in `@alkdev/ujsx`.

### 4. Column Formats as Annotations

`Type.String({ format: 'uuid' })` and `Type.String({ format: 'email' })` are annotations by default in TypeBox. `Value.Check` does not enforce format unless validators are explicitly registered via `FormatRegistry.Set`. This is correct JSON Schema behavior.

For dbtype, formats serve as metadata that hosts can use (e.g., the PG host maps `format: 'uuid'` to `uuid()` column type, the SQLite host maps it to `text()` with `$defaultFn`). Validation is opt-in via format registration.

### 5. CRUD Generation as an Operations Adapter

The repo pattern (auto-generated CRUD for each table) is not a core feature of dbtype. It's an adapter for `@alkdev/operations` that consumes the same element tree and module bundle. This keeps dbtype focused on schema definition and rendering, while the operations integration is a separate concern.

## Error Handling Strategy

dbtype uses a layered validation approach:

1. **TypeScript compile-time enforcement**: `DbColumnType`, column props, and element types are enforced by TypeScript types. Invalid `type` values, missing required props, and incorrect prop types are caught at compile time.

2. **Runtime validation at extraction**: `extractTable()` validates the element tree — missing `name` or `type` on columns, duplicate column names, invalid `DbColumnType` values. These throw descriptive errors.

3. **TypeBox validation at module compile**: `Type.Module(defs)` validates the schema map. Invalid `Type.Ref` targets, duplicate keys, and malformed schemas throw TypeBox errors.

4. **Host rendering validation**: `createInstance()` in the host validates dialect-specific constraints — unknown column types fall back to `text()`, invalid symbolic defaults throw errors. PG enum names must be unique within a module.

The general principle: **catch errors as early as possible**. Type errors at compile time, structural errors at extraction time, schema errors at module compile time, dialect errors at render time.

## Glossary

| Term | Definition |
|------|------------|
| **UJSX** | Universal JSX — `@alkdev/ujsx`'s element system. Uses `h()` and `createComponent` to create element trees that render to different hosts via `HostConfig`. Not an acronym, a project name. |
| **Element tree** | A tree of UJSX elements (`<table>`, `<column>`, `<index>`, `<fk>`) representing a database schema. The tree is the IR (intermediate representation). |
| **HostConfig** | UJSX's host configuration interface. Defines how elements map to output objects (`createInstance`, `appendChild`, `finalizeInstance`). Each dialect (SQLite, PG, MySQL) is a host. |
| **Type.Module** | `@alkdev/typebox`'s module system. Holds all schemas in a flat namespace with `Type.Ref` for cross-references. The module is dbtype's schema bundle. |
| **Type.Ref** | A TypeBox reference type that resolves within a `Type.Module`. Enables forward and circular references without import ordering issues. |
| **TImport** | The type returned by `M.Import(key)`. Embeds the full `$defs` namespace and resolves `$ref` pointers for validation. |
| **$defs** | JSON Schema keyword used by serialized Type.Modules. Contains all referenced schemas. `$ref` pointers reference entries in `$defs`. |
| **extractTable()** | Core function that walks an element tree, resolves function components, and produces `TableMeta` (TypeBox schema + column metadata + indexes + FKs). |
| **Function component** | A UJSX component created with `createComponent` that returns element(s). Transparent to the host — only resolved intrinsic elements (`column`, `table`) reach `createInstance`. |
| **Intrinsic element** | A built-in element type (`table`, `column`, `index`, `fk`). Not a function component — directly dispatched to the host's `createInstance`. |
| **Reconciler** | UJSX's diffing engine. Can compare old and new element trees to produce update instructions. Not used in phase 1, but positions dbtype for future schema migration support. |
| **Host rendering** | The process of walking an element tree through a `HostConfig` to produce output (Drizzle tables). Done via `createRoot(host).render(element)`. |
| **Schema derivation** | Producing insert, update, and filter schemas from the module. E.g., `Type.Partial(Type.Ref('Users'))` for update schemas. |

## Document Lifecycle

| From | To | Condition |
|------|----|-----------|
| `draft` | `reviewed` | All open questions resolved |
| `reviewed` | `stable` | Implementation complete and verified by tests |
| `stable` | `deprecated` | Superseded by new architecture |

## References

- Research: `docs/research/architecture.md`, `docs/research/typemap-architecture.md`, `docs/research/dizzle-column-diffs.md`, `docs/research/typedef-kind-pattern.md`
- Probe results: `scripts/probe-e2e.ts`
- Sibling projects: `@alkdev/typebox`, `@alkdev/ujsx`, `@alkdev/operations`, `@alkdev/pubsub`
- Reference implementation: `@alkdev/drizzle-typebox` (current `src/`), `drizzle-graphql` (workspace)