Numeric — dialect-native auto-increment

Numeric IDs are the framework's internal-only ID scheme — 1, 2, 3, … generated by the database's native auto-increment idiom. The framework uses them for its own bookkeeping tables (CDC log, audit log, migration ledger); application tables should default to TypeID unless they fit the constraints below.

import { id, table, text, timestamp, json } from '@voltro/database'

export const cdcLog = table('_voltro_cdc_log', {
  id:        id({ scheme: 'numeric' }),
  tableName: text(),
  op:        text(),
  oldRow:    json().nullable(),
  newRow:    json().nullable(),
  writtenAt: timestamp().default('now'),
})

#When to pick numeric

Three constraints; ALL three must apply.

#1. The ID is internal — never URL-exposed

Sequential numeric IDs make enumeration attacks trivial. Knowing /api/users/4221 exists tells an attacker /api/users/4222 exists too. The framework doesn't add an authorization layer to obscure this — if your auth model accidentally lets a request through, the numeric ID makes the attack surface linear in the row count.

Internal tables — CDC log, audit log, migration ledger, schedule run records — are never exposed via URL or API. They're tools for the framework + operators, not user-visible objects.

#2. The table is append-mostly + high-volume

Numeric IDs are compact: 8 bytes per column. TypeID is ~30 bytes per column. On a foreign-key column referenced from 100M child rows, that's 2.2 GB of disk + index space savings.

For tables in the tens of millions of rows where FK joins are hot, the compactness matters. For typical app tables (10k–100k rows), the savings are noise; pay the TypeID tax for the URL-safe, brandable upside.

#3. You won't merge data across databases

Numeric IDs collide across separately-deployed databases. Two staging instances both have users.id = 1. Trying to merge their data later requires re-mapping every FK. UUID-family schemes (TypeID, ULID, Snowflake) don't collide.

Internal tables are usually scoped to one process / one deploy / one instance, so this doesn't apply. Application tables often outgrow their original deploy boundary; pick a non-numeric scheme to keep the option open.

#Per-dialect emission

The framework's DDL emitter dispatches numeric ID columns to the dialect-native idiom:

Dialect	Column shape
postgres	"id" BIGSERIAL PRIMARY KEY
mysql	`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
mariadb	`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
mssql	[id] BIGINT IDENTITY(1,1) PRIMARY KEY
sqlite	"id" INTEGER PRIMARY KEY AUTOINCREMENT

The framework chose BIGINT (or INTEGER on sqlite, which auto-promotes) so a 64-bit address space avoids the 2^31 INT overflow that's bitten enough deploys to be a meme. SQLite's INTEGER promotes to 64-bit when used as PRIMARY KEY AUTOINCREMENT anyway.

#Insert behaviour

The framework's mutation middleware OMITS the id field from the insert when the column's scheme is numeric:

// You write:
await ctx.store.insert('_voltro_cdc_log', {
  tableName: 'users',
  op: 'insert',
  oldRow: null,
  newRow: { id: 'user_abc', email: 'a@b.c' },
  writtenAt: new Date(),
})

// The framework emits (on postgres):
INSERT INTO "_voltro_cdc_log" (table_name, op, old_row, new_row, written_at)
VALUES ($1, $2, $3, $4, $5)
RETURNING *

// The returned row carries the server-assigned id:
// { id: 4221, tableName: 'users', op: 'insert', ... }

This means:

• You can't pre-compute the ID before insert. The server hands it back.
• Distributed inserts (multiple processes hitting the same table) take an extra round-trip vs client-generated schemes.
• The auto-increment counter is shared per-table. Two parallel inserts get sequentially distinct IDs (DB-enforced); no race window.

#What you lose vs TypeID

• Pre-computed IDs: typeid/ulid/snowflake are generated client-side. Numeric forces a server round-trip.
• Cursor pagination via lexical sort: numeric works for paginateById because integers sort lexically when zero-padded for comparison, but ORDER BY id against an int column does the right thing without padding.
• Brand-by-table at compile time: branded types still flow (CdcLogId ≠ AuditId) but at runtime an unsigned 1 is a 1 is a 1.
• URL safety against enumeration: 4221 invites guessing; cdc_01j5xkqyz8x3n4m9pvabcd does not.

#What you gain

• Compact joins: BIGINT FK is 8 bytes; TypeID/ULID FK is 30+ bytes. On hot join paths with hundreds of millions of rows, the index size matters.
• Cheap range scans by id: WHERE id BETWEEN 1000 AND 2000 against an INT column hits a B-tree leaf range in O(log n + k). String-typed PKs do the same big-O but the constants are bigger.
• Familiar to DBAs: every relational DBA knows BIGSERIAL / AUTO_INCREMENT / IDENTITY. The CDC log table looks like every other audit table they've maintained.

#Caveats

• Auto-increment sequences can be reset. TRUNCATE TABLE … RESTART IDENTITY on postgres resets the counter; mysql's ALTER TABLE … AUTO_INCREMENT = 1 does the same. If you do this in dev and the table still has historical numeric IDs in FKs elsewhere, you can collide new rows with old references.
• Cross-DB migrations are painful. Re-mapping every FK from old IDs to new IDs is an O(n × m) operation that locks every related table during the rewrite.
• Serial overflow exists. BIGINT's 9.2 × 10^18 ceiling is essentially unreachable for normal workloads but very-high-throughput multi-decade systems have hit it. If you're inserting 1M rows/sec for 100 years, plan for it.

#Where it lives

• voltro/packages/database/src/migrate.ts — numericIdSql(name, dialect) emits the per-dialect column shape
• voltro/packages/database/src/idGenerator.ts — numeric scheme registers no generator (server-side)
• voltro/packages/runtime/src/storeMiddleware.ts — skips id injection when the registered scheme is numeric; falls through to RETURNING / OUTPUT to capture the server-assigned value