{"content":"# aux4/repository\n\nSimplified aux4 database\n\naux4/repository provides a tiny JSON repository backed by SQLite. It exposes a small set of commands (write, read, find, delete, truncate, clean) that let you store arbitrary JSON records keyed by id, query them using SQL-like expressions, optionally expire records with a time-to-live, restrict them with access labels, and manage repository tables. It's designed to be simple to use from shell scripts and other aux4 packages, and to work with stdin-based workflows (pipe JSON into write) or explicit --data flags.\n\nThis package is a building block in the aux4 ecosystem for storing and querying structured JSON data without running a separate database server. Typical use cases include quick test fixtures, local caches, small data imports/exports, and lightweight persistence for CLI tools. It ships as a single self-contained binary with no external tools required, and can store data either in a local SQLite file (the default) or in a remote Turso/libSQL database — see Storage backends.\n\n## Installation\n\n``bash\naux4 aux4 pkger install aux4/repository\n`\n\n## Quick Start\n\nWrite a record and read it back. This uses the default database (.local.db) and the repository name \"users\".\n\n`bash\naux4 repository write users --id user1 --data '{\"name\":\"John\",\"age\":30}' --metadata '{\"role\":\"admin\"}'\n`\n\nThis prints the id of the stored record (here: user1). Then read the repository:\n\n`bash\naux4 repository read users\n`\n\nThis returns a JSON array of stored records ({ \"id\": ..., ...fields }). Add --raw true to also include a __metadata object with the stored metadata plus createdAt/updatedAt timestamps.\n\n## Storage backends (--db)\n\nEvery command accepts --db, which selects the storage backend by its scheme:\n\n- **Local (default)** — a file path (or file: URL) uses an embedded, file-backed SQLite database. The default is .local.db in the current directory.\n\n `bash\n aux4 repository write users --id u1 --data '{\"name\":\"John\"}'\n aux4 repository read users --db /var/data/app.db\n `\n\n- **Remote (Turso/libSQL)** — a libsql://, wss://, ws://, https://, or http:// URL connects to a remote Turso/libSQL database over the network. The command surface, output, and behavior are identical to the local backend.\n\n `bash\n export TURSO_AUTH_TOKEN=\"<your-token>\"\n aux4 repository write users --db libsql://my-db.turso.io --id u1 --data '{\"name\":\"John\"}'\n aux4 repository read users --db libsql://my-db.turso.io\n `\n\n**Authentication.** For remote databases, the auth token is read from the TURSO_AUTH_TOKEN environment variable and applied automatically (unless the URL already includes an authToken query parameter). No token is required for a local file or an unauthenticated server.\n\nThe package is a single statically linked binary — it needs no sqlite3, jq, sed, or any other external tool installed, on either backend.\n\n## Storing data (write)\n\nThe write command stores a JSON document in the named repository table. You can provide the data via --data or pipe JSON to stdin. If --id is omitted, the command will use an id field inside the JSON (if present) or generate a UUID.\n\nThe key variables:\n- db (default: .local.db) — local database file path or a remote libsql:// URL (see [Storage backends](#storage-backends---db))\n- repository — repository/table name (positional)\n- id — optional id to store (overrides id in JSON)\n- data — JSON string to store (optional if providing via stdin)\n- metadata — optional JSON metadata (defaults to {})\n- ttl — optional time-to-live in seconds (see [Expiring records](#expiring-records-with-a-time-to-live-ttl))\n- access — optional comma-separated access labels (see [Restricting data with access labels](#restricting-data-with-access-labels))\n\nExample using explicit id, data, and metadata:\n\n`bash\naux4 repository write users --id 123 --data '{\"name\":\"John\"}' --metadata '{\"role\":\"admin\"}'\n`\n\nFor full command docs see [aux4 repository write](./commands/repository/write).\n\n## Reading data (read)\n\nThe read command returns stored records as JSON. By default it reads all rows; use --id to select a single record. Each returned object contains an id and the fields from the stored JSON. With --raw true, each object also gains a metadata object that merges the stored metadata with createdAt and updatedAt timestamps.\n\n**Table vs. JSON output.** On an interactive terminal, read and find render their results as a table for readability. When the output is piped, redirected, or captured by another aux4 command it is emitted as **compact single-line JSON** (the whole array on one line, like jq -c) — so scripts and pipelines always receive JSON. Number literals (e.g. 45.50) and key order are preserved. Pass --render none to force compact JSON even on a terminal.\n\n`bash\n# table on a terminal, raw JSON when piped into jq\naux4 repository read users\naux4 repository read users | jq '.[].name'\n\n# force raw JSON on a terminal\naux4 repository find users --expr \"age > 30\" --render none\n`\n\nExample — write two records then read all:\n\n`bash\naux4 repository write users --id user1 --data '{\"name\":\"John\",\"age\":30}' --metadata '{\"role\":\"admin\"}'\naux4 repository write users --id user2 --data '{\"name\":\"Jane\",\"age\":25}' --metadata '{\"role\":\"user\"}'\naux4 repository read users --raw true\n`\n\nExample expected output (excerpt):\n\n`json\n[{\"id\":\"user1\",\"name\":\"John\",\"age\":30,\"__metadata\":{\"role\":\"admin\",\"createdAt\":\"2026-07-21 08:31:31\",\"updatedAt\":\"2026-07-21 08:31:31\"}},{\"id\":\"user2\",\"name\":\"Jane\",\"age\":25,\"__metadata\":{\"role\":\"user\",\"createdAt\":\"2026-07-21 08:31:31\",\"updatedAt\":\"2026-07-21 08:31:31\"}}]\n`\n\nFor full command docs see [aux4 repository read](./commands/repository/read).\n\n## Searching with SQL expressions (find)\n\nfind lets you filter records using SQL expressions that are translated to JSON field access. The expr parameter accepts expressions like \"age > 30 and name like '%John%'\" and supports numeric comparisons, LIKE, logical operators, JSON path extraction, and parentheses for grouping.\n\nExample — find by name equality:\n\n`bash\naux4 repository find users --expr \"name = 'John'\" --raw true\n`\n\nExample expected output (excerpt):\n\n`json\n[{\"id\":\"user1\",\"name\":\"John\",\"age\":30,\"city\":\"NYC\",\"__metadata\":{\"role\":\"admin\",\"createdAt\":\"2026-07-21 08:31:31\",\"updatedAt\":\"2026-07-21 08:31:31\"}}]\n`\n\nFor full command docs see [aux4 repository find](./commands/repository/find).\n\n## Ordering and pagination\n\nBoth read and find accept --sort, --limit, and --offset to order results and page through them.\n\n--sort is a comma-separated list of field [asc|desc] clauses. The direction is **space-separated** and asc is the default when omitted; later clauses break ties left by earlier ones:\n\n`bash\n# order by age descending, then name ascending\naux4 repository read users --sort \"age desc, name\"\n`\n\n- **Data fields** sort by their JSON value and support dotted nested paths: --sort \"specs.rank desc\".\n- The **special fields** id, createdAt, and updatedAt sort by the record's id and its creation/update timestamps: --sort \"createdAt desc\".\n- Field names are validated (letters, digits, underscore, dot-separated segments) and directions are limited to asc/desc; an invalid field or direction is rejected with an error.\n\n--limit N caps the number of returned records and --offset N skips the first N — both non-negative integers. --offset may be used without --limit:\n\n`bash\n# newest 10 sessions, most recent first\naux4 repository read sessions --sort \"createdAt desc\" --limit 10\n\n# page 2 of 20-per-page users ordered by name\naux4 repository read users --sort \"name\" --limit 20 --offset 20\n\n# ten cheapest in-stock products matching an expression\naux4 repository find products --expr \"inStock = 1\" --sort \"price\" --limit 10\n`\n\nOrdering and pagination apply on top of --expr, --access, and ttl visibility — the access/ttl filter runs first, then results are ordered and paged.\n\n## Selecting fields (projection)\n\nBoth read and find accept --select to return only a subset of fields, in the order listed:\n\n`bash\naux4 repository read users --select \"first_name, last_name, age\"\n`\n\n- **id is always included first**, then the selected data fields in the requested order (--select \"age, name\" puts age before name). Listing id explicitly does not duplicate it.\n- **Absent fields are omitted** — this is a schema-less store, so if a document lacks a selected field it is simply left out of that row (never emitted as null).\n- **Dotted nested paths** are supported: --select \"address.city\" navigates into the nested object and returns the value keyed by the path as written ({\"id\":...,\"address.city\":\"NYC\"}).\n- Field names are validated (letters, digits, underscore, dot-separated segments); an invalid name is rejected with an error.\n- Empty --select returns all fields (the default).\n\nProjection is applied **in the tool after reading**, never in SQL — no field name is ever placed into a query, so --select has no injection surface. It composes with --expr, --sort, --limit/--offset, and --access; with --raw, the metadata object is appended after the selected fields.\n\n`bash\n# project two fields from a filtered, sorted, paged result\naux4 repository find users --expr \"age > 21\" --select \"first_name, age\" --sort \"age desc\" --limit 10\n`\n\n## Counting records (count)\n\ncount returns how many records match — without returning the records themselves. It accepts the same --expr, --access, --all, and --includeExpired as find, and applies the same access/ttl visibility. The output is a bare integer on one line.\n\n`bash\n# total visible records\naux4 repository count users\n\n# how many match a filter (including access-restricted records)\naux4 repository count users --expr \"age >= 18\" --all true\n`\n\nA count of 0 is a valid answer and exits 0 — unlike read/find, count does not use the empty-result exit-4 convention. There is no --sort/--select/--limit/--offset (a count is always the total of the matching set).\n\n## Updating records in place (patch and touch)\n\nwrite replaces a whole record (and creates it if missing). When you want to amend an existing record instead, use patch (data) or touch (ttl/access). Both are **update-only** — they error if the id does not exist — and both print the id on success.\n\n**patch** shallow-merges a JSON payload into the record's data: listed keys set/override (existing keys keep their position, new keys are appended in payload order), unmentioned keys are preserved, and any id in the payload is ignored. metadata and created_at are untouched; updated_at is bumped.\n\n`bash\n# add/override only these fields; everything else in the record is preserved\naux4 repository patch users --id u1 --data '{\"age\":31,\"city\":\"NYC\"}'\n\n# payload can come from stdin\necho '{\"verified\":true}' | aux4 repository patch users --id u1\n`\n\n**Note:** patch is a shallow top-level merge — nested objects are replaced, not deep-merged — and it cannot delete a field. These are intentional limitations of this version.\n\n**touch** updates only metadata without changing data: --ttl <seconds> sets a new expiry epoch (now + seconds) and --access \"a,b\" replaces the access labels. At least one of --ttl/--access must be given.\n\n`bash\n# extend a session's ttl\naux4 repository touch sessions --id s1 --ttl 3600\n\n# re-scope a document to new access labels\naux4 repository touch docs --id doc1 --access \"team-a,team-c\"\n`\n\n**Note:** touch only *sets* ttl/access; it cannot clear them (making a record permanent or public again is out of scope for this version).\n\n## Expiring records with a time-to-live (TTL)\n\nRecords can be given an expiry using the --ttl <seconds> flag on write. The value is a duration in seconds; it is converted to an absolute expiry epoch using the database clock and merged into the record metadata under the ttl key. Any user-supplied --metadata is preserved:\n\n`bash\naux4 repository write sessions --id s1 --data '{\"token\":\"abc\"}' --metadata '{\"role\":\"admin\"}' --ttl 3600\n`\n\nThis stores metadata like {\"role\":\"admin\",\"ttl\":1783724578}. Records with no ttl never expire.\n\nExpired records (whose ttl is in the past) are **hidden by default** from both read and find — including reads by --id. Records with no ttl are always returned. To include expired records (for debugging or manual sweeps), pass --includeExpired true:\n\n`bash\n# expired records are hidden\naux4 repository read sessions\n\n# expired records are shown\naux4 repository read sessions --includeExpired true\naux4 repository find sessions --expr \"role = 'admin'\" --includeExpired true\n`\n\nHiding is a query-time filter — expired records still occupy storage until they are swept by clean.\n\n**Note:** A negative value written as --ttl -60 (with a space) is interpreted as a flag; use --ttl=-60 to store an already-expired record, or set a past epoch directly via --metadata '{\"ttl\":1}'.\n\nFor full command docs see [aux4 repository write](./commands/repository/write).\n\n## Restricting data with access labels\n\nRecords can be scoped to a set of access labels using the --access <labels> flag on write. The value is a comma-separated string that is split into a JSON array and stored under the access key in the record metadata (alongside any user-supplied --metadata and ttl):\n\n`bash\naux4 repository write docs --id doc1 --data '{\"title\":\"Roadmap\"}' --access \"team-a,team-b\"\n`\n\nThis stores metadata like {\"access\":[\"team-a\",\"team-b\"]}. A record written **without** --access has no access key and is **public** — visible to every caller. A record written **with** --access is **restricted** — visible only to callers that present a matching label.\n\nread and find accept the caller's labels via --access and filter results accordingly:\n\n- Public records (no access key) are always visible, regardless of the caller's labels.\n- A restricted record is visible only when one of its labels matches one of the caller's labels.\n- With no --access, only public records are returned.\n- --all true bypasses access filtering and returns every record — public and restricted.\n\n`bash\n# caller holding label \"team-b\" -> public records + records tagged team-a/team-b\naux4 repository read docs --access team-b\n\n# no --access -> public records only\naux4 repository read docs\n\n# --all true -> every record, public and restricted\naux4 repository read docs --all true\n`\n\nWith find, the access filter is applied **on top of** the --expr filter and cannot be bypassed through the expression:\n\n`bash\n# matches the expression AND is public or tagged \"secret\"\naux4 repository find docs --expr \"team = 'x'\" --access secret\n`\n\nfind enforces this by evaluating the access/visibility predicate in an **inner subquery** and applying --expr only to the already-restricted rowset. An --expr that closes a parenthesis or adds OR 1=1 can therefore only widen selection *within* the rows the caller is already authorized to see. As defense-in-depth, find also **rejects** any --expr containing the sequences ;, --, or /*. The caller's --access labels are safely single-quoted, so a label cannot inject SQL.\n\n**Warning — --expr is a trusted, application-controlled surface.** --expr is raw SQL. The subquery shape keeps restricted rows out of the result, but a caller who controls --expr can still build a **blind-extraction oracle** (e.g. ... AND (SELECT data FROM repo WHERE id='x') LIKE 'S%') to infer restricted content from which authorized rows come back. Access filtering protects the returned rowset, not against arbitrary crafted predicates. **Never pass raw end-user text into --expr when access control matters** — express caller authorization only via --access/--all, never by interpolating user input into --expr.\n\n**Note:** Access restriction is advisory scoping enforced at query time, not a hard security boundary. Anyone with direct access to the underlying database (the local file, or the remote Turso/libSQL database and its token) can read every record regardless of its labels. The enforcing layer is whoever owns the database and controls the --access/--all values passed to read/find.\n\nFor full command docs see [aux4 repository read](./commands/repository/read) and [aux4 repository find](./commands/repository/find).\n\n## Sweeping expired records (clean)\n\nThe clean command removes expired records from **every** repository in a database. It takes no repository argument — it enumerates all user tables and deletes the records whose ttl is in the past, printing a per-repository summary. It is intended to be run periodically (for example from [aux4/cron](/r/public/packages/aux4/cron)) as a maintenance sweep.\n\n`bash\naux4 repository clean\n`\n\n`text\nusers: 1 expired records removed\nsessions: 1 expired records removed\ncache: 0 expired records removed\n`\n\nUse --db to target a specific database:\n\n`bash\naux4 repository clean --db /var/data/app.db\n`\n\nFor full command docs see [aux4 repository clean](./commands/repository/clean).\n\n## Bulk loading (import)\n\nimport reads a **JSON array on stdin** and upserts every element in a **single transaction** — if any element fails, the whole import rolls back and nothing is written. This is both faster and safer than a loop of write calls, especially against a remote database. Each element is treated like a write payload (id from the element's id or a generated UUID; data is the element with id stripped; existing ids are overwritten). It prints one id per line, in input order.\n\n`bash\necho '[{\"id\":\"u1\",\"name\":\"Ann\"},{\"name\":\"Bo\"},{\"id\":\"u3\",\"name\":\"Cy\"}]' | aux4 repository import users\n`\n\n--ttl and --access apply uniformly to every imported record. An empty array writes nothing (exit 0); a non-array or malformed payload is rejected and nothing is written.\n\n`bash\n# import a batch of short-lived, team-scoped records\necho '[{\"id\":\"s1\"},{\"id\":\"s2\"}]' | aux4 repository import sessions --ttl 3600 --access \"team-a\"\n`\n\n## Listing repositories (list)\n\nlist prints the repositories (tables) in a database as a compact JSON array of names, sorted alphabetically. It takes only --db. An empty database prints [] (exit 0).\n\n`bash\naux4 repository list\n`\n\n`json\n[\"accounts\",\"sessions\",\"users\"]\n`\n\n## Deleting, truncating, and dropping\n\n- **delete** removes one or more records by id. Supply multiple --id flags to remove multiple records.\n- **truncate** deletes all records in a repository but keeps the (empty) table, so it can immediately accept new writes. It asks for confirmation unless you pass --yes.\n- **drop** removes an entire repository — table, schema, and data. It also asks for confirmation unless you pass --yes, and is idempotent (dropping a non-existent repository is a harmless no-op). Use truncate to empty a repository, drop to remove it entirely.\n\n`bash\n# remove a single record\naux4 repository delete users --id user1\n\n# empty a repository (schema kept)\naux4 repository truncate users --yes\n\n# remove a repository entirely (schema + data)\naux4 repository drop sessions --yes\n`\n\nFor full command docs see [aux4 repository delete](./commands/repository/delete), [aux4 repository truncate](./commands/repository/truncate), and [aux4 repository drop](./commands/repository/drop).\n\n## Examples\n\n### Write a record with explicit id, data, and metadata\n\nThis example writes a user record and prints the id back.\n\n`bash\naux4 repository write users --id 123 --data '{\"name\":\"John\"}' --metadata '{\"role\":\"admin\"}'\n`\n\nThe command prints:\n\n`\n123\n`\n\n### Write using STDIN (auto-generated UUID)\n\nPipe JSON to the write command without providing --id; the command will generate a UUID when none is provided.\n\n`bash\necho '{\"product\":\"Widget\",\"price\":29.99,\"inStock\":true}' | aux4 repository write inventory\n`\n\nThis prints a generated UUID (v4). Tests expect a UUID matching the pattern:\n\n`\n^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$\n`\n\n### Read complex nested JSON\n\nWrite a record with nested JSON and read it back. The nested structure is preserved.\n\n`bash\naux4 repository write orders --id order1 --data '{\"customer\":{\"name\":\"Alice\",\"email\":\"alice@example.com\"},\"items\":[{\"id\":\"item1\",\"qty\":2},{\"id\":\"item2\",\"qty\":1}],\"total\":29.98}' --metadata '{\"status\":\"pending\",\"priority\":\"high\"}'\naux4 repository read orders --id order1 --raw true\n`\n\nExpected output (excerpt):\n\n`json\n[{\"id\":\"order1\",\"customer\":{\"name\":\"Alice\",\"email\":\"alice@example.com\"},\"items\":[{\"id\":\"item1\",\"qty\":2},{\"id\":\"item2\",\"qty\":1}],\"total\":29.98,\"__metadata\":{\"status\":\"pending\",\"priority\":\"high\",\"createdAt\":\"2026-07-21 08:31:31\",\"updatedAt\":\"2026-07-21 08:31:31\"}}]\n`\n\n### Find with numeric comparison\n\nWrite several records, then find those matching a numeric condition.\n\n`bash\naux4 repository write users --id user1 --data '{\"name\":\"John\",\"age\":30,\"city\":\"NYC\"}' --metadata '{\"role\":\"admin\"}'\naux4 repository write users --id user2 --data '{\"name\":\"Jane\",\"age\":25,\"city\":\"LA\"}' --metadata '{\"role\":\"user\"}'\naux4 repository write users --id user3 --data '{\"name\":\"Bob\",\"age\":35,\"city\":\"NYC\"}' --metadata '{\"role\":\"user\"}'\naux4 repository find users --expr \"age > 28\" --raw true\n`\n\nExpected output (excerpt):\n\n`json\n[{\"id\":\"user1\",\"name\":\"John\",\"age\":30,\"city\":\"NYC\",\"__metadata\":{\"role\":\"admin\",\"createdAt\":\"2026-07-21 08:31:31\",\"updatedAt\":\"2026-07-21 08:31:31\"}},{\"id\":\"user3\",\"name\":\"Bob\",\"age\":35,\"city\":\"NYC\",\"__metadata\":{\"role\":\"user\",\"createdAt\":\"2026-07-21 08:31:31\",\"updatedAt\":\"2026-07-21 08:31:31\"}}]\n`\n\n### Delete multiple records and verify\n\nWrite multiple records, delete several using multiple --id flags, and read to confirm remaining records.\n\n`bash\naux4 repository write products --id prod1 --data '{\"name\":\"Widget\",\"price\":19.99}' --metadata '{\"category\":\"electronics\"}'\naux4 repository write products --id prod2 --data '{\"name\":\"Gadget\",\"price\":29.99}' --metadata '{\"category\":\"electronics\"}'\naux4 repository write products --id prod3 --data '{\"name\":\"Tool\",\"price\":39.99}' --metadata '{\"category\":\"hardware\"}'\naux4 repository write products --id prod4 --data '{\"name\":\"Device\",\"price\":49.99}' --metadata '{\"category\":\"electronics\"}'\naux4 repository delete products --id prod1 --id prod3 --id prod4\naux4 repository read products --raw true\n`\n\nExpected output (excerpt):\n\n`json\n[{\"id\":\"prod2\",\"name\":\"Gadget\",\"price\":29.99,\"__metadata\":{\"category\":\"electronics\",\"createdAt\":\"2026-07-21 08:31:31\",\"updatedAt\":\"2026-07-21 08:31:31\"}}]\n``\n\n## Configuration\n\nThis package does not require external configuration files. All runtime options are provided via command arguments and the default database file (.local.db) can be overridden with --db.\n\n## License\n\nThis package is licensed under the Apache License 2.0.\n\nSee LICENSE for details.\n"}