SQL Overview

Kimberlite speaks PostgreSQL-compatible SQL against its append-only log. Every INSERT/UPDATE/DELETE appends an event; every SELECT reads the current derived view, or a point-in-time snapshot via AS OF TIMESTAMP / AT OFFSET.

Quick example

-- DDL
CREATE TABLE patients (
  id BIGINT PRIMARY KEY,
  name TEXT NOT NULL,
  dob TIMESTAMP,
  active BOOLEAN
);

-- DML (parameterized)
INSERT INTO patients (id, name, dob, active)
VALUES ($1, $2, $3, $4);

-- SELECT with JOIN, WHERE, GROUP BY, aggregates
SELECT p.name, COUNT(e.id) AS visits
FROM patients p
LEFT JOIN encounters e ON e.patient_id = p.id
WHERE p.active = true
GROUP BY p.name
HAVING COUNT(e.id) > 0
ORDER BY visits DESC
LIMIT 10;

-- Time-travel: state two weeks ago
SELECT * FROM patients AS OF TIMESTAMP '2024-01-15 10:30:00';

Supported today (v0.4)

DDL

DML

Queries

Resource limits (configurable; defaults listed):

• Max JOIN output: 1,000,000 rows
• Max GROUP count: 100,000 groups

Not supported in v0.4

Attempts to use unsupported syntax (e.g. WITH RECURSIVE) return a clean error rather than silently misbehaving.

Data types

All numeric types compile to BIGINT on the wire today. SMALLINT, INTEGER, REAL, DOUBLE PRECISION, DECIMAL, JSON, BYTEA are not first-class types at the protocol layer yet — store encoded blobs in the append-only event API if you need them.

Time-travel and audit

Every write is an immutable log entry. You can query state at any historical point by offset or timestamp:

-- Current
SELECT * FROM patients WHERE id = 123;

-- At a specific log offset
SELECT * FROM patients AT OFFSET 4200 WHERE id = 123;

-- At a wall-clock time
SELECT * FROM patients
AS OF TIMESTAMP '2024-01-15 10:30:00'
WHERE id = 123;

This replaces manual audit-table machinery: the audit trail is the primary store.

Multi-tenant isolation

Queries are automatically scoped to the tenant the client authenticated as. Non-admin identities cannot observe rows from other tenants even if the SQL would select them. See concepts/multitenancy.

Related reference

• DDL Reference — CREATE TABLE, ALTER TABLE, CREATE INDEX
• DML Reference — INSERT, UPDATE, DELETE
• Query Reference — SELECT, joins, aggregates, time-travel
• SQL Engine Design — internals

Key takeaway: Kimberlite SQL is a real PostgreSQL-compatible subset today — INNER/LEFT/RIGHT/FULL/CROSS joins (with USING), aggregates with FILTER (WHERE …), non-recursive and recursive CTEs, IN/EXISTS subqueries, set operations (UNION/INTERSECT/EXCEPT), JSON operators, parameterised queries (including LIMIT $N / OFFSET $N), time-travel, and window functions all work. Multi-statement transactions, scalar function projections, and ALTER TABLE end-to-end execution are on the roadmap; attempts to use unsupported syntax fail cleanly rather than silently misbehave.