# Secondary indexes and unique fields

Available in the Rust development checkout. The archived 0.1.0-alpha binaries and source predate indexing. A fresh default instance created by `ratdb init` uses storage v3. For a named file use `ratdb init --indexed users.ratdb`, then `ratdb setup users.ratdb admin` and `ratdb serve users.ratdb`. Plain explicit-path init retains its v1 format for compatibility.

## Shell and client interfaces

```text
use website/users
create unique index username
insert user:001 {"username":"jason"}
insert user:002 {"username":"jason"}
show indexes
explain find username=jason
find username=jason
create index active
drop index active
```

The second insert returns “unique index violation” and leaves user:001 intact. Existing duplicates also reject creation of a unique index. To change an index's uniqueness, drop it and create it again. Creation and deletion each hold the server's database mutex until a complete definition and all its entries can be published together. Other sessions can stay connected. Large builds can delay requests or outlast a client deadline. After a lost reply, reconnect and inspect the definition and documents before retrying.

The authenticated protocol adds `create_index {field, unique}`, `drop_index {field}`, `indexes` and `explain_find {field, value}` to the selected den. Rust network den handles expose corresponding methods. Python sessions expose `create_index(field, unique=True)`, `drop_index(field)`, `indexes()` and `explain_find(field, value)`. The existing find and filtered page calls use the index automatically. Duplicate values are recoverable operation_failed responses and embedded callers receive Error::Unique. Other callers can still write ordinary documents without knowing about a definition.

Embedded Rust callers can use Documents::create_indexed for a new host file or wrap Database::create_indexed over their own Storage. On a borrowed den use create_index, indexes, drop_index and find. Documents::explain reports primary_id, secondary_index or bounded_scan. Ordinary find returns up to 100 records and reports truncation. For more, call page with the same equality filter and the returned exclusive ID cursor until next_after is null. Unindexed pages examine at most 10,000 documents per call. A page reads the current generation and later pages can observe intervening writes. Results follow bytewise document ID order.

## Values and constraints

Definitions belong to exactly one warren and den. One definition per top-level field and at most eight per den. Field names are case-sensitive UTF-8, 1–128 bytes, without NUL. Dots are literal field-name characters. ID already has a primary lookup. Choose scalar fields whose encoded keys fit the configured key limit, 1,024 bytes by default, including the namespace, field, type, value and any non-unique document ID suffix.

| JSON value | Equality and uniqueness |
|---|---|
| Missing or null | Both omit the entry. Multiple documents may have either. Finding null uses a bounded scan and matches explicit null only. |
| String | Exact UTF-8 bytes, with a length prefix. Case, normalization and accents are significant. Numeric-looking strings remain strings. |
| Boolean | Separate type tag for booleans. |
| Number | Unsigned integers and negative integers retain exact integer representations. Floating-point values use finite IEEE-754 binary64 with both zero signs canonicalized. JSON integer 1 and float 1.0 are distinct, consistent with RatDB's serde_json equality. |
| Array or object | Creating an index over such a field rejects the build. A subsequent write assigning one to an indexed field rejects the whole write set. Other fields remain schemaless. |

Every document mutation and its posting changes share one private storage root. The writer removes every previous claim in a batch before adding the final claims. This permits two documents to exchange values. Duplicate final claims, invalid values and capacity errors discard the logical transaction. Inserts, replacements, partial updates, deletes, and cross-den transactions share this path. Deleting a document releases its claims. Dropping a definition keeps the documents and explicit den. A failed build can reserve file space. Its unpublished tail can be reused.

## Format, migration and recovery

V1 still uses two fixed banks. PG2 remains a separate fixed-key experiment. V3 stores variable-length keys in an ordered, compressed binary radix tree under the RATDB003 format signature. Bytes map to nine-bit symbols 1 through 256, with zero as the terminator. A branch records the first differing bit and the bytewise separator. Leaf payloads, nodes, the format header and the two root records have CRC-32 protection. Index definitions occupy the I3 namespace and postings the K3 namespace. Unique postings map a typed value to one owner ID. Non-unique posting keys also include the document ID, which provides deterministic streaming pagination.

A writer appends changed leaves and copies only their ancestor branches. It syncs those bytes, publishes and syncs the first root record, then publishes and syncs its mirror. Reopen reads the newest valid root and verifies the reachable tree, key order, branch separators, sizes and checksums. An incomplete mirror is overwritten first on the next publication. Conflicting root histories or damaged reachable data fail the open. A failure during publication poisons the handle. Reopen and reconcile the result. Normal host document opens and server startup also audit every expected posting and every stored posting against its owner. The explicit check command runs this audit. Corruption stops the operation. Preserve a damaged file for investigation and restore a checked backup.

V3 grows in 256 KiB extents up to a 16 GiB logical file ceiling. It requires exclusive ownership, exact reads/writes, durable ordered flushes and zero-filled growth preserving previous bytes. It uses no host API or worker thread in the portable core. Historical nodes accumulate, including old values. Ordinary deletion is not secure erasure. Ordinary queries read a tree path, a bounded set of matching IDs, and the returned documents. Worst-case depth depends on key length and key distribution.

Stop the server before migration. Run `ratdb migrate-indexed OLD NEW` using a fresh destination on a filesystem supporting hard links. The command checks the source, creates a private destination, opens and audits it, compares every key/value and publishes without overwriting an existing path. It leaves the source available for rollback. Set credentials for NEW separately. Discard any previously cached instance generation after migrating. The archived alpha refuses v3. Rolling back to OLD restores its pre-migration state; reconcile any later application writes before switching.

The same command on a v3 source compacts the reachable tree into a fresh file. A server online backup also copies reachable data and preserves the generation. Raw offline backup and restore copy the whole file, including historical bytes. No automatic background reclamation runs. Reserve enough free space for growth and a separate verified copy. A killed migration can leave an unpublished sibling staging file. Inspect before removing stale staging files with the server stopped.

[Measurements and verification](INDEX_ENGINEERING_REPORT.md)