Citadel

Citadel-only (no direct SQLite equivalent)

Fixed-parameter reads; every benchmark exceptjson_tableis served from the result cache on repeat execution.

Benchmark Citadel ------------------------------- json_table 9.25 ms lateral 1.46 us date_sort 1.10 us date_extract 473 ns date_groupby 242 ns date_range_scan 102 ns date_arith 100 ns

Rotating probes; both arms measure execution speed.

Benchmark Without index With index Speedup --------------------------------------------------------------- json_gin 4.70 ms 3.49 us 1,347x fts_index 1.37 s 2.98 ms 461x

- correlated_in-SELECT COUNT() FROM t WHERE id IN (SELECT id FROM ref_table WHERE ref_table.val = t.age)
- full_outer_join-SELECT a.id, b.data FROM a FULL OUTER JOIN b ON a.id = b.a_id
- count-SELECT COUNT() FROM t
- correlated_scalar-SELECT a.id, (SELECT COUNT() FROM b WHERE b.a_id = a.id) FROM a
- point-SELECT FROM t WHERE id = 50000
- group_by-SELECT age, COUNT() FROM t GROUP BY age
- partial_index_point-SELECT FROM t WHERE email = ? AND deleted_at IS NULL
- cte-WITH filtered AS (SELECT ... WHERE age < 50) SELECT age, COUNT() FROM filtered GROUP BY age
- view_point-SELECT FROM v WHERE id = 50000
- truncate-TRUNCATE TABLE t
- insert_returning-INSERT INTO t (id, val) VALUES (...) RETURNING id, val
- upsert_returning-INSERT ... ON CONFLICT (id) DO UPDATE SET c = c + 1 RETURNING c
- view_filter-SELECT FROM v WHERE age = 42
- filter-SELECT FROM t WHERE age = 42
- window_agg-SELECT SUM(age) OVER (ORDER BY id ROWS 50 PRECEDING) FROM t
- jsonb_contains-SELECT id FROM users WHERE data @> '{"role":"admin"}'::jsonb
- savepoint_create-BEGIN; SAVEPOINT sp; RELEASE sp; COMMIT
- sort-SELECT FROM t ORDER BY age LIMIT 10
- upsert_counter-INSERT ... ON CONFLICT (id) DO UPDATE SET c = c + 1
- window_rank-SELECT ROW_NUMBER() OVER (PARTITION BY age ORDER BY id) FROM t
- delete_returning-DELETE ... WHERE id = ? RETURNING id, val
- upsert_dedup-INSERT ... ON CONFLICT (id) DO NOTHING
- json_extract-SELECT data ->> 'name' FROM users
- delete-DELETE FROM t WHERE id = ?
- update-UPDATE t SET age = age + 1 WHERE id BETWEEN 10000 AND 10099
- covered_range-SELECT age, id FROM t WHERE age = ?on an indexed column, parameter rotating per iteration
- covered_count-SELECT COUNT() FROM t WHERE age >= ?on an indexed column, parameter rotating per iteration
- sort_paginate_pk-SELECT id, name FROM t WHERE id > ? ORDER BY id LIMIT 20, parameter advancing per iteration
- join_param-SELECT a.val, b.data FROM a JOIN b ON b.a_id = a.id WHERE a.id = ?, parameter rotating per iteration
- correlated_exists-SELECT COUNT() FROM t WHERE EXISTS (SELECT 1 FROM ref_table WHERE ref_table.id = t.id)
- savepoint_nested-BEGIN; SAVEPOINT sp1; ... ; RELEASE/ROLLBACK TO sp1; COMMIT
- with_dml-WITH d AS (DELETE FROM src RETURNING ) INSERT INTO archive SELECT FROM d
- distinct-SELECT DISTINCT age FROM t
- insert_select-INSERT INTO sink SELECT id, val FROM a
- savepoint_rollback-BEGIN; INSERT 1K rows; SAVEPOINT sp; INSERT 10K rows; ROLLBACK TO sp; COMMIT
- update_returning-UPDATE t SET c = c + ? WHERE id = ? RETURNING c
- insert-INSERT INTO t (id, val) VALUES (?, ?)
- scan-SELECT FROM t
- wide_proj_pk-SELECT id FROM wide(24-column table: 3 INT keys, 8 INT, 12 TEXT; 10K rows)
- wide_proj_2col-SELECT id, k1 FROM wide
- wide_proj_3col-SELECT id, k1, t1 FROM wide
- wide_proj_full-SELECT FROM wide
- sort_nocase-SELECT name FROM t ORDER BY name COLLATE NOCASE LIMIT 10
- sum-SELECT SUM(age) FROM t
- insert_gen_virtual-INSERT INTO t (id, a, b) VALUES (?, ?, ?)
- union-SELECT id, val FROM a UNION ALL SELECT id, data FROM b
- select_gen_virtual-SELECT id, s FROM t WHERE s > ?
- update_gen_propagate-UPDATE t SET a = a + ? WHERE id = ?
- upsert_mixed-INSERT ... ON CONFLICT (id) DO UPDATE SET c = c + 1
- upsert_all_new-INSERT ... ON CONFLICT (id) DO NOTHING
- recursive_cte-WITH RECURSIVE seq(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM seq WHERE x < 1000) SELECT SUM(x) FROM seq
- insert_gen_stored-INSERT INTO t (id, a, b) VALUES (?, ?, ?)
- fk_cascade-DELETE FROM parent WHERE id = ?
- fk_cascade_delete_only-DELETE FROM parent WHERE id = ?(no index on child)
- join-SELECT a.id, b.data FROM a INNER JOIN b ON a.id = b.a_id
- fts_match-SELECT id FROM docs WHERE body @@ to_tsquery('rust & database')
- fts_phrase-SELECT id FROM docs WHERE body @@ phraseto_tsquery('rust database')
- fts_rank-SELECT id, ts_rank(body, to_tsquery('rust & database')) FROM docs WHERE body @@ ... ORDER BY r DESC LIMIT 10

- date_extract-SELECT AVG(EXTRACT(HOUR FROM ts)) FROM events
- date_groupby-SELECT DATE_TRUNC('month', ts), COUNT() FROM events GROUP BY 1
- json_table-SELECT a, b, c FROM JSON_TABLE(j, '$](https://github.com/yp3y5akh0v/citadel/tree/HEAD/crates/citadel-mem)[]' COLUMNS (a INT PATH '$.a', b TEXT PATH '$.b', c INT PATH '$.c'))
- lateral-SELECT c.id, p.name FROM c, LATERAL (SELECT name FROM p WHERE p.cat_id = c.id ORDER BY price DESC LIMIT 1) p
- date_range_scan-SELECT COUNT() FROM events WHERE d BETWEEN DATE '2024-02-01' AND DATE '2024-03-31'
- date_arith-SELECT COUNT() FROM events WHERE ts + INTERVAL '1 day' > TIMESTAMP '2024-06-01 00:00:00'
- date_sort-SELECT id FROM events ORDER BY ts LIMIT 100

Clauses- JOINs (INNER, LEFT, RIGHT, CROSS, FULL OUTER, LATERAL), subqueries (scalar, IN, EXISTS, correlated), CTEs (WITH/WITH RECURSIVE/ WITH-DML:WITH x AS (INSERT/UPDATE/DELETE ... [RETURNING ]) SELECT ...), UNION/INTERSECT/EXCEPT [ALL], CASE, BETWEEN, LIKE, DISTINCT,ANY/ALL(subquery + array forms), GROUP BY/HAVING, ORDER BY, LIMIT/OFFSET

Window functions- ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, SUM/COUNT/AVG/MIN/MAX OVER with PARTITION BY, ORDER BY, ROWS/RANGE frames

Views- CREATE/DROP VIEW, OR REPLACE, IF NOT EXISTS/IF EXISTS, column aliases, nested views

Materialized views-CREATE MATERIALIZED VIEW [IF NOT EXISTS] name AS SELECT ...,REFRESH MATERIALIZED VIEW [CONCURRENTLY] name(CONCURRENTLYdoes a diff-merge - DELETE removed rows, UPDATE changed rows, INSERT new rows - instead of TRUNCATE+repopulate),DROP MATERIALIZED VIEW [CASCADE], full backing-table semantics (indexes, joins, planner sees a real table),pg_matviewsintrospection

Triggers-CREATE TRIGGER name {BEFORE|AFTER|INSTEAD OF} {INSERT|UPDATE [OF cols]|DELETE} ON table FOR EACH {ROW|STATEMENT} [REFERENCING NEW TABLE AS new_t OLD TABLE AS old_t] [WHEN (expr)] BEGIN ... END. INSTEAD OF triggers make views writable. Transition tables work as virtual tables in trigger bodies.ALTER TABLE ... DISABLE/ENABLE TRIGGER [name|ALL]. PG-faithful name-order firing. Introspection viainformation_schema.triggersandSHOW TRIGGERS [ON table].

TEMP tables-CREATE TEMP TABLE ...lives in a per-connection in-memory database, dropped on disconnect. Full DDL/DML/index/constraint/trigger parity with persistent tables.

Functions- COUNT, SUM, AVG, MIN, MAX, LENGTH, UPPER, LOWER, SUBSTR/SUBSTRING, TRIM/LTRIM/RTRIM, REPLACE, INSTR, CONCAT, HEX, ABS, ROUND, CEIL/CEILING, FLOOR, SIGN, SQRT, RANDOM, COALESCE, NULLIF, CAST, TYPEOF, IIF

Date/Time Functions- NOW, CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, LOCALTIMESTAMP, LOCALTIME, CLOCK_TIMESTAMP, EXTRACT, DATE_PART, DATE_TRUNC, DATE_BIN, AGE, MAKE_DATE, MAKE_TIME, MAKE_TIMESTAMP, MAKE_INTERVAL, JUSTIFY_DAYS, JUSTIFY_HOURS, JUSTIFY_INTERVAL, ISFINITE, DATE, TIME, DATETIME, STRFTIME, JULIANDAY, UNIXEPOCH, TIMEDIFF, AT TIME ZONE. SupportsINTERVAL '1 year 2 months',DATE '2024-01-15',TIMESTAMP '2024-01-15 12:30:00Z',infinity/-infinitysentinels, BC dates, full IANA zone parsing (jiff), PG-normalized INTERVAL comparison.

Full-text search-tsvector/tsquerytypes,to_tsvector/to_tsquery/plainto_tsquery/phraseto_tsquery/websearch_to_tsquerybuilders,@@match operator,ts_rank/ts_rank_cdranking with weighted positions (A/B/C/D), prefix matching (term:), phrase distance (<N>), inverted indexes viaCREATE INDEX ... USING ftsfor ~461x speedup over sequential scan

UPSERT-INSERT ... ON CONFLICT (cols) DO NOTHING/DO UPDATE SET col = excluded.col ... WHERE ...andON CONFLICT ON CONSTRAINT idx_name.excluded.refers to the proposed row; barecolrefers to the existing row. Single-descent storage primitive: on the canonicalDO UPDATE SET counter = counter + 1pattern, Citadel is ~1.5x faster than SQLite.

No plaintext on disk.Every page is encrypted before writing and authenticated before reading.

Separate key file.Encryption keys live in{dbname}.citadel-keys, not inside the database. The passphrase derives a master key in memory via Argon2id (or PBKDF2 in FIPS mode) and never touches disk.

Key backup.Export an encrypted key backup with a separate recovery passphrase. Restore access without re-encrypting the entire database.

Instant rekey.Changing the passphrase re-wraps the root encryption key. No page re-encryption - instant regardless of database size.

Encrypted sync.Noise protocol (NNpsk0_25519_ChaChaPoly_BLAKE2s) with a 256-bit pre-shared key. Ephemeral Curve25519 keys per session for forward secrecy.

Agent layer: +---------------------------------------------+ | citadel-ai | Agent runtime (ReAct + Reflexion) +---------------------------------------------+ | citadel-llm | LLM client layer: Claude, OpenAI, Ollama, Gemini +---------------------------------------------+ Memory layer: +---------------------------------------------+ | citadel-mcp | MCP server: memory tools for any MCP client +---------------------------------------------+ | citadel-mem | Memory engine: regions, atoms, recall, erasure +---------------------------------------------+ | citadel-vector | VECTOR(N) type + PRISM filtered ANN index +---------------------------------------------+ Encrypted database engine: +----------------------+----------------------+ | citadel-cli | citadel-python | CLI, Python wheel +----------------------+----------------------+ | citadel-ffi | citadel-wasm | C FFI, WebAssembly +----------------------+----------------------+ | citadel-sql | SQL parser, planner, executor +---------------------------------------------+ | citadel | Database API, builder, sync +-------------+--------------+----------------+ | citadel-txn | citadel-sync | citadel-crypto | Transactions, replication, keys +-------------+--------------+----------------+ | citadel-buffer | citadel-page | Buffer pool (SIEVE), page codec +----------------------------+----------------+ | citadel-io | File I/O, fsync, io_uring +---------------------------------------------+ | citadel-core | Types, errors, constants +---------------------------------------------+

+----------+--------------------+----------+ | IV 16B | Ciphertext 8160B | MAC 32B | +----------+--------------------+----------+

Fresh random IV per page. HMAC verified before decryption.

Shadow paging with a god byte - one byte selects the active commit slot. Atomic commits without WAL:
- Write dirty pages to new locations (CoW)
- Compute Merkle hashes bottom-up
- Update the inactive commit slot
- Flip the god byte

What the at-rest integrity machinery does and does not guarantee against an attacker with file access:

- Per-page HMACbinds(epoch, page_id, IV, ciphertext). Any modification of a page's bytes is detected before decryption. It doesnotbind the commit generation: a page image validly written in the past for the same(page_id, epoch)verifies forever.
- Commit slotsare keyed-MAC'd (truncated HMAC-SHA256 over the whole slot) when the named-table entries fit the authenticated layout; files written by pre-1.13 versions carry only a keyless checksum over part of the slot and are still accepted, so slot authentication is corruption detection and a tampering bar, not a hard guarantee - an attacker can re-encode a slot in the legacy format.
- Rollback to an older genuine state(an earlier file snapshot, or an old slot plus its old pages) passes every check by construction and cannot be detected from the file alone. Deployments that need freshness must keep an external anchor - e.g. record the latest commit'stxn_idand Merkle root outside the attacker's reach and compare after opening.

Static or dynamic library with auto-generatedcitadel.h(cbindgen). All 37 functions are panic-safe.

#include "citadel.h" CitadelDb db = NULL; citadel_create("my.db", (const uint8_t)"secret", 6, NULL, &db); CitadelWriteTxn wtx = NULL; citadel_write_begin(db, &wtx); citadel_write_put(wtx, (const uint8_t)"key", 3, (const uint8_t)"val", 3, NULL); citadel_write_commit(wtx); CitadelSqlConn conn = NULL; citadel_sql_open(db, &conn); CitadelSqlResult result = NULL; citadel_sql_execute(conn, "SELECT  FROM users;", &result); citadel_close(db);

Install withnpm install @citadeldb/wasm.

import { CitadelDb } from "@citadeldb/wasm"; const db = new CitadelDb("secret"); db.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT);"); db.execute("INSERT INTO t (id, name) VALUES (1, 'Alice');"); const result = db.query("SELECT  FROM t;"); // { columns: ["id", "name"], rows: [[1, "Alice"]] } db.put(new Uint8Array([1, 2, 3]), new Uint8Array([4, 5, 6]));

import citadeldb db = citadeldb.connect("my.db", key="secret", create=True) db.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)") db.execute("INSERT INTO t VALUES (1, 'Alice')") db.query("SELECT  FROM t").to_dicts() # [{'id': 1, 'name': 'Alice'}]

git clone https://github.com/yp3y5akh0v/citadel.git cd citadel cargo build --release

Local-first agent memory: a plain-Markdown Obsidian vault is the source of truth, with a rebuildable DuckDB index for hybrid BM25 + vector + graph recall.