Data model

Most of what makes SkimmerDB fast and cheap for you is decided before the first write: which namespaces you make, what your ids look like, and what goes in text, meta and vector. This page explains how data is stored and how to shape it.

The short version:

• One query reads one namespace. Put together what you query together; split what you'd delete, scope a key to, or bill apart.
• text is what you search, meta is what you filter, group and sort by, vector is meaning. q only looks at text; filters and SQL only look at meta (and id).
• Keep meta flat, typed and consistent: top-level keys, one type per key, times as integer seconds.
• Make ids stable and meaningful, so a retried write replaces rather than duplicates.
• Batch writes; import big files. Append-only data (events, logs, traces) is the ideal shape.

#How it's stored

A namespace is a manifest plus immutable segments in object storage (S3). Every write request becomes one entry in the manifest: its documents (a small segment) and the ids it deleted. Background compaction merges entries into bigger segments. Nothing is ever edited in place, and the newest entry naming an id is the one you see.

What that means for you:

• A write request is atomic. Its documents and deletes become visible together, or not at all. An import too, however many rows. Writing an id again replaces the whole document.

• One copy, three indexes. Each segment holds the documents once, compressed, with:

  • a trigram index over text, for exact and regex search;
  • the metadata as typed, compressed columns in blocks of 64K rows, with min/max, dictionaries and Bloom filters per block, for filters and SQL;
  • for vector namespaces, an IVF index of 1-bit codes with int8 rows for reranking.

  Search, vector search, filters and SQL read the same data: there is nothing to keep in sync.

• Servers are caches. RAM, then local NVMe, then object storage. A namespace nobody uses costs only its bytes in S3. The first query after a sleep opens it in a round trip or two ("temperature": "cold"), and a small namespace (a few KB) opens in a single read. Every answer says what it read from object storage (store_gets, store_bytes).

• No schema to declare or migrate. Columns are typed per segment, and the namespace's schema is their union. A new key shows up with the next write; older documents read it as NULL. A key whose values change type is read at the widest type seen (int < float < text).

#Choosing namespaces

Namespaces are free while idle, open on first use and need no setup, so make as many as your data has natural boundaries (unlimited on pro, 1,000 on free). The question is what each query must see: a search, count or SQL query reads exactly one namespace.

Vectors are per namespace too: one embedding model (one dims) per namespace. Changing models means a new namespace, written alongside the old one until it has caught up.

#Ids

• Use a natural key: the ticket number, the file path, run-42/step-3, <user>/<message id>. Writes are by id, so a retry after a timeout or a 503 write_unknown is safe: it replaces what may already be there. Random ids make every retry a duplicate.
• Events need unique ids, or each write replaces the last: <agent>/<run>/<step> or <source>-<offset> keep them unique and still meaningful. Leave id out of an import and rows get ids from the import and the row number.
• Ids can carry structure. They may hold slashes (GET .../docs/run-42/step-3), and SQL can match them: WHERE id LIKE 'run-42/%'. They're up to 512 bytes, one line.
• Ids aren't searched. q matches text only: put anything you want to find by text in text too.

#text, meta and vector

• q never matches metadata, and filters never look inside text. A value you need both ways (a customer name you search for and filter on) goes in both.
• Hits return the matched line and the metadata, not the whole text. Keep meta small and useful as a summary, and fetch the full document with GET .../docs/{id} when you need it.
• For imports, text=subject,body picks the columns that become the searchable text; every column is metadata either way.

#Shaping metadata

Filters and SQL work on top-level keys with scalar values: strings, numbers and booleans.

#Flat beats nested

{"user_id": "u-17", "user_plan": "pro", "status": "open"}

{"user": {"id": "u-17", "plan": "pro"}, "status": "open"}

The nested form can't be filtered by plan (a filter on user takes operators, not keys), and in SQL user is one text column holding {"id":"u-17","plan":"pro"}.

#Arrays don't match by element

{"tags": ["billing", "urgent"]} doesn't pass the filter {"tags": "urgent"}: an array equals only the identical array. For tags you filter on, use a boolean per tag; for many free-form tags, also put them in text with a marker you search for.

{"tag_billing": true, "tag_urgent": true}

{"tags": ["billing", "urgent"]}

Then filter with {"tag_urgent": true}, or search the text for [tag:urgent].

#Times as integer seconds

Store times as integer seconds since 1970: "ts": 1760572800. Numeric ranges work in filters ({"ts": {"gte": 1760572800}}) and SQL buckets them (DATE_TRUNC('hour', ts)).

An ISO string ("2026-10-16T10:00:00Z") is text. It sorts and compares as text in SQL, but filter ranges need numbers (gte takes a number) and DATE_TRUNC refuses it. Imported CSV columns of YYYY-MM-DD HH:MM:SS are different: they become a real time column.

#One type per key

If priority is 3 in one document and "high" in another, SQL reads it as text, and priority > 2 becomes an error (text compared with a number). Pick one type per key and keep it everywhere. Booleans are 1 / 0 in SQL.

#Missing is NULL

Leave a key out (or send null in a PATCH, which removes it) rather than writing 0 or "" as a placeholder. Then COUNT(x), AVG(x) and IS NULL mean what you'd expect.

#Normalise what you filter on

Numbers compare as numbers; strings compare exactly ("Open" isn't "open"). Normalise the values you filter on when you write them. id and keys starting with # are reserved.

#One dataset, three shapes

Support tickets, written three ways.

1. Everything in text. Searchable, but nothing to filter or group by:

{"id": "t-1042", "text": "status: open\npriority: high\ncustomer: Acme\nRefund requested for order ACME-1042"}

2. Nested and stringly typed. Filterable only partly, and SQL can't do arithmetic on it:

{"id": "t-1042", "text": "Refund requested for order ACME-1042",
 "meta": {"ticket": {"status": "open", "priority": "high"}, "opened": "2026-10-16T10:00:00Z", "tags": ["billing"]}}

3. Shaped for SkimmerDB. The words in text, flat typed metadata, a natural id:

{"id": "t-1042", "text": "Refund requested for order ACME-1042",
 "meta": {"status": "open", "priority": 3, "customer": "acme", "opened_ts": 1760608800, "tag_billing": true}}

With the third shape, every kind of read works:

B=https://api.skimmerdb.com/v1
# find it by text, narrowed by metadata
curl -s $B/namespaces/support/search -H "Authorization: Bearer $KEY" \
  -d '{"q": "acme-1042", "filter": {"status": "open", "priority": {"gte": 2}}}'
# count open billing tickets
curl -s $B/namespaces/support/count -H "Authorization: Bearer $KEY" \
  -d '{"q": "", "filter": {"status": "open", "tag_billing": true}}'
# group by customer and day
curl -s $B/namespaces/support/sql -H "Authorization: Bearer $KEY" \
  -d "{\"sql\": \"SELECT customer, DATE_TRUNC('day', opened_ts) AS day, COUNT(*) AS n FROM docs WHERE status = 'open' GROUP BY customer, day ORDER BY day\"}"

#Next

• Writing documents: batches, updates, deletes and retries.
• Search and filters and the filter reference.
• Analytics with SQL.