Type System Specification

Status: v1.2 (frozen contract — additive-only changes after this point) Owner: datapipelines.co core Depends on: none (foundational spec — other specs depend on this) Last updated: 2026-08-07

1. Purpose

The Type System is the API contract between datapipelines.co and every client — whether that client is an agentic tool (Claude/GLM/Copilot) calling via MCP, a .NET service calling the REST API, a browser dashboard rendering results, or another pipeline reading this pipeline's output.

It defines:

1. A canonical set of logical types that represent the union of what any supported source database can produce.
2. Wire encoding rules for each logical type when serialized to JSON.
3. Per-dialect source-to-canonical mappings for every supported database (PG, Oracle, MSSQL, MySQL, H2, DuckDB, SQLite).
4. H2 staging type mappings for the in-memory staging layer.
5. The schema envelope structure that travels with every result set.

The canonical types are deliberately small (11 types) and versioned with an additive-only stability promise (§9). Clients build against this contract; churn breaks them.

2. Design Principles

1. Generic over specialized. Collapse types whose distinctions are academic for the analytics/federated-query use case (e.g., REAL/DOUBLE collapse into DECIMAL with their representable precision).
2. Type name is the wire contract. The presence of BIG in a type name (BIGINTEGER, BIGDECIMAL) signals "serializes as JSON string because the value space exceeds IEEE 754 double safe range." Clients switching on type name know what to expect on the wire without consulting additional metadata.
3. Lossless where loss matters, pragmatic elsewhere. Numeric precision is preserved for types that exceed IEEE 754 (BIG*). Approximate numerics (REAL/DOUBLE) collapse to DECIMAL with their representable precision because they were never exact to begin with — false precision is not a virtue.
4. Mapping by type and precision, never by value. A DECIMAL(18,2) column serializes as BIGDECIMAL → string, even if every actual value would fit in a double. Wire format is stable per-column, declared once in the schema, joins and parsers do not break.
5. Source-timezone normalization. All timestamp-bearing types normalize to UTC on ingest. The canonical type system has no notion of "TIMESTAMP WITH source TIME ZONE" — UTC is the canonical zone.
6. Additive-only evolution. Types and rules never removed or renamed; new types added under new version bumps. Clients coded against v1 will continue to work against vN. The full promise is stated in §9 Stability Promise; its client-side counterpart is the unknown-field rule in §7.1 — clients MUST ignore fields they do not recognize.

3. Canonical Types (v1)

The canonical type set is 11 types.

3.1 Wire encoding summary

number    → INTEGER, DECIMAL(p,s) where p ≤ 15
string    → BIGINTEGER, BIGDECIMAL(p,s) where p > 15, STRING, BINARY, DATE, TIME, TIMESTAMP
boolean   → BOOLEAN
null      → NULL

3.2 Why INTEGER/BIGINTEGER are split, not collapsed

JS Number.MAX_SAFE_INTEGER = 2^53 − 1. Int32 max = 2^31 − 1. Int64 max = 2^63 − 1.

• Integer types whose entire value space fits in double-safe range (≤ 2^53 − 1) serialize as JSON number.
• Integer types whose value space exceeds double-safe range (> 2^53 − 1) serialize as JSON string.

In practice, no source database has a type in the 2^31 to 2^53 range — they have either int32 or int64. So the boundary collapses cleanly: int32-and-smaller → INTEGER (number); int64 → BIGINTEGER (string).

3.3 Why DECIMAL/BIGDECIMAL are split at precision 15

IEEE 754 double holds ~15–17 significant decimal digits. Conservative threshold is 15 (round down for safety margin).

• DECIMAL with precision ≤ 15 → all values in the column's value space are losslessly representable as double → JSON number.
• DECIMAL with precision > 15 → some values lose precision in double → JSON string.

3.4 Why REAL/DOUBLE collapse into DECIMAL

Approximate numerics (REAL/FLOAT/DOUBLE) are IEEE 754 floats — by definition not exact. Treating them as DECIMAL with their representable precision is honest:

• REAL (float32, ~7 sig digits) → DECIMAL(7) (no scale — scale is meaningless for approximate)
• DOUBLE (float64, ~15 sig digits) → DECIMAL(15) (no scale)

The schema marks these by omitting the scale field: {type: "DECIMAL", precision: 15}. Exact-numeric DECIMALs always include scale: {type: "DECIMAL", precision: 15, scale: 4}.

This collapses the type system (no separate REAL/DOUBLE) without losing meaningful information — clients that need to know "this was approximate" infer it from the absent scale field.

3.5 Egress serialization rules (normative)

These rules fix the exact bytes a client receives. They apply to every egress path uniformly — SSE data_ready payloads, the REST result cursor, and MCP tool results.

1. TIMESTAMP — ISO 8601 with a literal Z suffix and exactly 6 fractional digits (microseconds), zero-padded: "2026-08-05T19:30:00.123456Z", "2026-08-05T19:30:00.000000Z". Never fewer digits, never more; sub-microsecond source precision is truncated (not rounded up) at ingest. Fixed width means clients can parse and sort lexicographically.
2. TIME — ISO 8601 time-of-day with exactly 6 fractional digits, zero-padded, and no zone designator: "14:30:00.123456", "00:00:00.000000".
3. DATE — ISO 8601 calendar date, no time component, no zone: "2026-08-05".
4. BINARY — standard base64 as defined by RFC 4648 §4, with = padding. The URL-safe alphabet (RFC 4648 §5, -/_) is not used, and the encoded string carries no line breaks and no data: prefix.
5. BIGINTEGER / BIGDECIMAL — JSON string holding the plain decimal representation; no exponent notation, no thousands separators. BIGDECIMAL preserves the source's trailing zeros to its declared scale ("12345.60", not "12345.6"); an exact-unsized BIGDECIMAL (scale omitted, §4) renders each value with the value's own scale.
6. NULL values — a NULL in any column serializes as JSON null, regardless of the column's canonical type.

4. Precision and Scale Semantics

Omitted precision on BIGDECIMAL means unbounded; omitted scale on BIGDECIMAL means unknown (normative). There is exactly one rule for an unsized source numeric (today: PostgreSQL numeric/decimal declared without precision, Oracle NUMBER declared without precision, and any exact-numeric expression that carries no typmod): the envelope reports BIGDECIMAL with both the precision and scale fields omitted. Omitted precision is normative shorthand for "the source declares no precision limit; assume unbounded"; omitted scale is normative shorthand for "the source declares no scale; every value carries its own."

• The envelope never reports a synthetic ceiling for this case — neither PostgreSQL's internal maximum digit count nor another dialect's storage maximum may be substituted. A fabricated bound would be a lie about the source column and would break clients that size their local decimal buffers from it. (Oracle's 38 digits for a bare NUMBER is Oracle's storage maximum, not a declared bound — and ojdbc reports scale −127 for that column, its scale-unspecified marker, while typmod-less Oracle expressions report scale 0; both mean unknown. Measured 2026-09-08, Oracle 21c XE.)
• scale is omitted, not 0 (adjudicated 2026-09-08, defect 100). The driver reports scale 0 for unknown when the typmod is gone — pgjdbc reports precision=0 scale=0 for SUM(fare) over a NUMERIC(10,2) column. Declaring scale: 0 asserts "integer", and the first exact store enforced that lie: H2 staged the column as DECIMAL(100000, 0) and every fractional cent was truncated on insert (the live nyc/mobility borough_od_matrix, airport_access_by_borough and weather_sensitivity_by_borough pipelines returned whole-dollar totals until this fix — and mobility_briefing, which composes borough_od_matrix, inherited the truncation). The BIGDECIMAL wire contract — a JSON string carrying the exact decimal — is unchanged: each value renders with its own scale.
• Clients that need a bound must impose their own (or CAST in the source query — the readable contract, e.g. SUM(x)::NUMERIC(14,2)). Staging applies its own ceiling separately: see the H2 overflow policy in §6.
• Defensive generalization (2026-08-08): ANY driver reporting precision ≤ 0 on an exact numeric takes this same unbounded encoding (BIGDECIMAL, precision omitted) — a DECIMAL(0) descriptor would violate §7.1's minimum: 1, and throwing on a driver quirk would violate §8.2's never-fail rule. Written for PG; applies everywhere the situation arises.

4.1 Why scale is omitted for approximate numerics

Approximate numerics have variable scale per value: 3.14 and 6.022e23 are both valid doubles, with zero and many fractional digits respectively. Declaring a fixed scale would be a lie. Clients consuming a DECIMAL without scale should treat it as "IEEE 754 double rendered as JSON number; do not assume fixed fractional digits."

4.2 H2 staging behavior for approximate numerics

Staging in H2 uses H2's native DOUBLE type for approximate-numeric origins (lossless round-trip), not DECIMAL(p, ?). The canonical label DECIMAL(15) is the API contract; the H2 storage choice is internal and invisible to clients.

Approximate precision is not preserved across staging (adjudicated 2026-08-08). A single-precision origin (DECIMAL(7)) that passes through tempdb reads back as DECIMAL(15) — H2 DOUBLE metadata cannot distinguish the two. The same source column therefore advertises DECIMAL(7) when read directly and DECIMAL(15) after staging. This widening is accepted: the value is approximate by definition, scale stays omitted either way, and widening is the safe direction for clients sizing buffers — treat any approximate DECIMAL as "≤ 15 representable digits".

5. Source-to-Canonical Mapping Tables

Each supported source dialect has a deterministic mapping from JDBC java.sql.Types + column metadata (precision, scale, type name) to a canonical LogicalType. Mappings are mechanical and exhaustive — every JDBC type in each dialect maps to exactly one canonical type.

5.1 PostgreSQL

5.2 Oracle

Oracle has significant quirks; the most important gotcha is that Oracle's DATE type stores both date AND time — it is semantically a TIMESTAMP, not a DATE. Our mapper reflects this.

Oracle DATE gotcha — explicit policy: Oracle's DATE type stores both date and time-of-day (no fractional seconds). Mapping it to canonical DATE would silently truncate the time component — a data-loss bug. The mapper always maps Oracle DATE → canonical TIMESTAMP. If a source table truly has date-only data in an Oracle DATE column, the canonical TIMESTAMP value will simply carry T00:00:00Z. This is honest and lossless.

NUMBER(1) is NOT auto-promoted to BOOLEAN. Some frameworks infer boolean from NUMBER(1). We do not — that's heuristic and lossy of intent. NUMBER(1) → INTEGER.

5.3 Microsoft SQL Server

sql_variant policy: MSSQL's sql_variant can hold values of different types per row. The mapper cannot pick a single canonical type per column. Policy: map to STRING, serialize each value via its underlying type's toString, and emit a warning in the response (warnings array — see Response Envelope spec). Pipeline authors should CAST sql_variant values to a concrete type in their source query template.

5.4 MySQL / MariaDB

MySQL's BOOLEAN is an alias for TINYINT(1). The JDBC driver reports the column type — we map by what the driver reports, not by declared name.

5.5 H2 (staging layer — used internally)

H2 is the staging database. We map H2 → canonical when reading back from staging for the OUTPUT node.

5.6 DuckDB

DuckDB has 128-bit integers (HUGEINT) — unusual but supported.

5.7 SQLite

SQLite is dynamically typed: columns have "type affinity" (INTEGER, TEXT, BLOB, REAL, NUMERIC), not fixed types. Mapping is based on declared column type string parsing and (where possible) actual value inspection.

The two BLOB-affinity rows are not in conflict — the discriminator is the declared type string, not the affinity: a column declared BLOB (or any type whose name contains BLOB) maps to canonical BINARY; a column declared with no type at all (e.g. CREATE TABLE t (c)), which SQLite also gives BLOB affinity, maps to canonical STRING. Untyped SQLite columns in practice hold text, and base64-encoding text as BINARY would be the more damaging error. Affinity names follow the SQLite determination rules (substring match on the declared type, in order INT → CHAR/CLOB/TEXT → BLOB/none → REAL/FLOA/DOUB → NUMERIC).

SQLite DATE/TIME/TIMESTAMP policy: SQLite has no native temporal types. Conventions vary widely: ISO 8601 text, Unix epoch seconds (INTEGER), Unix epoch millis (INTEGER), Julian day (REAL). In v1, we map all temporal-looking columns from SQLite to STRING and do not attempt heuristic parsing. Pipeline authors who know their storage convention should CAST in their query template or post-process the result.

5.8 LAKE (Parquet / Iceberg through DuckDB)

LAKE is a distinct dialect (Datasources §4.1) whose engine is DuckDB, so its ingress mapping is §5.6's, verbatim — the same engine reporting the same getColumns metadata for the same JDBC type codes. TypeMappers.forDialect(LAKE) returns DuckDbTypeMapper rather than a second copy, because two identical tables are two tables to drift.

What Parquet and Iceberg add is nested types (STRUCT, LIST, MAP), which DuckDB reports through the same catalog. They fall under §8.2's unknown-type policy today — canonical STRING with a warning — exactly as they do on an embedded DuckDB file, and nested-type support is v2 for every dialect at once (§12).

6. H2 Staging Type Mapping (Canonical → H2)

When the executor stages data from a source into H2 (CREATE TABLE staging.x ...), each canonical type maps to a specific H2 column type.

Round-trip rule (normative, 2026-08-08; storage amended 2026-09-08): an exact-unsized BIGDECIMAL (precision and scale omitted, §4) stages as DECFLOAT(100000) — H2 2.x's exact, arbitrary-scale decimal at the same 100000-digit ceiling — and on the way BACK (reading staged data through the H2 ingress mapper), a reported exact-numeric precision at the ceiling (≥ 100000) reads as the unbounded encoding again: BIGDECIMAL with precision and scale omitted. Without this rule the storage ceiling leaks into the schema envelope as exactly the fabricated bound §4 forbids. Consequence, accepted as truthful: a genuine H2 source column declared DECIMAL(100000, s) also reports unbounded — it sits at H2's own maximum, so "unbounded" is not a lie about it.

Overflow policy: H2 2.x supports DECIMAL precision up to 100000. Every bounded precision any supported dialect can declare fits well inside that. If a staged value's precision exceeds 100000 digits — only reachable from an unbounded source numeric (§4) carrying an extreme value — the staging step fails with error code pipeline.staging.precision_overflow. Pipeline authors should reduce precision in the source query (CAST to a smaller type) or restructure. The same threshold is stated in Staging §5.2; the two MUST stay identical.

7. Schema Envelope Structure

Every result set carries a schema describing its columns. The schema is an array of column descriptors.

7.1 Column descriptor (JSON Schema)

{
  "$schema": "https://datapipelines.co/schema/column.schema.json",
  "type": "object",
  "additionalProperties": true,
  "required": ["name", "type"],
  "properties": {
    "name": {
      "type": "string",
      "description": "Column name as returned by the OUTPUT node's SQL.",
      "minLength": 1
    },
    "type": {
      "type": "string",
      "enum": [
        "NULL", "BOOLEAN", "INTEGER", "BIGINTEGER",
        "DECIMAL", "BIGDECIMAL",
        "STRING", "BINARY",
        "DATE", "TIME", "TIMESTAMP"
      ]
    },
    "precision": {
      "type": "integer",
      "minimum": 1,
      "description": "Required for DECIMAL. Present for BIGDECIMAL except when the source numeric is unsized — an omitted precision on BIGDECIMAL means unbounded (see §4)."
    },
    "scale": {
      "type": "integer",
      "minimum": 0,
      "description": "Required for exact-numeric DECIMAL and for BIGDECIMAL with a declared precision. Omitted for approximate-numeric DECIMAL (source was REAL/DOUBLE) and for exact-unsized BIGDECIMAL (source numeric unsized — the driver reports 0 for *unknown*; see §4)."
    },
    "nullable": {
      "type": "boolean",
      "description": "Optional. True when the source column may contain NULL, false when the source declares it NOT NULL. Omitted when the driver does not report nullability (JDBC columnNullableUnknown) — an absent field means unknown, NOT 'not nullable'."
    }
  },
  "allOf": [
    {
      "if": { "properties": { "type": { "const": "DECIMAL" } } },
      "then": { "required": ["precision"] }
    },
    {
      "if": { "properties": { "type": { "const": "BIGDECIMAL" } }, "required": ["precision"] },
      "then": { "required": ["scale"] }
    }
  ]
}

Unknown fields (normative). additionalProperties is true by design: §9.2 promises that new optional fields may be added to the schema envelope without a version bump, and a closed schema would make every such addition breaking. Therefore:

• Producers MUST emit only the fields defined here plus fields introduced by a later revision of this spec.
• Clients MUST ignore fields they do not recognize. A client MUST NOT reject, error on, or fail validation of a column descriptor because it carries an unknown property. Strict-mode deserializers (Jackson FAIL_ON_UNKNOWN_PROPERTIES, System.Text.Json UnmappedMemberHandling.Disallow, Pydantic extra="forbid") MUST be configured off for this type.
• A client that rejects unknown fields is non-conformant, and breakage from a future additive field is that client's defect, not a contract break.
• Field removal or meaning change remains forbidden (§9.3) — the open object buys additive room, not licence to churn.

7.2 Schema envelope example

{
  "schema_version": 1,
  "schema": [
    {"name": "customer_id",   "type": "INTEGER", "nullable": false},
    {"name": "customer_name", "type": "STRING", "nullable": false},
    {"name": "total_amount",  "type": "BIGDECIMAL", "precision": 18, "scale": 2, "nullable": true},
    {"name": "unbounded_total", "type": "BIGDECIMAL"},
    {"name": "lifetime_value", "type": "DECIMAL", "precision": 12, "scale": 2},
    {"name": "measurement",   "type": "DECIMAL", "precision": 15},
    {"name": "order_count",   "type": "INTEGER"},
    {"name": "is_vip",        "type": "BOOLEAN"},
    {"name": "first_order_at", "type": "TIMESTAMP"},
    {"name": "renewal_date",  "type": "DATE"},
    {"name": "support_time",  "type": "TIME"},
    {"name": "logo",          "type": "BINARY"}
  ]
}

7.3 Field-by-field rules

• name — always present. From the OUTPUT node's SQL column alias.
• type — always present. One of the 11 canonical types.
• precision — present iff type ∈ {DECIMAL, BIGDECIMAL}, with one exception: omitted for BIGDECIMAL when the source numeric is unsized/unbounded (§4). Omitted precision on BIGDECIMAL means unbounded; it never means "unknown".
• scale — present iff:
  • type = BIGDECIMAL AND the source numeric declares a precision (bounded), or
  • type = DECIMAL AND the source was exact-numeric (NUMERIC/DECIMAL/MONEY).
  • Omitted when type = DECIMAL AND the source was approximate-numeric (REAL/FLOAT/DOUBLE), and when type = BIGDECIMAL AND the source numeric is unsized (§4 — the driver reports scale 0 for unknown; a declared 0 asserts "integer" and truncates at the first exact store).
• nullable — optional, any type. true/false mirror the driver's ResultSetMetaData.isNullable() verdict (columnNullable / columnNoNulls); the field is omitted when the driver reports columnNullableUnknown. Absence means unknown — clients MUST NOT read an absent nullable as false. Being optional and additive, it does not bump schema_version (§9.2).

8. Edge Cases and Policies

8.1 All-NULL columns

When a source query returns a column where every value is NULL (common with conditional CASE WHEN expressions), JDBC metadata still declares a type, but the column may not have a meaningful one. Policy: trust JDBC metadata. If the driver reports a type, use the standard mapping. If the driver reports NULL type (JDBC Types.NULL, code 0), emit canonical NULL.

8.2 Unknown / unmappable types

When a source column has a type the dialect mapper doesn't recognize (exotic Oracle object types, PG extension types, custom MSSQL CLR types, etc.), Policy: fall back to STRING, serialize the value via its toString(), and add a warning to the response envelope:

{
  "schema": [
    {"name": "weird_column", "type": "STRING"}
  ],
  "warnings": [
    {
      "code": "type_mapping.unknown_source_type",
      "message": "Source type 'pgvector' on column 'weird_column' has no canonical mapping; falling back to STRING.",
      "column": "weird_column",
      "source_type": "pgvector"
    }
  ]
}

The pipeline does not fail. The pipeline author sees the warning and fixes it (usually with a CAST in the template). The dispatch-level and mapper-level fallback branches that implement this policy are shown in §11.2.

8.3 Mixed-precision values in a single column (impossible in practice)

Cannot occur: a single column has one declared type in any source database, and the mapper is deterministic. Documented here for completeness only.

8.4 Timestamp timezone normalization

On ingest: when a source column has timezone info (TIMESTAMPTZ in PG, TIMESTAMP WITH TIME ZONE in Oracle/MSSQL/H2, datetimeoffset in MSSQL), the value is converted to UTC at read time. The original timezone is dropped — canonical TIMESTAMP carries UTC only.

For TIMESTAMP WITHOUT TIME ZONE: the source value is assumed to be in UTC (no conversion possible — the source has no TZ info). This is the documented assumption; pipeline authors working with non-UTC naive timestamps should declare their convention in source SQL.

Policy rationale: federated queries joining multiple sources with different TZ conventions produce inconsistent results if TZ is preserved per-source. UTC normalization is the only defensible default. Matches industry practice (Snowflake, BigQuery, Databricks).

Deployment precondition — the JVM default zone MUST be UTC (normative). The server REQUIRES -Duser.timezone=UTC (equivalently, TZ=UTC in the container environment). This is a hard precondition of every rule above, not an operational nicety:

• JDBC reads of zone-less types (ResultSet.getTimestamp() / getTime() / getDate() without an explicit Calendar) resolve against the JVM default zone. Under a non-UTC default, a source TIMESTAMP WITHOUT TIME ZONE is silently shifted by the offset before it ever reaches the mapper — a correctness bug the type system cannot detect or repair downstream.
• Zone-aware source values are likewise rendered through the default zone by several drivers on the way back out, so egress ISO 8601 strings would carry the wrong instant while still ending in Z. Silent, plausible, and wrong: the worst failure shape.
• The same precondition backs "TIMESTAMP WITHOUT TIME ZONE is assumed UTC" above and the TIMESTAMPTZ-everywhere rule for internal metadata storage.

The flag is baked into the Docker image entrypoint; bare-JVM and JAR deployments MUST pass it themselves — see Deployment §3.1. Deployments that cannot guarantee a UTC JVM are unsupported: every timestamp guarantee in this document is void without it.

8.5 Boolean-from-non-boolean sources

Some sources fake booleans:

• Oracle: NUMBER(1) with 0/1 (pre-23c, no native boolean)
• MySQL: TINYINT(1) — handled by JDBC driver detection
• MSSQL: BIT — semantically boolean

Policy: map by source type, not by inferred intent. NUMBER(1) in Oracle → INTEGER (not BOOLEAN). Pipeline authors who want boolean semantics should wrap in their query: CASE WHEN col = 1 THEN true ELSE false END AS col.

8.6 UUID and GUID types

Mapped to STRING (canonical UUID text form: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx). No canonical UUID type in v1. Most clients handle UUIDs as strings naturally.

8.7 JSON / JSONB / XML types

Mapped to STRING, value is the serialized JSON/XML text. Clients parse as they see fit. No canonical JSON type in v1 — collapsing to STRING keeps the type system small while preserving the data.

8.8 Money / currency types

Mapped to DECIMAL/BIGDECIMAL with the source's native precision and scale. PG money → BIGDECIMAL(19, 2). MSSQL money → BIGDECIMAL(19, 4), smallmoney → DECIMAL(10, 4). MySQL has no native money type — typically stored as DECIMAL(p, 2).

8.9 Out-of-range integers on ingest

If a source column is declared as INTEGER but contains a value that overflows int32 (rare bug in source schema), the ingest step fails with pipeline.staging.value_overflow. Pipeline author fixes via CAST in source SQL.

9. Stability Promise

The canonical type system is a versioned, additive-only contract.

9.1 What is frozen in v1

• The 11 canonical type names and their spellings.
• The wire encoding for each type (number/string/boolean/null).
• The precision-15 threshold between DECIMAL and BIGDECIMAL.
• The int32/int64 boundary between INTEGER and BIGINTEGER.
• The UTC normalization policy for TIMESTAMP.
• The H2 staging type mapping.

9.2 What is NOT frozen

• Per-dialect source mappings (a new database version may introduce types we map differently; mappings are versioned per dialect).
• The list of warning codes (additive).
• The schema envelope's optional fields (new optional fields may be added non-breakingly).

9.3 Evolution rules

• Never remove or rename a canonical type.
• Never change the wire encoding of an existing type.
• Never change the precision-15 or int32/int64 boundaries.
• New canonical types are added only under a schema_version bump.
• New canonical types are added with documented: source mapping rules, wire encoding, H2 staging mapping, and migration notes.
• Clients coded against v1 continue to work against vN without modification.

9.4 Versioning

• schema_version field on every schema envelope and every response envelope.
• v1 starts at schema_version: 1.
• Bumps are integers, monotonic, never reused.
• A bump's release notes document the additive change.

10. Worked Examples

10.1 End-to-end: PG numeric(18,2) → staging H2 → JSON response

1. Source: PG column total_amount declared as numeric(18, 2).
2. Ingest mapping: precision 18 > 15 → canonical BIGDECIMAL(18, 2).
3. H2 staging: BIGDECIMAL(18, 2) → H2 DECIMAL(18, 2). Value 12345.67 stored exactly.
4. Egress: read back from H2 via JDBC ResultSet.getBigDecimal(). Serialize as JSON string "12345.67" (lossless).
5. Schema entry: {"name": "total_amount", "type": "BIGDECIMAL", "precision": 18, "scale": 2}.
6. Client (.NET): parses string via decimal.Parse("12345.67") → System.Decimal. Lossless.
7. Client (JS): sees string "12345.67"; parses with Number() only if approximate is acceptable for the use case (e.g., chart rendering), otherwise uses Decimal.js or displays as-is.

10.2 End-to-end: PG bigint ID → staging → JSON response

1. Source: PG column event_id declared as bigint.
2. Ingest mapping: int64 → canonical BIGINTEGER.
3. H2 staging: BIGINTEGER → H2 BIGINT. Value 9223372036854775807 stored exactly.
4. Egress: read back via ResultSet.getLong(). Serialize as JSON string "9223372036854775807" (lossless).
5. Schema entry: {"name": "event_id", "type": "BIGINTEGER"}.
6. Client (.NET): long.Parse("9223372036854775807") → System.Int64. Lossless.
7. Client (JS): sees string "9223372036854775807"; uses BigInt() for arithmetic or displays as-is. JSON.parse does not corrupt the value (it stays a string).

10.3 End-to-end: PG timestamptz → staging → JSON response

1. Source: PG column created_at declared as timestamptz, value 2026-08-05 14:30:00.123456-05:00 (US Eastern).
2. Ingest mapping: timestamptz → canonical TIMESTAMP (TZ info to be normalized).
3. At read time: JDBC getTimestamp() returns the value in JVM default TZ (UTC-configured), so value read is 2026-08-05 19:30:00.123456 UTC.
4. H2 staging: stored as TIMESTAMP WITH TIME ZONE value 2026-08-05 19:30:00.123456+00.
5. Egress: read back, format as ISO 8601 UTC string: "2026-08-05T19:30:00.123456Z".
6. Schema entry: {"name": "created_at", "type": "TIMESTAMP"}.
7. Client: parses ISO 8601 string. Always UTC, always Z suffix. No TZ conversion logic needed on client.

10.4 End-to-end: Oracle DATE (with time component)

1. Source: Oracle column order_date declared as DATE, value 2026-08-05 14:30:00.
2. Ingest mapping: Oracle DATE → canonical TIMESTAMP (NOT canonical DATE — see §5.2).
3. H2 staging: stored as TIMESTAMP WITH TIME ZONE value 2026-08-05 14:30:00.000000+00 (assumed UTC since Oracle DATE has no TZ).
4. Egress: "2026-08-05T14:30:00.000000Z".
5. Schema entry: {"name": "order_date", "type": "TIMESTAMP"}.

10.5 End-to-end: MSSQL sql_variant column

1. Source: MSSQL column mixed_values declared as sql_variant, contains INT in some rows, VARCHAR in others.
2. Ingest mapping: sql_variant → canonical STRING (with warning).
3. H2 staging: stored as VARCHAR, value is the underlying type's toString.
4. Egress: all values as strings.
5. Schema entry: {"name": "mixed_values", "type": "STRING"}.
6. Warning in response:

   {
     "code": "type_mapping.sql_variant",
     "message": "Column 'mixed_values' is MSSQL sql_variant; values serialized as text. CAST to a concrete type in source SQL for typed access.",
     "column": "mixed_values"
   }
   

11. Implementation Notes (Non-Normative)

This section is informational; the normative content is in Sections 3–10.

11.1 Where this lives in the codebase

The type system is implemented in the typesystem Gradle module:

• LogicalType enum (the 11 types)
• Dialect enum (the 7 dialects, §5 — declared HERE, not in datasources: forDialect below needs it and this module depends on nothing internal; consumers get it transitively)
• ColumnSchema data class (name, type, precision?, scale?, nullable?)
• IngressTypeMapper interface + per-dialect implementations (PostgresTypeMapper, OracleTypeMapper, MssqlTypeMapper, MysqlTypeMapper, H2IngressMapper, DuckDbTypeMapper, SqliteTypeMapper)
• FallbackTypeMapper (unknown dialect / unrecognized JDBC type → STRING + warning, per §8.2)
• H2EgressMapper (canonical → H2 column type). Note the split: H2IngressMapper reads H2 JDBC metadata back into canonical types, H2EgressMapper produces the H2 DDL type for a canonical type. There is no H2TypeMapper — see Staging §5.3.
• JsonEncoder (canonical → wire representation)
• SchemaEnvelope data class (schema_version + schema array)

11.2 Mapper dispatch

Per-source mapping is dispatched by dialect identifier:

interface IngressTypeMapper {
    fun map(sqlType: Int, precision: Int, scale: Int, typeName: String): LogicalTypeMapping
}

data class LogicalTypeMapping(
    val type: LogicalType,
    val precision: Int? = null,
    val scale: Int? = null
)

object TypeMappers {
    fun forDialect(dialect: Dialect): IngressTypeMapper = when (dialect) {
        POSTGRES    -> PostgresTypeMapper
        ORACLE      -> OracleTypeMapper
        MSSQL       -> MssqlTypeMapper
        MYSQL       -> MysqlTypeMapper
        H2          -> H2IngressMapper
        DUCKDB      -> DuckDbTypeMapper
        SQLITE      -> SqliteTypeMapper
        LAKE        -> DuckDbTypeMapper   // §5.8 — DuckDB is the engine
        // Documented else path: a Dialect value this build does not know
        // (e.g. one added to the enum ahead of its mapper) degrades instead
        // of throwing — every column maps to STRING with the §8.2 warning.
        else        -> FallbackTypeMapper
    }
}

/**
 * Implements the §8.2 unknown-type policy. Also the delegate every
 * per-dialect mapper calls for a JDBC type code it does not recognize:
 * never throw, map to STRING, and attach one
 * `type_mapping.unknown_source_type` warning per affected column.
 */
object FallbackTypeMapper : IngressTypeMapper {
    override fun map(sqlType: Int, precision: Int, scale: Int, typeName: String) =
        LogicalTypeMapping(type = LogicalType.STRING)   // + warning, see §8.2
}

Fallback contract (§8.2 restated for implementers): unrecognized input never fails an execution. Both fallback paths — unknown Dialect at dispatch, and unknown JDBC type code inside a per-dialect mapper — resolve to canonical STRING, serialize values via toString(), and emit exactly one type_mapping.unknown_source_type warning naming the column and the source type.

The block above is illustrative, not compilable (2026-08-08). A when over the enum is exhaustive, so Kotlin flags the else as redundant — a hard error under §7.1's allWarningsAsErrors. Implementations use any equivalent total dispatch (v1 ships a map lookup with an elvis fallback); since neither form gets compile-time exhaustiveness back, a test must assert every Dialect resolves to a non-fallback mapper — that test is the guard when a dialect is added. Additionally, IngressTypeMapper carries a mapColumn(...) overload alongside map(...): §8.2's warning must name the affected column, which map's signature cannot do.

11.3 Testing the type system

The type system must have:

• Unit tests for every per-dialect mapper covering every JDBC type code that dialect produces.
• Round-trip tests: source value → H2 staging → JSON wire → client parsed value (lossless where expected).
• Edge case tests: NULL columns, mixed-precision overflow, Oracle DATE (not DATE), MSSQL sql_variant warnings, etc.
• Wire encoding tests: every canonical type serializes to the declared wire representation; JSON.parse on the serialized output preserves exact values for BIG* types.
• Egress format tests (§3.5): TIMESTAMP and TIME render exactly 6 fractional digits including the all-zero case; BINARY round-trips through a standard padded base64 decoder and contains no -/_ characters.
• Fallback tests (§8.2/§11.2): an unrecognized JDBC type code yields STRING plus exactly one type_mapping.unknown_source_type warning, and never throws.
• Unbounded-precision tests (§4): a PG numeric with no declared precision — or an exact-numeric expression whose typmod the engine drops — produces a BIGDECIMAL descriptor with no precision and no scale key, and that descriptor validates against the §7.1 JSON Schema.
• Unknown-field tests (§7.1): a column descriptor carrying an unrecognized property still deserializes successfully in the reference clients.

12. Open Questions / Future Additions

These are explicitly out of scope for v1 but tracked for future versions. Listed here so they are not forgotten.

• Nested types (struct, list/array, map): currently fall back to STRING. Future v2 could add canonical STRUCT, ARRAY, MAP types with schema-declared shapes.
• Geospatial types: currently fall back to STRING (WKT). Future v2 could add canonical GEOMETRY, GEOGRAPHY types with declared SRID.
• Intervals: currently STRING. Future v2 could add canonical INTERVAL_YEAR_MONTH, INTERVAL_DAY_TIME.
• UUID: currently STRING. Future v2 could add canonical UUID.
• ENUM: currently STRING. Future v2 could add canonical ENUM with declared allowed values.
• JSON: currently STRING. Future v2 could add canonical JSON with declared schema.
• BIT / bit strings: currently STRING (PG) or BINARY (MySQL). Future v2 could add canonical BIT_STRING.
• Schema-introspection endpoint: a /types or /schema endpoint exposing this canonical type list to clients and to MCP for tool-discovery. To be specified in REST API spec.

Appendix A: Change Log