Skip to main content

Data

Kilimanjaro's data plane gives every project a chainable query builder over its Postgres collections, full-text search, read-only SQL with bound parameters, realtime subscriptions, ordered streams, and a first-class object-storage primitive co-located with the relational data.

1. Data model​

1.1 Collections are Postgres tables​

A collection is a Postgres table in your project's user-data schema. Every project owns three schema families:

SchemaContentsSDK access path
k9o_<proj_id>User-defined collections — your application's datak9o.from(collection)
k9o_<proj_id>_sysPlatform system tables (audit, policies, gate tokens, idempotency log, end-user identity tables)Typed APIs: k9o.audit, k9o.policies, gate.* — never via k9o.from()
k9o_<proj_id>_ext_<name>Extension-owned tables (one schema per installed extension)k9o.extension(name).from(collection)

Collection names are bare and unprefixed within k9o_<proj_id>. Schema-level isolation makes cross-project namespace collisions structurally impossible. A k9o.from('audit_log') call resolves a user-named audit_log collection in your user-data schema — it does not touch the platform's system-schema audit_log.

1.2 Record shape and reserved fields​

Every collection row is a JSON-serialisable object on the SDK surface. The platform reserves one top-level field:

FieldTypeDescription
idstringAuto-generated Stripe-style identifier: rec_<12-char base62>. Immutable from creation.

All other fields are caller-defined. The id prefix rec_ extends the conventions §3.2 resource-ID catalogue.

Your project's schema is the source of truth for column types, and it flows through the stack end-to-end. You declare columns in a migration with Postgres types; the SDK codegen reads that schema and generates a typed record interface for each collection (a TypeScript interface, a Python TypedDict, a Go struct, and so on across all twelve SDK families); your application code gets compile-time type checking against the column shapes you migrated; and the gateway validates writes against the Postgres column types at runtime before they hit the database. One declaration in the migration, three layers of enforcement — your IDE catches mistakes as you type, the SDK rejects malformed payloads before they go on the wire, and Postgres is the final authority.

1.3 Query surface​

The SDKs query a collection through the builder, k9o.from(collection) (§3). Full-text search is search (§3.5), and read-only SQL, including federated analytics, is query (§4). Each returns the same result shape, {data: Record[], meta: {...}}. Each respects the project-scoping invariant; queries are schema-qualified to k9o_<proj_id> without exception. Each is subject to JSON-policy RLS; policy filters compose as additional AND constraints (with one boundary on the raw-SQL surface for subject-scoped rules — see §4.4).

2. Collection lifecycle via migrations​

2.1 No ad-hoc DDL​

A collection does not exist until a migration creates it. The platform does not support ad-hoc DDL at the SDK layer — k9o.from('newcollection').insert({...}) against a collection without a migration returns K9O_COLLECTION_NOT_FOUND (HTTP 404). Schema drift is structurally impossible: the schema is always the result of an auditable sequence of migration files.

2.2 Migration shape​

A migration is a JSON object with a monotonic version, a slug, an up operation, a reversible down, and an optional policy section. Schema and policy couple when they change together. k9o migration apply sends the migrations to the gateway as { "migrations": [ ... ] }:

{
"migrations": [
{
"version": 1,
"slug": "create_users",
"up": {
"create_table": {
"name": "users",
"columns": [
{ "name": "id", "type": "text", "primary_key": true, "default": "gen_random_uuid()" },
{ "name": "email", "type": "text", "not_null": true, "unique": true },
{ "name": "role", "type": "text", "not_null": true, "default": "'member'" }
],
"indexes": [{ "columns": ["email"], "unique": true }]
}
},
"down": { "drop_table": "users" },
"policy": {
"collections": {
"users": {
"actions": {
"select": {
"rule": {
"or": [
{ "eq": ["subject.id", "resource.id"] },
{ "contains": ["subject.roles", "ORG_ADMIN"] }
]
}
},
"update": { "rule": { "eq": ["subject.id", "resource.id"] } },
"delete": { "rule": { "contains": ["subject.roles", "ORG_ADMIN"] } }
}
}
}
}
}
]
}
BehaviourNotes
Apply-time validationSchema changes that introduce undefined-policy state are rejected with K9O_MIGRATION_POLICY_VALIDATION_FAILED (HTTP 400) unless the migration declares noPolicy: true.
up / down symmetryBoth sections required. down must be fully reversible against the state produced by up. Validated at k9o migration validate. A file without a down section is rejected with K9O_INVALID_MIGRATION_FILE (HTTP 400).
Policy-only migrationsValid. Adjust policy without touching schema.
Schema-only migrationsValid. Emit a K9O_POLICY_NOT_COUPLED warning in meta.warnings unless the migration declares noPolicy: true.

2.3 Migration CLI​

CommandEffect
k9o migration create <slug>Scaffolds a new migration file with the current timestamp and a schema + policy stub.
k9o migration apply [--to <timestamp>]Applies all pending migrations up through <timestamp> (or all pending if omitted). Each migration is atomic in a Postgres transaction.
k9o migration rollback [--to <timestamp>]Rolls back applied migrations down to <timestamp>.
k9o migration statusLists applied and pending migrations.
k9o migration validateValidates all migration files for syntax, up/down completeness, and policy-coupling. Does not apply.
k9o db diff [<branch>]Schema diff against a named branch or main.
k9o branch create <name>Creates a dev branch with a forked schema.
k9o branch merge <name>Merges a dev branch back to main, resolving conflicts.

2.4 Conflict semantics​

Concurrent migration runs against the same project are serialised by a Postgres advisory lock. A second concurrent k9o migration apply call waits up to 60 seconds then throws K9O_MIGRATION_CONFLICT (HTTP 409) if the lock is not released. KError.context carries activeRunId.

3. Query builder​

3.1 .from(collection)​

The chainable query builder is the primary data-access API. It covers select, insert, update and delete, with filters, limit, cursor and count. The Documents tab of the examples under 4.1 shows a filtered, paged select and the writes.

A select, update or delete chain is lazy — no network call is made until the chain is awaited (or .execute() is called in languages without await). insert sends immediately and returns its result directly. select() takes no arguments and returns every column; update and delete target one record with .eq('id', ...). .insert() does not require the caller to supply an id; the platform generates rec_<12-char base62> via a trigger. Caller-supplied IDs are used as-is.

3.2 Filter vocabulary​

MethodSQL equivalentNotes
.eq(col, val)col = valEquality
.neq(col, val)col != valInequality
.gt(col, val)col > valGreater than
.gte(col, val)col >= valGreater than or equal
.lt(col, val)col < valLess than
.lte(col, val)col <= valLess than or equal
.in(col, vals[])col = ANY(vals)Inclusion in array
.notIn(col, vals[])col != ALL(vals)Exclusion from array
.like(col, pattern)col LIKE patternCase-sensitive
.ilike(col, pattern)col ILIKE patternCase-insensitive
.is(col, val)col IS valnull / true / false checks

Multiple filter calls on the same chain compose as AND. All filter values are parameterised before the Postgres call — they never interpolate into the SQL string.

3.3 Limit and pagination​

// Limit
.limit(100) // max per-surface limit 1000; default 50

// Cursor pagination — returns meta.cursor.next for the next page
.limit(50).cursor(cursorToken)

Paginate with a cursor: pass the meta.cursor.next token from one page as .cursor(...) on the next. See conventions §6.

3.4 Counting rows​

Counting is opt-in per query. .count(mode) takes 'none', 'exact' or 'estimated', and the total comes back in meta.cursor.totalCount (total_count in the snake_case SDKs).

search(collection, options) ranks the rows of a collection against a text query over one column, best match first. column names the text column, q is the query, and limit caps the rows returned. With type: 'websearch', q accepts quoted phrases and minus terms; language is the language of the text, for example english.

4. Raw SQL​

Raw SQL is read-only. Every mutation of customer data goes through the governed Document API, so field-level encryption, classification, row-level write policy, per-row audit and erasure registration apply to every write. For the general case use the governed bulk operations. For exceptional raw changes a maker-checker-gated migration operation exists; it can never write plaintext into an encrypted collection.

4.1 The query call​

query(sql, params) runs one read-only statement and returns the rows in the standard {data, meta} envelope. Write :name placeholders in the SQL and pass an object of values (a hash, a map or a dictionary, depending on the language); the values are bound by the database and never become part of the SQL text.

Create the client once

client
import { createClient, realtimeTopic } from "@k9o/core";

const k9o = createClient({
  orgSlug: "acme",
  projectSlug: "task-app",
  apiKey: process.env.K9O_API_KEY,
});

Data · Elastic Database · Query tab

1. SQL
// SQL for joins and reports. Read-only; values are bound by the database, never pasted into the text.
const { data: orders, meta } = await k9o.query(
  `SELECT o.id, o.total, c.email
     FROM orders o JOIN customers c ON c.id = o.customer_id
    WHERE o.status = :status AND o.total > :min
    ORDER BY o.total DESC
    LIMIT 25`,
  { status: "paid", min: 100 },
);
const timing = meta.timing as { totalMs: number } | undefined;
console.log(`${orders.length} orders in ${timing?.totalMs}ms`);
Output · bash
3 orders in 42ms

Over HTTP the call is POST /@{projectSlug}/query with a JSON body, { "sql": "...", "params": [...] }, answering { "data": [ ...rows ], "meta": { "rowCount": n } }. Parameters on the wire are positional: reference them as $1, $2, and pass the values in params (strings, numbers, booleans or null). The route accepts one read-only statement; a multi-statement body is refused.

Pass dynamic values as bound parameters, never inline in the SQL text. The gateway's injection scan refuses SQL that embeds dynamic content with K9O_INJECTION_DETECTED (HTTP 400); pure static queries such as SELECT NOW() are accepted.

4.2 Schema qualification​

Raw SQL queries are automatically schema-qualified to k9o_<proj_id>. Unqualified table names resolve to your user-data schema. Cross-schema references are rejected at the credential layer — with one exception, §4.3.

4.3 Federated analytics — dataset.* and pg.*​

A raw SQL query can reference a dataset — a governed, Parquet-backed analytical table on object storage — alongside your ordinary collections, in one query:

SELECT c.name, sum(s.amount) AS total
FROM dataset.q2_sales AS s
JOIN pg.customers AS c ON c.id = s.customer_id
GROUP BY c.name
ORDER BY total DESC;

dataset.<name> routes that part of the query to a columnar, partition-pruned read over Parquet; pg.<collection> is the non-leaking alias for your ordinary Postgres collections (it never exposes the underlying k9o_<proj_id> schema name), read under a dedicated read-only role. Row-level security still applies to the pg.* side of the join. The dataset side is read-only: any write that touches a dataset.* reference is rejected with K9O_ANALYTICS_READ_ONLY (400), and encrypted columns are never decrypted through the analytics route — there is no reveal path to it. An unqualified name that resolves to nothing returns K9O_QUERY_INVALID with a hint to qualify it as dataset.<name> or check the collection name, rather than a silent empty result. A query that references only collections behaves exactly as before — no dataset.* reference, no change.

Datasets are queried with the same query call by qualifying the table as dataset.<name>. The platform's own telemetry lands in dataset.telemetry_metrics, dataset.telemetry_logs and dataset.telemetry_traces:

Create the client once

client
import { createClient, realtimeTopic } from "@k9o/core";

const k9o = createClient({
  orgSlug: "acme",
  projectSlug: "task-app",
  apiKey: process.env.K9O_API_KEY,
});

Data · Elastic Analytics: query the Pulse Datasets

Pulse Datasets are columnar Parquet, queried with the same k9o.query by qualifying the table as dataset.<name>. They are read-only. The platform's own telemetry lands in dataset.telemetry_metrics, dataset.telemetry_logs and dataset.telemetry_traces.

1. Hourly latency
// Hourly latency for one metric: count, average and p95
const { data: latency } = await k9o.query(
  `SELECT date_trunc('hour', occurred_at) AS hour,
          count(*)                          AS samples,
          avg(value)                        AS avg_ms,
          quantile_cont(value, 0.95)        AS p95_ms
     FROM dataset.telemetry_metrics
    WHERE name = :metric AND occurred_at > now() - INTERVAL 7 DAY
    GROUP BY hour
    ORDER BY hour DESC`,
  { metric: "http.latency" },
);
Output · bash
{ hour: '2026-11-05T14:00:00.000000Z', samples: 4210, avg_ms: 38.6, p95_ms: 912.4 }
  • A query that mentions a dataset runs on the analytics engine; one that names only collections runs on the primary database.
  • The SDK can create and feed a dataset (k9o.analytics.create and k9o.analytics.write); the examples here only read the datasets.

A query that mentions a dataset runs on the analytics engine; one that names only collections runs on the primary database. Write the values of a dataset query into the SQL text; bound parameters apply to queries over collections.

A dataset is created and populated through Pulse Lakehouse (schema declaration, NDJSON ingestion, file upload, collection materialization) and read through Pulse Compute, which also covers rollups. The SDKs query datasets; they do not create or feed them. This subsection covers only the query-side federation syntax.

4.4 RLS on raw SQL​

RLS applies to raw SQL for project-scoped row rules — the common case, where the predicate keys on a fixed value like org_id or project_id. A SELECT * FROM users in a raw SQL call is subject to those policies exactly as the query builder is.

It does not extend to subject-scoped row rules — those that vary per calling end-user (e.g. "each user sees only their own rows"). Enforcing a per-caller predicate on raw SQL would require threading a caller-supplied scoping value through session state, which reopens the very injection vector the raw-SQL surface is designed to close. This is a permanent architectural boundary, not a gap awaiting a fix: a collection whose row-level enforcement must be finer than project scope should be read through the query builder (which carries the subject binding safely), not through raw SQL.

5. Realtime subscriptions​

5.1 Subscribing to a collection​

Realtime is Server-Sent Events: GET /@{projectSlug}/realtime/subscribe?topic=<topic>. The SDKs expose it as realtime.subscribe(topic), which you iterate for events. Build the topic for a collection with the SDK's topic helper (realtimeTopic.collection('orders') in TypeScript); on the wire it is collections.<name>.*:

Create the client once

client
import { createClient, realtimeTopic } from "@k9o/core";

const k9o = createClient({
  orgSlug: "acme",
  projectSlug: "task-app",
  apiKey: process.env.K9O_API_KEY,
});

Data · Real-Time Streaming

1. Change feed
import { realtimeTopic } from "@k9o/core";

// Change feed: one-way, server-sent events.
const feed = k9o.realtime.subscribe(realtimeTopic.collection("orders"));
for await (const event of feed) {
  console.log(event.eventType, event.payload); // e.g. row_inserted
  if (event.eventType === "row_deleted") feed.close();
}
Output · bash
row_inserted { id: 'order-124', total: 42 }
row_updated { id: 'order-124', total: 45 }
row_deleted { id: 'order-124' }

Each event is one frame:

type RealtimeEvent = {
topic: string; // e.g. 'collections.orders.row_inserted'
eventType: 'row_inserted' | 'row_updated' | 'row_deleted';
payload: { collection: string; id: string }; // identifies the changed row
occurredAt: string; // ISO 8601
eventId: string; // 'evt_' plus 22 characters; equals the SSE id
};

On the wire, each frame is an SSE event whose id is an event id (evt_ plus 22 characters) and whose event is the eventType. The same id is in the data as eventId. A captured row_inserted frame:

retry: 1000
:ok

id: evt_WEb1vGdy8PPuUQYflikoE0
event: row_inserted
data: {"topic":"collections.docs_feed_probe.row_inserted","eventType":"row_inserted","payload":{"collection":"docs_feed_probe","id":"rec_4G2o1KvQvTdH"},"occurredAt":"2026-10-27T10:20:38.969Z","eventId":"evt_WEb1vGdy8PPuUQYflikoE0"}

The payload is { collection, id }, not the row: a subscriber reads the row by id through the query builder. There is no server-side filter, no prior-row copy and no initial-state replay on subscribe — filter on the client, and read current state with a normal query.

5.2 Resuming and re-reading​

The client contract for a change feed:

  • Heartbeat. On a quiet feed the gateway sends a :hb comment line every 15 seconds.
  • Stream length. A stream lasts up to 300 seconds, then the gateway ends it.
  • Resume. Reconnect with the last event id you saw in the Last-Event-ID header. A resumed stream can repeat a few events from just before that id.
  • Duplicates. Drop duplicates by eventId.
  • Resync. If the position is no longer available (for example a cursor that is not an event id), the stream sends an event: resync frame instead of events. Re-read the current state through the data API, then subscribe again.
event: resync
data: {"reason":"cursor-unknown","action":"reread-source"}

This is the contract a client implements; an SDK's subscription helper may not reconnect for you (see your SDK page). To stop a subscription, leave the loop. The TypeScript SDK also accepts an AbortSignal and returns a handle with close().

5.3 RLS enforcement on subscription delivery​

RLS policies apply to every event delivered. If a row is inserted and the subscribing user's RLS policy would not allow them to read it, the event is silently suppressed — the subscriber does not receive it and does not learn of its existence. A subscription can never widen visibility past the auth context.

5.4 Connection caps​

TierMax concurrent subscriptions per project
Base Camp (Free)25
Ascent100
Ridge500
Summit2,500
Sovereign Control / Private PerimeterConfigurable

Exceeding the cap throws K9O_SUBSCRIPTION_LIMIT_EXCEEDED (HTTP 429); KError.recommendation carries guidance when there is any, and is null otherwise.

Subscriptions are metered as egress GB — each event delivery's serialised byte count accrues to meta.cost.egress.

5.5 Streams​

A change feed is one-way and carries row changes. A stream is a named, ordered log your own code publishes to and subscribes from. Events get a sequence number, a subscriber can start from any sequence, and replay reads history in pages. Create a stream with a display name and a maxBytes cap of at least 10,485,760 (10 MiB); the stream's slug, derived from the display name, is what the other calls take. publish accepts an optional subject, and sequence arrives as a string. The Streams tab of the change-feed example in 5.1 shows each call.

subscribe and replay start wherever fromSequence says; leave a subscription by breaking out of the loop, aborting its signal or cancelling its context. See Pulse Stream for retention, alerting and how streams are metered.

6. Object storage​

6.1 Bucket namespace model​

Buckets are named namespaces within a project. Bucket names must be unique within a project; they are not globally unique. Buckets do not span projects.

Project
└── Bucket (named namespace; e.g., "avatars", "documents", "exports")
└── Object (path + content)
ConstraintNotes
Logical bucket name3–63 chars; lowercase letters, numbers, and hyphens only; must not begin or end with a hyphen; must not be an IP address. Validated at createBucket time; returns K9O_INVALID_BUCKET_NAME (400) on violation.
Path structure within a bucketCaller-defined. The platform imposes no date/time namespacing.
Collision safetyupsert: false (default) — PUT to an existing object key returns K9O_OBJECT_ALREADY_EXISTS (409). upsert: true — overwrites the existing object.
Region pinningBuckets are region-co-located with the project's pinned Postgres shard. Moving a project's region is not supported post-creation.

6.2 Bucket lifecycle​

Create the client once

client
import { createClient, realtimeTopic } from "@k9o/core";

const k9o = createClient({
  orgSlug: "acme",
  projectSlug: "task-app",
  apiKey: process.env.K9O_API_KEY,
});

Data · Object Storage

1. Upload and share
const avatars = k9o.storage.from("avatars");

// Upload, tag, and share a time-limited link
const { data: stored } = await avatars.upload(`users/${userId}/avatar.png`, file, {
  contentType: "image/png",
  upsert: true,
  metadata: { classification: "public", uploader: userId },
});

const { data: link } = await avatars.createSignedUrl(stored.path, { expiresIn: 600 });
console.log(link.signedUrl);

const { data: objects } = await avatars.list({ prefix: `users/${userId}/` });
await avatars.remove(stored.path);
Output · bash
{ path: 'users/user-123/avatar.png', size: 48213, lastModified: '2026-11-05T10:20:31.000Z' }
https://acme.cdn-de01.k9o.io/k9o-objects-<region>-<id>/k9o/<project id>/buckets/avatars/users/user-123/avatar.png?X-Amz-Expires=600&X-Amz-Signature=...
  • remove takes one object path as a string.
  • expiresIn is clamped to 900 seconds: a longer request is accepted and the response reports expiresIn: 900, so plan for 15 minutes. Fetching it returns the uploaded bytes.

A bucket takes a fileSizeLimit (bytes) and a list of allowedMimeTypes. Deleting a bucket that still holds objects is refused with 409 (K9O_STATE_CONFLICT, reason bucket_not_empty), so delete its objects first and each leaves with its own object.deleted event. Deleting an empty bucket also deletes the function triggers on it, and the result reports how many (triggersDropped).

6.3 Upload​

The upload endpoint is POST /@<projectSlug>/storage/buckets/<bucket>/objects with Content-Type: multipart/form-data. When malware scanning is enabled on the project (Console setting; off by default), all uploads are scanned before the object becomes accessible. A detected threat returns K9O_MALWARE_DETECTED (HTTP 422) and the object is quarantined.

Listing the objects under a prefix and removing them are shown with the upload above. Outside TypeScript, remove takes one path per call.

6.4 Download via signed URL​

Signed URLs require an explicit expiresIn, in seconds, with no default. The gateway accepts 1 to 604,800 and clamps any larger value to 900 seconds (15 minutes); the response carries the effective expiresIn.

The signed URL is a pre-authenticated GET URL. The binary download goes directly from the CDN / object store to the client — it does not proxy through the API gateway.

6.5 Storage access​

Access to buckets and objects follows the caller's project role:

RoleAllowed
Project memberRead and list objects
Project writerUpload and delete objects, in addition to read and list
Project adminBucket lifecycle, in addition to the above

A project's IAM policy can further narrow what a role may do. The ceiling applies to the buckets:<verb> actions and is qualified by bucket name.

6.6 Customer-managed encryption​

When a project's custody mode is BYOK or HYOK, storage objects are encrypted at rest under the project's customer-managed key. Upload and download APIs are identical across custody modes — the encryption is transparent. Object content is encrypted under a data encryption key (DEK), which is itself wrapped under the project's custody key; custody-mode transitions re-wrap the DEK envelope without re-encrypting object content.

6.7 CDN and metering​

Storage objects are served through a regional CDN co-located with the project's pinned region. Cache-Control headers are caller-supplied at upload time or via bucket default.

Storage bytes accrue to the storage GB-mo billing dimension; egress (signed-URL downloads) accrues to egress GB. Intra-region egress is metered at the intra-region rate; cross-region and cross-sovereignty-boundary egress accrue at the higher zones.

7. Durability​

7.1 Per-shard ACID​

Single-project writes are fully ACID within a shard:

PropertyMechanism
AtomicityEach write, and each migration, runs in a Postgres transaction: it commits fully or rolls back fully. No partial commits.
ConsistencyPostgres constraint evaluation (NOT NULL, UNIQUE, CHECK, foreign-key references) runs within the transaction. Violations throw K9O_CONSTRAINT_VIOLATION (HTTP 409) and roll back.
IsolationPostgres transaction isolation applies within each write. The data plane does not expose interactive transactions or isolation levels.
DurabilityCommitted transactions are synchronously written to the Postgres WAL before commit returns. WAL is continuously archived to object storage.

Cross-project writes are structurally impossible.

7.2 Storage durability — 11×9​

Object storage and collection data are guaranteed at 99.999999999% (11 × 9) durability through:

  • Synchronous Postgres streaming replication within the shard cluster to at minimum two synchronous replicas in distinct availability zones. A write is not acknowledged until it is replicated to both standbys.
  • Continuous WAL archival to geo-redundant object storage within the project's pinned sovereignty region. WAL segments flush at sub-second intervals.
  • Object storage replication at the infrastructure layer (minimum three availability-zone copies within the region).

The 11×9 commitment is a storage-durability guarantee, not an availability or latency guarantee. A durably committed write survives hardware and AZ failures; it does not guarantee the write can be read within any latency bound during a partial region outage.

7.3 Subscription delivery guarantees​

A subscription is a live stream, not a durable log. Events are served from the gateway's recent in-memory window; after a disconnect, resume with the last SSE id as Last-Event-ID, drop duplicates by eventId, or re-read current state when the stream sends resync (§5.2). Callers use record.id for idempotent event handling.