The SQLite API layer provides the database connection pool and the CRUD route system used by all structured (non-time-series) data in clarity.
The /exactapi/{entity} URL surface is served entirely by the dynamic relation-discovery layer (API v2) — a runtime router assembled from SQLite PRAGMA-discovered foreign keys. A phased migration moved every table onto this layer; the old compile-time typed CRUD route macros (crud_routes! / nested_route!) and their per-path gate have been removed. The typed model structs and FromRow trait survive (they define row shapes the dynamic core reads), but they no longer register any HTTP routes.
NOTE — large refactor (
1355333removed dead code;7948df9splitwarp_dynamic_routes.rsinto a directory). Since the version documented previously:
- The typed route stack (
crud_routes!/nested_route!macros,DYNAMIC_TABLES/dynamic_path_filter/__dyn_skip__per-path gate, the dormant Axumbuild_dynamic_router) is gone. The dynamic layer is the only CRUD path;create_warp_routescomposes it.api/routes.rsis now a ~58-line macro file that registers models only.api/warp_dynamic_routes.rs(one 3402-line file) is now the directoryapi/warp_dynamic_routes/—mod.rs(route builder + FK-graph/URL machinery),reply.rs(reply/stringify helpers),scope.rs(per-request auth/row-scoping),shims.rs(handler shims).utils/mod.rsshrank; its logic split intoutils/{authscope,bind,filters,inmem,regexeng,sqlemit}.rs(all re-exported fromutils/mod.rs).db/mod.rsshrank to the pool/connection manager; schema creation + migrations + seed moved todb/migrations.rs(run_migrations_internal).- Deleted dead code:
hooks.rs,service_auth.rs,rate_limit.rs(→ rewritten asapi/rate_limiter.rs),db_helpers.rs(→db_blockindb/mod.rs),api/attachments.rs(→api/warp_attachments.rs). A new LoopBack-compatible/exactapi/collectionsAPI landed (see Collections route surface).
Sources:
clarity:backend/src-tauri/src/sqlite_api/db/mod.rsclarity:backend/src-tauri/src/sqlite_api/db/migrations.rsclarity:backend/src-tauri/src/sqlite_api/api/routes.rsclarity:backend/src-tauri/src/sqlite_api/models/mod.rsclarity:backend/src-tauri/src/sqlite_api/introspect.rsclarity:backend/src-tauri/src/sqlite_api/aliases.rsclarity:backend/src-tauri/src/sqlite_api/api/dynamic.rsclarity:backend/src-tauri/src/sqlite_api/api/paths.rsclarity:backend/src-tauri/src/sqlite_api/api/warp_routes.rsclarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rsclarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_bridge.rsclarity:backend/src-tauri/src/sqlite_api/api/collections.rsdb/mod.rsSource: clarity:backend/src-tauri/src/sqlite_api/db/mod.rs
pub struct Db { pub pool: r2d2::Pool<...> }
Db wraps an r2d2 connection pool over rusqlite (with bundled sqlcipher encryption).
Database::new(db_url) (async, with retry logic)db_block(pool, closure): executes a synchronous SQLite operation on a blocking thread via tokio::task::spawn_blockingclarity:backend/src-tauri/src/main.rs:1984-2012
Database carries three lazily-populated tokio::sync::RwLock caches used by the dynamic layer:
| Field | Type | Populated by |
|---|---|---|
schema_cache |
HashMap<String, Vec<String>> |
per-table column lists (N+1 PRAGMA fix) |
discovered_tables |
HashMap<String, DiscoveredTable> |
introspect::discover_tables |
relation_map |
Option<RelationMap> |
introspect::build_relation_map |
The schema_cache is invalidated per-table after a runtime ALTER TABLE ADD COLUMN (used by tag_mappings when a write introduces a new field) via Database::invalidate_schema_cache; the other two caches assume a stable relation graph and are not invalidated at runtime. clarity:backend/src-tauri/src/sqlite_api/db/mod.rs:180, 1471-1473
customerId dual-FK columnsThe 6 org-scoped child tables (sites, units, equipment, clients, ingestconfigs, userprofiles) each gained a customerId INTEGER column that is an alias of orgsId (same FK target orgs(id)). Both columns coexist:
CREATE TABLE statements declare both FKs; idempotent ALTER TABLE … ADD COLUMN customerId INTEGER patches existing on-disk DBs. clarity:backend/src-tauri/src/sqlite_api/db/mod.rs:305-540UPDATE … SET orgsId = COALESCE(orgsId, customerId), customerId = COALESCE(customerId, orgsId) WHERE orgsId IS NULL OR customerId IS NULL), guarded by PRAGMA table_info so a missing column skips rather than fails. clarity:backend/src-tauri/src/sqlite_api/db/mod.rs:430-540pre_bind_hook_dual_org_fk in the dynamic layer (INSERT/UPDATE time). DUAL_ORG_FK_TABLES (dynamic.rs) must stay in sync with DUAL_ORG_FK_MIGRATION_TABLES (db/mod.rs). clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:2737-2760sync.rs ensure_site / ensure_unit now write customerId alongside orgsId (same value). clarity:backend/src-tauri/src/sqlite_api/api/sync.rs:89-145users.username backfillIn addition to the existing name → username rename, a startup defensive backfill populates the users.username column via a COALESCE priority chain (from meta_data.$.name, legacy name, or email) so the dynamic layer can read username as a dedicated column regardless of where the identity previously lived. clarity:backend/src-tauri/src/sqlite_api/db/migrations.rs:640-720
ingestconfigs, tags)New in 9847dba. A startup defensive backfill copies soft-FK values that were stored only in the meta_data JSON back into their real SQL columns, closing the same gap the soft-FK exceptions address. All ingestconfigs FK columns (clientsId, unitsId, siteId, orgsId) and tags.ingestconfigId were added via ALTER TABLE on legacy DBs without a FOREIGN KEY clause, so create_row may have persisted them only in JSON. For each (table, column) (guarded by a PRAGMA table_info presence check), it runs UPDATE {table} SET {col} = CAST(json_extract(meta_data, '$.{col}') AS INTEGER) WHERE {col} IS NULL AND json_extract(...) IS NOT NULL. Errors are logged and non-fatal. clarity:backend/src-tauri/src/sqlite_api/db/mod.rs:562-609
accessAllowed sync (rewritten)New in 9847dba. The startup step that populates every admin userprofiles.accessAllowed array was rewritten. The old form was a correlated UPDATE … GROUP BY o.id subquery, which only produced a single group's worth of rows and could leave admins with a truncated hierarchy. It now (1) computes the full json_group_array of {customerId, sitesId, units:[…]} over all orgs LEFT JOIN sites once via query_row, then (2) json_inserts that array into every admin profile in one UPDATE. A failure in either step is logged and skipped. clarity:backend/src-tauri/src/sqlite_api/db/migrations.rs:1080-1115
DB initialization now also CREATE TABLE IF NOT EXISTS the four tables owned by the monitor subsystem so they exist regardless of which subsystem starts first: monitor_system_config, monitor_collections, monitor_rules, and alarm_events (plus four indexes on alarm_events: opened_at_ms, status, (collection_id, tag_index), rule_id). clarity:backend/src-tauri/src/sqlite_api/db/migrations.rs:965-1010
NOTE: This schema is shared with the monitor subsystem (see Monitor). The
CREATE TABLE IF NOT EXISTSform is idempotent, but the column/index definitions here must be kept in sync with the monitor module's own DDL.
Database uses the bundled-sqlcipher feature of rusqlite — pulse-db.sqlite is encrypted at rest with the DB_PASSWORD key. As of a18d35c, each pooled connection is configured for SQLCipher v4 immediately after PRAGMA key:
PRAGMA cipher_compatibility = 4; -- v4 format (per-page HMAC)
PRAGMA cipher_use_hmac = ON; -- HMAC-SHA-256 over every page (tamper detection)
PRAGMA kdf_iter = 256000; -- PBKDF2 iterations (v4 default)
PRAGMA cipher_page_size = 4096;
A pre-existing v3 DB is read as v3 and rewritten as v4 transparently. clarity:backend/src-tauri/src/sqlite_api/db/mod.rs:128-141
Startup integrity check. At the end of run_migrations_internal, PRAGMA integrity_check reads every page, triggering the per-page HMAC verification. Any tampered byte yields a non-ok result, and migrations fail-fast with InvalidParameterName("Database integrity check failed: …") rather than starting on a corrupted/tampered DB. clarity:backend/src-tauri/src/sqlite_api/db/migrations.rs:1115-1130
run_migrations_internal begins by calling secure_store::provision_pack(...) to sync ADMIN_PASSWORD / OPC_PASSWORD / MQTT_BROKER_USERNAME / MQTT_BROKER_PASSWORD from env vars into the secret pack on every startup. User seeding then reads each user's password from the pack (admin/opcuser), hashes with Argon2, and stores meta_data without any plaintext password; configs seeding injects the MQTT broker username/password into the broker block at runtime rather than from the (now credential-free) configs.json. clarity:backend/src-tauri/src/sqlite_api/db/migrations.rs:6-20,519-540,800-830 — see API Server § Seed credentials & secret pack.
Changed in
1027dce. The secret pack is no longer an OS-keychain entry —secure_store.rsnow persists it as an AES-256-GCM + HMAC-SHA256 encrypted file (~/.clarity/secrets/secrets-pack.bin), keyed by the hardware fingerprint + build-bakedJWT_SECRET. TheADMIN_PASSWORDprovision pair also gained a build-timeoption_env!("ADMIN_PASSWORD")fallback (was empty).clarity:backend/src-tauri/src/sqlite_api/db/mod.rs:309
configurations seed file — build-time overrideNew in 67ac68c. The configurations table is seeded (when empty) from a JSON file include_str!'d into the binary. The seed file is now selectable at compile time via the CONFIGURATION_FILE env var: when set, db/mod.rs embeds Assets/seed_data/$CONFIGURATION_FILE; when unset it defaults to configurations.json. This lets staging/production builds bake a different default configuration set. (env_vars.sh carries a commented example.) clarity:backend/src-tauri/src/sqlite_api/db/migrations.rs:550-560, clarity:backend/src-tauri/env_vars.sh:31-32
New in 9847dba. sqlite_api::api::common gained an HTML-sanitization layer, ported from the old LoopBack Express middleware_xss/middlewareFunc.js, that runs on request bodies (and query params) before they are deserialized or stored. Source: clarity:backend/src-tauri/src/sqlite_api/api/common.rs:1-167
Encoders:
sanitize_string(&str) -> Cow<str> — fast-path returns the input unchanged unless it contains one of & < > " '; then, only if an HTML tag is detected (< followed by / or a letter), encodes all five characters to entities (& < > " '). Entity chars alone (no tag) are left as-is. Single encoding only — no double-encoding (the JS original double-encoded, corrupting stored data as &amp;).sanitize_value(&mut Value) — recursively applies sanitize_string to every string in a JSON object/array (input path).encode_html_value(&mut Value) — like sanitize_value but always encodes the five chars in any string (output/response path), no tag pre-check.Warp filters (drop-in replacements for warp::body::json):
sanitized_json::<T>() — parse body as Value → sanitize_value → deserialize into T; a re-deserialization failure returns 400 Invalid request body: ….sanitized_json_value() — parse body as Value → sanitize_value, return the Value.parse_raw_query also sanitizes each decoded key and value to block reflected XSS via query params. clarity:backend/src-tauri/src/sqlite_api/api/common.rs:283-305
Adoption. Every warp::body::json in the CRUD/route macros and dynamic layer was replaced: the crud_routes! / crud_routes_admin! / crud_routes_sync! / crud_routes_string_id! / nested_route! macros and the tagmeta/units interceptors (warp_routes.rs), the flat + nested-alter + nested-POST dynamic filters (warp_dynamic_routes.rs), connections.rs, tag_mappings.rs, collections.rs, plus the hand-written auth/collection routes in main.rs. clarity:backend/src-tauri/src/sqlite_api/api/warp_routes.rs:177-406, clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:351-950
Bulk-body shape change (side effect). Because
sanitized_json_value()yields aserde_json::Value(not a typed extractor), the/bulkandtagmetabulk handlers changed their closure arg fromVec<Value>toValueand now coerce withdata.as_array().cloned().unwrap_or_default(). A non-array body that previously produced a 400 (deserialization failure) now silently becomes an empty list (no-op) rather than an error.clarity:backend/src-tauri/src/sqlite_api/api/warp_handlers.rs
Routes are assembled by create_warp_routes (the single entrypoint) and mounted under warp::path("exactapi") in main.rs. The dynamic relation-discovery layer (build_dynamic_warp_routes) generates the CRUD + nested routes from the discovered FK graph; the old compile-time crud_routes!/nested_route! route macros are gone. There is no /v2 sub-prefix in the actual paths — /exactapi/{model} is the correct base. clarity:backend/src-tauri/src/sqlite_api/api/warp_routes.rs:76-406
What
api/routes.rsdoes now. It no longer registers any routes — it is a ~58-line file that invokes the surviving typed model macros:register_models!→invoke_schema!(theCrudModelstructs + relation table inschema.rs) andimpl_crud_string_id!(Configurations, …). Those structs define row shapes the dynamic core reads and back a handful of custom handlers (see The typed layer today); they mount no HTTP routes.clarity:backend/src-tauri/src/sqlite_api/api/routes.rs:1-58
Standard CRUD routes per model:
| Method | Path | Operation |
|---|---|---|
| GET | /exactapi/{model} |
List all (accepts ?filter=JSON LoopBack syntax) |
| POST | /exactapi/{model} |
Create record |
| GET | /exactapi/{model}/{id} |
Get by ID |
| PUT | /exactapi/{model}/{id} |
Replace by ID |
| DELETE | /exactapi/{model}/{id} |
Delete by ID |
| GET | /exactapi/{model}/{id}/exists |
Existence check ({"exists": bool}) |
| GET | /exactapi/{model}/count |
Count matching (?where=JSON) |
| GET | /exactapi/{model}/findOne |
First match (?filter=JSON) |
| POST | /exactapi/{model}/bulk |
Bulk create (array body) |
| POST | /exactapi/{model}/alter |
Bulk add/remove columns |
| POST | /exactapi/{model}/update |
Bulk update (where + data) |
| PUT | /exactapi/{model}/update |
Bulk update (alias) |
| DELETE | /exactapi/{model} |
Bulk delete (?where=JSON) |
| PATCH | /exactapi/{model} |
Upsert (insert or update) |
| DELETE | /exactapi/{parent}/{id}/{rel}/{child_id} |
Delete one nested child (HasMany, safety-belted — see below) |
delete_nested_item) — LoopBack gap F6Both the typed and dynamic layers now register DELETE /exactapi/{parent}/{id}/{relation}/{child_id} for HasMany edges. It hard-deletes the child with a safety belt: DELETE FROM {child} WHERE id = ? AND {fk} = ?. A forged URL cannot delete a child belonging to a different parent — a mismatch matches zero rows and returns 404 "Item not found in this parent context" (which deliberately does not leak whether the child exists under another parent). Success returns {"count": 1}.
Typed handler: clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/shims.rs:828-950. Registered via add_nested_routes! (HasMany arm). clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:1186-1244
The ?filter= and ?where= params accept LoopBack-compatible JSON:
GET /exactapi/units?filter={"where":{"siteId":"site-123"},"limit":50,"order":"name ASC"}
GET /exactapi/tagmeta?filter={"where":{"and":[{"unitsId":"unit-1"},{"dataTagId":{"like":"temp%"}}]}}
regexp)regexp predicates cannot be pushed into SQL, so they are evaluated in memory after the SQL query returns. The old implementation collected regex predicates into a flat Vec<(field, pattern)> and AND-ed them together — which silently dropped the boolean structure and never fired for pure-regex or/nor clauses (because where_clause_emits_sql returned false and the recursive walker was never invoked).
This is now a proper predicate tree (apply_filter_with_tree replaces apply_filter):
RegexNode / CompiledRegexNode enums model Leaf { field, pattern }, And, Or, Nor. clarity:backend/src-tauri/src/sqlite_api/utils/regexeng.rs:46-70extract_regex_tree(where_clause) walks the full where clause up-front (regardless of SQL emission) and builds the tree, preserving and/or/nor grouping; multiple top-level siblings become an implicit And. clarity:backend/src-tauri/src/sqlite_api/utils/regexeng.rs:200-263apply_regex_filter_tree(items, tree) compiles the tree (caching compiled Regex in REGEX_CACHE as Arc<Regex>; patterns that fail to compile become a never-matching $^ leaf) and retains rows via check_regex_tree. clarity:backend/src-tauri/src/sqlite_api/utils/regexeng.rs:71-84,361-437parse_regex_pattern accepts a raw string, Perl-style /pat/flags, or LoopBack { "regexp": …, "options": "i" }; the i flag injects a (?i) prefix. clarity:backend/src-tauri/src/sqlite_api/utils/regexeng.rs:85-168Leaf matches when the field exists and its value (stringified for numbers/bools/arrays/objects) matches; missing/null never matches. And([])→true, Or([])→false, Nor([])→true. clarity:backend/src-tauri/src/sqlite_api/utils/regexeng.rs:361-437The tag-mappings list handler uses the tree API (apply_filter_with_tree at :183 + apply_regex_filter_tree at :216). clarity:backend/src-tauri/src/sqlite_api/api/tag_mappings.rs:168-220
/exactapi/tag_mappings endpoint (custom handlers)Newly mounted (
45a686e). These custom routes existed but were never mounted —/exactapi/tag_mappingsreturned 405 on every verb until this range wired them into the filter chain (beforedynamic_routes, first-match priority).clarity:backend/src-tauri/src/sqlite_api/api/warp_routes.rs:386-406. The table is also now created on init (CREATE TABLE IF NOT EXISTS tag_mappings (id, generatedDataTagId TEXT DEFAULT lower(hex(randomblob(16))), metricName),clarity:backend/src-tauri/src/sqlite_api/db/migrations.rs:395-420) — fresh DBs previously had none.
| Method | Behavior |
|---|---|
GET /exactapi/tag_mappings?filter={"where":spec} |
Find-all — returns a JSON array of every matching row (no LIMIT); empty array if none. Any field omitted from spec is unconstrained. clarity:backend/src-tauri/src/sqlite_api/api/tag_mappings.rs:168-220 |
POST /exactapi/tag_mappings |
Find-or-create — metricName required (400 if missing/blank); reuses an existing row on an exact match of every field in the body (200 OK), else INSERT … RETURNING * (201 Created). generatedDataTagId is DB-defaulted when absent. clarity:backend/src-tauri/src/sqlite_api/api/tag_mappings.rs:87-156, 253-277 |
GET /{id} · PUT /{id} · DELETE /{id} |
Single-row fetch · partial update (RETURNING *) · delete ({"status":"deleted","id":id}); 404 if absent. clarity:backend/src-tauri/src/sqlite_api/api/tag_mappings.rs:222-358 |
A write body may introduce a new field: ensure_columns ALTER TABLEs it as a TEXT column, then invalidates the schema cache so the new column is visible to later filters (tag_mappings.rs:77-84). Because the table has no meta_data column, build_search_exprs emits a real-column predicate for a known field and NULL (zero rows) for an unknown key — instead of the json_extract(meta_data,…) it uses for generic tables, which previously errored with "no such column: meta_data". clarity:backend/src-tauri/src/sqlite_api/utils/sqlemit.rs:217-320
The Python SDK's mapped-tags fetch path depends on the find-all GET (one spec → many tags); see Python SDK § mapped_tags.
Deferred-regex LIMIT/OFFSET (correctness fix, a18d35c). When a deferred regex tree is active, the rows must be regex-filtered in memory before paging, so apply_filter_with_tree now omits LIMIT/OFFSET from the SQL (it would otherwise page the pre-filter row set) and the caller applies skip/limit afterwards. list_rows (non-streaming) drains skip then truncates to limit; the streaming dynamic_alter_shim tracks skipped/emitted counters and also applies a fields projection per row inside the stream. clarity:backend/src-tauri/src/sqlite_api/utils/sqlemit.rs:117-215, clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:686-697, clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/shims.rs:32-460
dynamic_alter_shimalso now unwraps a{"action":"find","filter":{…}}envelope (using thefiltersub-object when present) before parsing theLoopBackFilter.clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/shims.rs:32-120
Raw like regex. In extract_regex_tree, a like value containing regex metacharacters is now used as a raw regex pattern (with a (?i) prefix when options contains i) instead of being passed through like_to_regex_pattern, which would escape those metacharacters. clarity:backend/src-tauri/src/sqlite_api/utils/regexeng.rs:200-263
Trailing-dot patterns (new in dab6060). A like/regexp value that ends with a literal . and contains no other regex/SQL wildcards (e.g. kwh.) is now treated as "match anything that follows" — the user's likely intent. has_trailing_dot(s) detects this case (rejecting it if %, _, .*, ^, $, \, brackets, etc. are present), is_regex_like routes such values into the regex tree, and extract_regex_tree normalizes kwh. → kwh.* before compiling. The trailing_dot_to_regex helper does the same normalization for the in-memory like_to_regex path. clarity:backend/src-tauri/src/sqlite_api/utils/regexeng.rs:169-263
WhereNode)Nested GET routes materialize rows first and apply the non-pushdownable part of WHERE post-hoc (only simple leaf eq/like are pushed to SQL — see Leaf-table SQL pushdown), so a full in-memory predicate evaluator was added alongside the regex tree:
WhereNode models Leaf { field, op }, And, Or, Nor; WhereOp covers eq/neq/gt/lt/gte/lte/inq/nin/between/like/nlike and presence checks. clarity:backend/src-tauri/src/sqlite_api/utils/inmem.rs:82-114extract_where_tree(where_clause) builds the tree (multiple top-level siblings → implicit And); check_where_tree(item, node) evaluates one JSON row. And([])→true, Or([])→false, Nor([])→true. Numeric comparisons use value_to_f64; like/nlike compile via like_to_regex.warp_dynamic_routes.rs) for nested-list filtering.Case-insensitive like/nlike (new in dab6060). WhereOp::Like and Nlike now carry a (pattern, case_insensitive) tuple. extract_where_tree reads the sibling options key once per field predicate ("i" present → case-insensitive) and propagates the flag; eval_op prepends (?i) to the compiled regex when set. like patterns that are actually regex-style (trailing ./.*, or containing */+/?/(/[) return true from the where-tree's eval_op and defer to the regex tree, which runs afterward via apply_regex_filter_tree and matches them correctly. clarity:backend/src-tauri/src/sqlite_api/utils/inmem.rs:82-320
Dotted field-path resolution (new in dab6060). Both the where tree (check_where_tree) and the regex tree (check_regex_tree) now resolve a Leaf's field through lookup_path(item, path) instead of a flat item.get(field). lookup_path walks dotted paths like content.0.value (and bracket form content[0].value) — numeric segments are array indices, others are object keys — returning None (no match) if any segment is missing. This mirrors the SQL-side polymorphic_json_paths resolution so the two filter paths agree on nested JSON fields. clarity:backend/src-tauri/src/sqlite_api/utils/inmem.rs:29-80 (lookup_path), clarity:backend/src-tauri/src/sqlite_api/utils/regexeng.rs:361-437 (check_regex_tree), clarity:backend/src-tauri/src/sqlite_api/utils/inmem.rs:321-400 (check_where_tree)
NULL & _-wildcard semantics fixes (new in 1027dce). Three eval_op / LIKE-wrapping corrections aligning the in-memory evaluator with SQL three-valued logic: clarity:backend/src-tauri/src/sqlite_api/utils/inmem.rs:321-500
neq on NULL now returns false (a missing/NULL field no longer matches !=), matching SQL where col != 'value' excludes NULLs. Was !is_null_value(expected).
SQL-emission side changed again in
c41a03b: the generated SQL forneq/ninnow includes NULL rows —emit_neqemits(expr != ? OR expr IS NULL)(utils/mod.rs:1856) andemit_nin_innerappendsOR … IS NULL(utils/mod.rs:2012) — matching LoopBack's JS semantics whereneqmatches missing fields. The in-memory evaluator bullet above describes the pre-c41a03balignment to SQL three-valued logic; the SQL side has now deliberately diverged from it toward LoopBack semantics.> TODO-VERIFY:whether the in-memoryeval_opwas updated to match (the diff shows only the SQL emitters).
nlike on NULL now returns false (both the regex-pattern and literal branches add an explicit Null | None => false); the regex-pattern branch otherwise returns true to defer to the regex tree. Previously missing fields could erroneously pass nlike and inflate counts._ no longer suppresses LIKE auto-wrap. Both like_to_regex (in-memory) and emit_like_inner (SQL) now auto-wrap a wildcard-free value with %…% based on !contains('%') only — _ is a single-char LIKE wildcard, so like: "VTP_G1" becomes %VTP_G1% (substring) instead of an exact 6-char match, catching legitimate values like VTP_G1_M1_….clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:977-1244
All verified directly from warp_routes.rs. Nested routes follow the pattern GET /exactapi/{parent}/{id}/{child} and support the same list/count/findOne/create/update/delete operations as top-level models.
| Path | Child resource |
|---|---|
/exactapi/units/{id}/equipment |
Equipment in unit |
/exactapi/units/{id}/tagmeta |
Tag metadata for unit |
/exactapi/units/{id}/deviations |
Deviations for unit |
/exactapi/units/{id}/faulttrees |
Fault trees for unit |
/exactapi/units/{id}/faultTemplates |
Fault templates for unit |
/exactapi/units/{id}/incidents |
Incidents for unit |
/exactapi/units/{id}/calculations |
Calculations for unit |
/exactapi/units/{id}/dashboards |
Dashboards for unit |
/exactapi/units/{id}/tags |
Tags for unit |
/exactapi/units/{id}/activities |
ELog activities for unit |
/exactapi/units/{id}/heatrates |
Heat rate records for unit |
/exactapi/units/{id}/useractivities |
User activity entries for unit |
/exactapi/units/{id}/clients |
Clients for unit |
/exactapi/units/{id}/ingestconfigs |
Ingest configs for unit |
/exactapi/units/{id}/modelpipelines |
Model pipelines for unit |
/exactapi/units/{id}/boiler-assets |
Boiler assets for unit |
/exactapi/units/{id}/equipment/{eqId}/tagmeta |
Tag metadata scoped to equipment within unit |
/exactapi/units/{id}/dashboards/{dashId}/dashboard-plots |
Plots for a specific dashboard |
/exactapi/sites/{id}/units |
Units in site |
/exactapi/sites/{id}/equipment |
Equipment in site |
/exactapi/sites/{id}/clients |
Clients for site |
/exactapi/sites/{id}/ingestconfigs |
Ingest configs for site |
/exactapi/sites/{id}/activities |
Activities for site |
/exactapi/orgs/{id}/sites |
Sites in org |
/exactapi/orgs/{id}/units |
Units in org |
/exactapi/orgs/{id}/equipment |
Equipment in org |
/exactapi/orgs/{id}/clients |
Clients in org |
/exactapi/orgs/{id}/ingestconfigs |
Ingest configs in org |
/exactapi/orgs/{id}/userprofiles |
User profiles in org |
/exactapi/orgs/{id}/sites/{siteId}/units |
Units in a specific org→site |
/exactapi/customers/{id}/sites |
Sites for customer |
/exactapi/customers/{id}/units |
Units for customer |
/exactapi/customers/{id}/equipment |
Equipment for customer |
/exactapi/customers/{id}/ingestconfigs |
Ingest configs for customer |
/exactapi/customers/{id}/userprofiles |
User profiles for customer |
/exactapi/customers/{id}/sites/{siteId}/units |
Units in a specific customer→site |
RateLimiter with RateLimitConfig { max_requests, window_secs } guards the ingest endpoint (see Processing API).
clarity:backend/src-tauri/src/sqlite_api/api/rate_limiter.rs (referenced from clarity:backend/src-tauri/src/main.rs:130)
Complete entity list verified from clarity:backend/src-tauri/src/sqlite_api/schema.rs:
| Entity path | Model struct | Notable relationships |
|---|---|---|
units |
Units |
HasMany: equipment, tagmeta, deviations, faulttrees, faulttemplates, incidents, calculations, dashboards, tags, activities, heatrates, modelpipelines, boilerassets, useractivities, clients, ingestconfigs |
equipment |
Equipment |
BelongsTo: units, site, org; HasMany: tagmeta |
tagmeta |
Tagmeta |
BelongsTo: units, equipment; HasMany: deviations; extra search col: dataTagId |
deviations |
Deviations |
BelongsTo: units, dataTag (tagmeta) |
faulttrees |
FaultTree |
BelongsTo: units, template (faulttemplates) |
faulttemplates |
FaultTemplate |
BelongsTo: units |
incidents |
Incident |
BelongsTo: units |
calculations |
Calculation |
BelongsTo: units |
dashboards |
Dashboard |
BelongsTo: units; HasMany: plots (dashboardplots) |
dashboardplots |
DashboardPlot |
BelongsTo: dashboard |
sites |
Site |
HasMany: units, equipment, clients, ingestconfigs, activities; BelongsTo: org |
orgs |
Org |
HasMany: sites, units, equipment, clients, ingestconfigs, userprofiles |
userprofiles |
UserProfile |
BelongsTo: org; HasMany: activities (useractivities) |
useractivities |
UserActivity |
BelongsTo: userprofile, units |
clients |
Clients |
BelongsTo: units, site, org; HasMany: ingestconfigs |
ingestconfigs |
IngestConfig |
BelongsTo: units, site, org, clients; HasMany: tags |
statuses |
Statuses |
BelongsTo: ingestconfigs (configId) |
tags |
Tag |
BelongsTo: units, ingestconfig |
activities |
Activities |
BelongsTo: units, site |
heatrates |
HeatRate |
BelongsTo: units relation removed from schema.rs (the units → heatrates HasMany and HeatRate → units BelongsTo macro entries were deleted); heatrates is now served by the dynamic layer, which rediscovers the FK at runtime. clarity:backend/src-tauri/src/sqlite_api/schema.rs:16,112 |
modelpipelines |
ModelPipeline |
BelongsTo: units |
boilerassets |
BoilerAssets |
BelongsTo: units |
configs |
Configs |
No relationships |
configurations |
Configurations |
No relationships; TEXT primary key (String-Id model — see below) |
profiles_lookups |
ProfilesLookups |
No relationships |
labels |
Labels |
No relationships |
connections |
Connections |
No relationships |
users |
User |
No relationships; extra search cols: email, password, username (renamed from name); also served at /exactapi/Users |
The
usersnamecolumn was renamed tousernameacross the model, schema macro, and DB table.db/mod.rsmigrates an existinguserstable (rebuild + copy, remappingmeta_data.name→meta_data.username) and adds an idempotentALTER TABLE users ADD COLUMN username TEXT.clarity:backend/src-tauri/src/sqlite_api/models/mod.rs:475-490,clarity:backend/src-tauri/src/sqlite_api/schema.rs:127,clarity:backend/src-tauri/src/sqlite_api/db/migrations.rs:640-720
A phased migration replaced the hand-maintained schema.rs relation macros with a router assembled at startup from SQLite foreign-key introspection. The framework-agnostic pieces and a Warp shim live in the new modules; the Axum router exists but is dormant.
PRAGMA foreign_key_list / table_info ──► introspect::RelationMap (Phase 1)
relation-name overrides + soft FKs ──► aliases (Phase 2)
dynamic CRUD core (list/get/create/…) ──► api/dynamic (Phase 3)
BFS over the FK graph → nested URL paths ──► api/paths (Phase 4)
GET /exactapi/dyn/_schema introspection ──► api/warp_dynamic_bridge (Phase 5a)
full Warp CRUD surface (flat + nested) ──► api/warp_dynamic_routes (Phase 5b)
The migration is complete: the dynamic layer is the sole /exactapi/{entity} CRUD surface. It is assembled at startup by build_dynamic_warp_routes(db, storage) — discover tables + relations via PRAGMA, register the flat filter set per table, register nested filters per BFS path, then fold everything with .or().unify() (first-match-wins). clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:196-320
introspect.rs)Database::discover_tables() reads every user table's real columns (PRAGMA table_info) and declared FKs (PRAGMA foreign_key_list); build_relation_map() derives a RelationMap { has_many, belongs_to } where each FK becomes a DiscoveredRelation { parent_table, child_table, child_fk, parent_pk }. Both maps always contain an entry (possibly empty) for every discovered table. clarity:backend/src-tauri/src/sqlite_api/introspect.rs:230-340
OPT_OUT_TABLES excludes tables whose behavior is non-generic (not because their schema is unusual):
| Table | Reason kept off discovery |
|---|---|
connections |
Real url/username/password columns + custom Warp handlers |
tag_mappings |
Runtime ALTER TABLE expansion |
configurationsanduserswere removed from the opt-out list — the dynamic core now handles TEXT primary keys (STRING_PK_TABLES) and password-hashing/extra-column tables (EXTRA_COLUMNS+ a pre-bind hook).clarity:backend/src-tauri/src/sqlite_api/introspect.rs:108-130
aliases.rs)PRAGMA gives the relation graph but not the name clients use in ?include=[...] and nested URLs. Default inference: HasMany → child table name verbatim; BelongsTo → child FK column minus Id. RELATION_NAME_OVERRIDES replaces the inferred name for the LoopBack-irregular cases (org, site, dashboard, ingestconfig, userprofile, template for faulttemplates, dataTag for tagmeta, singular modelpipeline). Each override stores the inferred name as a tripwire so it becomes a no-op + startup warning if PRAGMA stops producing the relation. clarity:backend/src-tauri/src/sqlite_api/aliases.rs:75-191
SOFT_FK_EXCEPTIONS lists relations enforced only at the application level (no FOREIGN KEY clause), which PRAGMA cannot discover — these are merged into the graph so they are still routable. clarity:backend/src-tauri/src/sqlite_api/aliases.rs:140-186
As of
a18d35c,ingestconfigs.clientsId → clients.idis registered as a soft FK.clientsIdwas added toingestconfigsviaALTER TABLEon legacy DBs, but SQLite cannot add aFOREIGN KEYconstraint that way, soPRAGMA foreign_key_listreturns only the 3 original FKs and misses this edge. Declaring it as a soft FK makesGET /exactapi/clients/:id/ingestconfigsroutable.clarity:backend/src-tauri/src/sqlite_api/aliases.rs:153-168
More soft FKs added in
9847dba. On legacy DBs all ofingestconfigs' FK columns wereALTER TABLE-added without constraints, soPRAGMA foreign_key_list(ingestconfigs)can return empty — not just missingclientsId. Four more edges are now registered so nested routes resolve andfk_columns_for_tabledoesn't drop the columns (which would makecreate_rowstore the value only inmeta_dataand nested-route CTEs silently drop rows):
ingestconfigs.unitsId → units.id,ingestconfigs.siteId → sites.id,ingestconfigs.orgsId → orgs.idtags.ingestconfigId → ingestconfigs.id(added viaALTER TABLE db/mod.rs:1078;PRAGMA foreign_key_list(tags)otherwise returns onlyunitsId)
clarity:backend/src-tauri/src/sqlite_api/aliases.rs:150-197
Soft FKs are also consulted by lookup_fk (used by the recursive nested-DELETE CTE walker): it first checks the discovered RelationMap, then falls back to SOFT_FK_EXCEPTIONS so application-level edges resolve there too. clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:2469-2495
BelongsTo includes (
45a686e).resolve_include_keynow also matches the BelongsTo direction, so a child can include its parent (e.g.?include=["org"]onunitsviaorgsId → orgs) — previously only HasMany includes resolved.clarity:backend/src-tauri/src/sqlite_api/aliases.rs:300-330
Soft FKs merged into column/reference discovery (9847dba). Two more consumers now fold in SOFT_FK_EXCEPTIONS so a soft-FK column is treated as a real FK column everywhere, not just in nested routing:
fk_columns_for_table(db, table) appends any soft-FK child_fk not already returned by PRAGMA foreign_key_list. Without this, create_row stored the value in meta_data JSON instead of the SQL column — perpetuating the very gap the backfill repairs. clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:2991-3010fk_references_for_table(db, table) likewise appends soft-FK (child_fk, parent_table) pairs for FK-validation consistency. clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:3020-3046Soft-FK JOINs check meta_data too (nested lists, 9847dba). In list_nested's recursive CTE, the LEFT JOIN for a soft-FK child edge now matches on (alias.fk = w.current_id OR json_extract(alias.meta_data, '$.fk') = w.current_id) — mirroring the flat route's OR-of-both semantics — so rows whose FK value lives only in meta_data (legacy writes) are not silently dropped from nested routes. Hard FKs keep the plain alias.fk = w.current_id join. clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:2461-2501
api/dynamic.rs)Framework-agnostic handlers (list_rows, get_row, create_row, update_row, delete_row, row_exists, …) plus the per-table knobs that let generic code serve special tables:
| Knob | Tables | Effect |
|---|---|---|
STRING_PK_TABLES |
configurations |
TEXT primary key: id used verbatim (no parse::<i64>), client may supply id on create |
EXTRA_COLUMNS |
users (email,password,username), tagmeta/deviations (dataTagId), incidents (criticalTags) |
Bind named columns as real SQL columns, not meta_data JSON |
pre_bind_hook_users |
users |
Hash plaintext password with argon2id before binding (no security regression vs typed layer) |
pre_bind_hook_dual_org_fk |
DUAL_ORG_FK_TABLES (6) |
Mirror orgsId↔customerId on write |
clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:2629-2760
build_dynamic_router(db) -> Router<SqliteApiState> (the Axum surface) is dormant — main.rs mounts only the Warp stack at /exactapi/.... If discovery fails it returns an empty router so the server still starts. clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:101-140
A read_meta_data_bytes helper tolerates both BLOB (canonical) and TEXT (legacy) meta_data storage; the same fix was applied to the typed process_include/derive_parent_fks paths. clarity:backend/src-tauri/src/sqlite_api/api/handlers.rs:19-25
Extra-column BLOB/TEXT polymorphism. EXTRA_COLUMNS values are read/written as Option<String>, but some columns store JSON as BLOB rather than TEXT (notably the newly-enabled incidents.criticalTags, which holds a JSON array as BLOB in legacy data). Two helpers handle the round-trip:
read_extra_col_string(row, col) (read path) tries Option<String> first (TEXT/NULL fast path), then falls back to reading the column as a BLOB and doing a lossy UTF-8 conversion. It replaces the previous direct row.get::<_, Option<String>>(col) in list_rows, get_row, fetch_rows_for_include_with_params, and list_nested. clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:89-101extra_col_to_string(val) (write path) serializes arrays/objects to a JSON string, stringifies numbers/bools, and maps JSON null → None, so create_row/update_row/upsert_row/update_all persist non-string extra columns correctly. clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:103-119parse_extra_col_value(s) re-types JSON-valued extra columns on read (new in 57c553a). read_extra_col_string always returns a String, so wrapping it with json!(s) produced a quoted string — e.g. incidents.criticalTags came back as "[{…}]" instead of an array. parse_extra_col_value re-parses any value starting with [ or { back into the intended Value (falling back to json!(s) on a parse error); plain string columns (tagmeta.dataTagId, users.email) never start with those characters and pass through unchanged. It replaces the json!(s) calls in list_rows, get_row, fetch_rows_for_include_with_params, list_nested, and the include-row branches. clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:112-119Enabling
incidents.criticalTagsresolved a prior TODO that left it commented out ofEXTRA_COLUMNSbecause the String-only reader could not handle its BLOB storage.clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:2705-2708
Companion meta_data BLOB/TEXT tolerance was also added to utils::link_resource_to_admin and utils::sync_admin_access, which previously assumed Vec<u8> (BLOB) and would fail on TEXT-stored profiles. clarity:backend/src-tauri/src/sqlite_api/utils/sqlemit.rs:581-620, 2080-2088
api/paths.rs)bfs_paths(map) walks the full FK graph from every root and emits one NestedPath per reachable nested URL (e.g. [orgs, sites, units, equipment] → /exactapi/orgs/:id/sites/:id/units/:id/equipment). A path terminates at a leaf with no outgoing FKs, an opt-out table, or a cycle. Soft-FK edges are merged at every step. Output is in BFS order (shorter first) so Axum's later-wins matcher gets correct precedence. Routes must be registered before the router is finalized, hence the one-shot startup computation. clarity:backend/src-tauri/src/sqlite_api/api/paths.rs:199-214
Root-table edge-detection fix (
1027dce).bfs_paths_with_max_depthdecides whether a root has any nested paths to emit. It now checkshas_many(outgoing edges where this table is the parent), notbelongs_to(edges where it is the child). A root likeorgshas children (sites,units, …) but no parent of its own, sobelongs_to[orgs]is empty whilehas_many[orgs]is not — the old check misclassifiedorgsas a "pure leaf" and emitted a degenerate self-loop, so the nested GET/POST/DELETE filters fororgs/:id/sites,orgs/:id/units, etc. were never registered (the flat/orgsCRUD was unaffected).clarity:backend/src-tauri/src/sqlite_api/api/paths.rs:234-252
_schema introspection endpointGET /exactapi/dyn/_schema (admin auth) returns the discovered tables (columns + outgoing FKs), the full relation map, the alias overrides, and the soft-FK list as JSON — a self-describing contract the frontend can fetch once at boot. Mounted after the static routes. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_bridge.rs:167-190
api/warp_dynamic_routes.rs)build_dynamic_warp_routes(db, storage) is the Warp shim that wires the Phase-3 core into the live /exactapi/... URLs, matching the typed layer's URL shape exactly but generated from the FK graph. Per table it exposes the full flat CRUD surface (GET/POST/PATCH/DELETE /{t}, GET/PUT/DELETE /{t}/:id, count, findOne, update, bulk, :id/exists) and, per NestedPath, GET /{root}/:id/{rel} (any depth) plus any-depth POST (create — see below), depth-1 POST …/alter (filtered list, see below), and DELETE …/:fk_id (F6).
New in f3f16e6. Every read path in the dynamic layer now emits top-level ID fields as JSON strings, not numbers. The UI validates responses with z.string().uuid(), so a numeric id / *Id (the tables use INTEGER PRIMARY KEY, there are no real UUIDs) failed schema validation; serializing them as strings satisfies the schema shape and lets the UI keep its validation unchanged. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:1009-1100
stringify_row_ids(row) rewrites a single object: for each top-level key that is exactly id or ends with Id, a numeric value becomes its string form (i64 / u64 always; floats only when finite and whole, e.g. 1.0→"1" — NaN/Infinity/fractional pass through). Skipped: dataTagId (already a string such as "VTP_G1_M1") and any null value (nullable FKs stay JSON null, not the string "null"). It does not recurse into nested sub-documents (meta_data, equipmentLoad, benchmark keep their native shape) — except that array values (include-relation child rows) are recursed into, so each related row's own id/*Id are stringified as well. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:1009-1054accessAllowed special-case (new in 67ac68c). stringify_row_ids skips its normal top-level rule for the accessAllowed key and instead calls normalize_access_allowed_units(v). accessAllowed is a BLOB-stored JSON array of {customerId, sitesId, units: [...]} objects; the helper stringifies each numeric customerId/sitesId and every numeric entry inside each units array (legacy data mixes ints and strings), so the UI sees one consistent string shape. Whole-valued floats are converted (5.0→"5"); already-string values pass through. This is the write/response counterpart to the auth layer's parse_id reader — see API Server § User Hierarchy Resolution. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:1174-1213stringify_response(v) applies stringify_row_ids to a single object or to each element of an array; other JSON shapes pass through unchanged. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:1056-1069reply_value / reply_option_value are stringify-applying replacements for reply_for / reply_option. The CRUD shims were switched to them: list_items_shim, create_item_shim, create_bulk_shim, update_item_shim, upsert_item_shim, find_one_shim, the nested-POST filter, and the odd-segment branch of list_nested_shim all return through reply_value/reply_option_value; get_item_shim and the even-segment (single-item) branch of list_nested_shim call stringify_row_ids directly on the in-hand row. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:1071-1100/alter streaming shim covered too (extended in 57c553a). dynamic_alter_shim now stringifies IDs on both of its output paths: the non-streaming {"result": items} envelope wraps items in a Value::Array and runs stringify_response (recursive into include arrays); the streaming json_insert path runs stringify_row_ids on each row after the optional fields projection (and on the raw row when no projection is applied), since SQLite's json_insert emits integer id/fk_id. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:1192-1200, 1326-1345Consumers that previously parsed
id/*Idas numbers must now accept strings. The conversion is applied uniformly across list, get-by-id, create, bulk-create, update, upsert, findOne, nested reads, and the/alter(flat + nested) streaming shim. The internal ingest-config reader was updated to tolerate both shapes — see Processing API § ingest-config ID parsing.
:id routes + route orderingThe by-id routes (GET/PUT/DELETE /{t}/:id) now take a warp::path::param::<String> instead of ::<i64>, so TEXT-PK tables (e.g. configurations, 24-char hex ids) are reachable by id through the dynamic layer — previously the i64 param silently failed to match a string id. The shims get_item_shim/update_item_shim/delete_item_shim take id: String accordingly. Because :id is now a String, the count and findOne literal routes must be registered before /:id so the words count/findOne are not captured as ids; the registration order was reordered to put them first. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:413-525, 1300-1306, 1398-1404
The flat PATCH /{t} upsert route likewise pulls id from the body supporting both a string and an integer id (v.as_str() then v.as_i64()), and upsert_row branches on STRING_PK_TABLES: TEXT-PK tables bind the id verbatim, integer tables parse to i64. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:371-382, clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:1902-1955
orgs/sites/units name changeOn-disk collection folders are now renamed to follow a name change for the three hierarchy tables. Both update_item_shim (PUT /{t}/:id) and update_all_shim (POST /{t}/update, which gained a Storage arg) pre-fetch the old row(s), run the update, then call renaming::handle_rename(db, storage, table, id, old_row, new_row) per changed row when table ∈ {orgs, sites, units}. A rename failure is logged as a warning and does not fail the request. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:1300-1356, 1449-1496
handle_rename resolves the folder path (Base/Org, Base/Org/Site, Base/Org/Site/Unit), renames the directory, and rewrites the metadata recursively. When the DB name no longer matches what's on disk, find_actual_folder_fallback(parent_dir, new_path) scans the parent directory and, only if exactly one candidate subdirectory exists (skipping hidden dirs, backups, and the new path itself), uses it as the source; multiple candidates log a warning and abort the auto-detect. clarity:backend/src-tauri/src/sqlite_api/api/renaming.rs:32-90, 123-155
rename_dirtarget-exists & cross-device handling (9847dba). If the target path already exists (a symptom of the old duplicate-row bug),rename_dirno longer errors — it logs a warning, removes the stale source folder, and returnsOk. After a successfultokio::fs::renameit also defensively removes the old path if it still exists (a cross-device rename can copy-then-leave the source).clarity:backend/src-tauri/src/sqlite_api/api/renaming.rs:108-135
cleanup_stale_folders(db, storage, remove)helper (9847dba, not yet wired). A newpub async fnthat walks<base>/<org>/<site>and returns (optionally removes) folders that match no DBorgs/sitesrow — for reconciling the filesystem after duplicate-creation bugs. It has no callers in the current source (defined atrenaming.rs:303), so no route exposes it yet.clarity:backend/src-tauri/src/sqlite_api/api/renaming.rs:298-420
list_nested_shim now runs include expansion before the fields projection (include needs the parent rows' FK columns for BelongsTo lookups), and preserves the include relation names so the projection does not strip the data that include just added. The parent table for include is resolved from the URL segments — for even segment counts (single item, e.g. /units/1) the second-to-last segment is the table; for odd counts (list) the last segment is. extract_include_relation_names pulls relation names from string / array / object include forms (recursively) for the preserve list. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/shims.rs, 1801-1826
When an include carries a scope with limit (top-N children per parent), the child rows are selected via a ROW_NUMBER() window inside a derived table. Ordering there cannot use table-qualified column refs (the alias isn't visible in the subquery), so apply_order_subquery emits unqualified column / json_extract(meta_data, '$.<field>') references, and the row cap is now a bound parameter (WHERE rn <= ?) rather than string interpolation. clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:1006-1031, 1116-1152
POST /{root}/:id/{rel}/…/{rel}/alter (any depth)Introduced depth-1 in dab6060; generalised to any depth in 67ac68c. The nested counterpart to the flat POST /{table}/alter LoopBack filter endpoint now matches an arbitrary-depth path and auto-injects the chain of parent FK constraints into the body before dispatching to the standard flat alter shim. E.g. POST /exactapi/units/1/equipment/2/tagmeta/alter with body {"where":{"description":{"like":"kwh"}}} is rewritten to enforce both tagmeta.unitsId = 1 and tagmeta.equipmentId = 2 (one constraint per segment). build_nested_alter_filter_any_depth is registered for every NestedPath with depth() >= 1 (was gated on depth() == 1, which 405'd all depth-2+ paths). clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:743-810
warp::path::tail() (same pattern as the nested GET/POST filters) and validates the whole path shape inside the tail and_then — last segment must be alter; the remaining chain must be an even, non-empty (id, rel) sequence whose count equals segments.len() and whose rels match the discovered segments[i].url_relation_name. Validating up-front is critical: when several alter filters are registered under the same root, body::json consuming the request body in a non-matching filter would make every later filter fail with "request body already taken". By rejecting before body::json, only the matching filter reaches the body. On success the and_then yields (leaf_table, constraints: Vec<(i64, child_fk)>).dynamic_nested_alter_shim(db, child_table, constraints, body) applies merge_parent_constraint once per (parent_id, parent_fk) in URL order, each wrapping the running where in {"and": [<existing>, {fk: id}]} — equivalent to WHERE fk1 = id1 AND fk2 = id2 AND …. merge_parent_constraint still accepts the three body shapes ({"where":…}, {"filter":{…}} unwrapped, bare filter) and preserves fields/include/limit/skip/order. The merged body goes to dynamic_alter_shim, so the nested route inherits streaming, deferred-regex paging, fields projection, and ID stringification. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/shims.rsalter-suffix guards on the GET and POST nested filters (new in 67ac68c). build_nested_get_filter and build_nested_post_filter_any_depth now reject any tail whose last segment is alter (reject::not_found()), so those paths fall through to the alter route instead of the GET filter returning 405 Method Not Allowed or the POST filter buffering then dropping the body (the same "body already taken" hazard). The POST guard extracts the tail once inside its own and_then and threads the Tail through. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:855-872, 909-940New in 1027dce. Nested POST was previously depth-1 only (a hand-rolled typed matcher for /{root}/:id/{rel}). It now works at any depth via a single warp::path::tail()-based filter, build_nested_post_filter_any_depth, registered for every NestedPath with depth() >= 1. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:617-674, 827-915
[id1, rel1, id2, rel2, …] (odd/short tails reject::not_found() so other matchers can try). It prepends the real root_table (resolving aliases like customers→orgs) to form [root, id, rel, …, rel] and dispatches to create_nested. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:827-915create_nested(db, segments, payload, storage) now accepts odd-length ≥ 3 segments (was exactly 3). It uses the last three segments — [parent_table, parent_id, child_table] — to resolve the FK via lookup_fk(map, parent_table, child_table), injects the parent FK into the payload, and calls derive_parent_fks to pull transitive FKs from the parent row's hierarchy (e.g. POST /sites/1/units yields a unit with siteId=1, orgsId, customerId). It gained a storage arg so create_row's post_create_hook can build the on-disk collection. clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:2648-2695Auto-collection creation in post_create_hook. The on-disk layout is <base>/<org>/<site>/<unit>/<grid>/metadata.json, so a bare sites/units row has no writable grid. Previously only orgs auto-created that tree; now: clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:3405 (lookup_name), clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:3437-3650 (post_create_hook)
| Created table | Auto-creates |
|---|---|
orgs |
unnamed_site_{siteId} + unnamed_unit_{unitId} + collection <org>/unnamed_site_{siteId}/unnamed_unit_{unitId}/default_grid |
sites |
a default unnamed_unit_{unitId} (linked to admin) + collection <org>/<site>/unnamed_unit_{unitId}/default_grid — only if the site has an org FK |
units |
collection <org>/<site>/<unit>/default_grid — needs org + site names resolved |
Org/site names are resolved from the DB (lookup_name(pool, table, id) reads meta_data.$.name, collapsing all errors to None); the new row's own name comes from the create payload. Each collection's metadata.json is then patched with {organization, site, unit} via update_metadata_fields. Failures are logged and do not fail the SQL insert.
Configurable business unit (
45a686e). Thebufield stamped into auto-created site/unit metadata is no longer the hardcoded["ems"]— it comes fromdefault_bu(), which reads the comma-separatedCLARITY_BUenv var once (memoized in aOnceLock), defaulting to["ems"]when unset (e.g.CLARITY_BU="ems,fms").clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:3710-3730(call sites:3755–3953).
ID-based default names (
9847dba). The auto-created default site/unit are no longer the fixed literalsunnamed_site1/unnamed_unit1. Each is inserted with a temporary name (unnamed_site/unnamed_unit), thenUPDATEd tounnamed_site_{id}/unnamed_unit_{id}using the row's own auto-increment id, and the on-disk collection is created under that unique name. This makes two orgs/sites created back-to-back get distinct default children instead of colliding on the same folder name. The same rename fix (seecreate_item_warp) removed a duplicate default-tree creation that left stale filesystem folders.clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:3506-3800
New in 9847dba. In the typed layer's create_item_warp, the orgs branch used to create its own default site + unit + collection inline (the same work post_create_hook in the dynamic layer does). With both layers compiled in, that produced two default trees and left stale filesystem folders after a rename (two DB rows with the same name). The typed branch now only links the new org to admin (link_resource_to_admin) and returns; default site/unit/collection creation is left entirely to post_create_hook. clarity:backend/src-tauri/src/sqlite_api/api/warp_handlers.rs
dispatch_post_create_syncNew in 1027dce. The file-layer sync side-effects fired after a row is created (tags → sync_tag_created; tagmeta → sync_tagmeta_created with site/org→units fan-out plus fire-and-forget monitor-rule creation when limLo/limHi is present; equipment → sync_equipment_created) were duplicated inline in create_item_shim and create_bulk_shim, and absent from the nested POST path. They are now a single helper called by all three: clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:1358-1496
POST /{t} → create_item_shimPOST /{t}/bulk → create_bulk_shim (per item, body = {} so the per-item monitor-rule spawn is a no-op — the bulk handler keeps its own batched monitor-rule block)POST /{root}/:id/{rel}/… → build_nested_post_filter_any_depthNet effect: tags/tagmeta/equipment created via a nested POST now register in the unit's on-disk collection just like the flat route, which they did not before.
New in 57c553a. The file-layer tagmeta sync functions in sync.rs (which add a new tag to a collection's metadata.json) now also register the tag into the in-memory TAG_SCOPE_MAP via tag_resolver::upsert_tags, so a write_fast arriving immediately after a tagmeta upload can resolve the tag's scope without waiting for the next full build_tag_map scan (or a restart) — otherwise the write would report the tag as unresolved and drop its data.
sync_tagmeta_created upserts each tag on a successful Added result, scoped to its (org, site, unit, grid). clarity:backend/src-tauri/src/sqlite_api/sync.rs:584-597sync_tagmeta_bulk collects the Added tags per collection (skipping AlreadyExists, which are already in the map from startup) and upserts them in one call per collection. clarity:backend/src-tauri/src/sqlite_api/sync.rsSee Tag Resolver § Lifecycle and the complementary write-side refresh-and-retry.
check_unique_constraints (currently users.email, case-insensitive) now prefixes its rejection message with DUPLICATE:. create_item_shim strips that prefix and returns 409 Conflict via conflict_error (JSON {"error": "<message>"}) instead of the generic 500 internal_error, so clients can distinguish "already exists" from a real server error. clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:2953-2995, clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:970-980, 1461-1476
New in dab6060. list_nested walks the FK chain with a recursive CTE but historically applied all leaf-table filtering in memory — materializing every leaf row (e.g. all ~2850 tagmeta for unit 1) before discarding most of them, which took 15+ s for 7 OR'd LIKE patterns. list_nested now takes a leaf_where_clauses: &[LeafWhereClause] argument that it appends to the CTE's final SELECT as bound SQL predicates.
Boolean-tree pushdown (
1027dce). The pushdown argument was a flat&[(col, op, LeafWhereValue)]list, so a top-levelorgroup silently becameand(each clause was appended withAND), making nested routes return zero rows for queries the flat route handled. It is now a tree,LeafWhereClause=Clause(col, op, val)|Or(Vec<…>)|And(Vec<…>), emitted by the recursiveemit_leaf_wherewhich parenthesises groups and joins children with their own operator, preserving the user's grouping.clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:117-282, 2358-2366
dynamic::LeafWhereValue is a 4-variant enum: Literal (eq on a real column), JsonEq (eq on a json_extract(meta_data,'$.path')), Like (LIKE on a real column, with optional COLLATE NOCASE), JsonLike (LIKE on a JSON path). As of 1027dce the Literal and JsonEq payloads hold a serde_json::Value (was String) and are bound via bind_json_value, which preserves the JSON type (Number→i64, Bool→0/1, String→TEXT) — binding everything as String previously caused a TEXT-vs-INTEGER mismatch and silently returned 0 rows for e.g. systemInstance: 2. clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:2343-2352, 217-2351027dce). For real-column conditions (Literal, Like), emit_single_clause now emits (l."col" op ? OR json_extract(l.meta_data,'$.col') op ?) — matching rows where the value lives in the real column or the meta_data JSON, exactly like the flat route's apply_filter_with_tree. Previously the nested route checked only the real column, so rows with the value in meta_data only (legacy / dual-write data) were excluded by the nested route but returned by the flat route. clarity:backend/src-tauri/src/sqlite_api/api/dynamic.rs:237-282split_where_for_pushdown(where_clause, valid_columns) (in warp_dynamic_routes.rs) splits a LoopBack where into (pushdown, remaining). Conservative rules: only top-level eq / like (no regex metachars, no trailing ./.*) push down; and groups push their pushdownable leaves and keep the rest; an or group pushes down only if every child is pushdownable; nor, in/nin, gt/lt, between, regexp, exists, etc. always stay in memory. Real-column vs JSON-path is decided against the leaf table's columns (fetched via db.get_columns_for_table). Wildcard-free like patterns are auto-wrapped %…% to match the flat route's substring semantics. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/shims.rslist_nested_shim resolves the leaf table from the URL segments (even count → second-to-last, odd → last), runs the split, passes pushdown to list_nested, and applies the remaining clause in memory afterward via check_where_tree and apply_regex_filter_tree (the latter catches trailing-dot / regex patterns the where-tree can't). clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/shims.rsTwo URL-rewrite tables map discovered SQL table names to client-facing URL segments:
| Table | Constant | Mapping |
|---|---|---|
URL_ALIASES |
customers→orgs, Users→users |
Serve a CRUD surface under an alias URL while binding to a different SQL table |
TABLE_URL_OVERRIDES |
faulttemplates→faultTemplates, dashboardplots→dashboard-plots, profiles_lookups→profiles-lookups |
kebab/camel URL spellings |
clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:104-153
Mounting is non-trivial: create_warp_routes runs inside Tauri's sync setup closure on a tokio worker thread, where block_on panics ("cannot start a runtime from within a runtime"). The builder is therefore run on a fresh std::thread and the result returned over an mpsc::channel. The dynamic routes are or()-chained last, so the custom interceptor / opt-out routes mounted earlier win any URL they serve (first-match-wins). clarity:backend/src-tauri/src/sqlite_api/api/warp_routes.rs:386-406
There is no per-path typed-vs-dynamic switch anymore. The earlier DYNAMIC_TABLES / dynamic_path_filter / __dyn_skip__ sentinel gate — which let a table opt back into the removed typed route macros — is gone, along with the route-generating macros themselves.
Precedence is now pure Warp filter ordering in create_warp_routes: the few remaining custom interceptors and opt-out handlers are .or()-chained before dynamic_routes, and the dynamic surface is folded in last — so first-match-wins gives the custom routes priority, and every other table falls through to the dynamic layer. clarity:backend/src-tauri/src/sqlite_api/api/warp_routes.rs:386-406, clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:311-313
Mounted before the dynamic surface (and thus winning their paths): the tag_mappings custom routes, connections, the tagmeta/units monitor-rule interceptors, attachments, and /incidents/raw. Everything else is served by the dynamic layer.
CrudModel layer todayThe refactor deleted the route-generating macros (crud_routes! / crud_routes_admin! / crud_routes_string_id! / nested_route!) and the modules hooks.rs, service_auth.rs, db_helpers.rs. It did not delete the typed model layer. The CrudModel trait and its generator macros — invoke_schema! (schema.rs), impl_crud! / impl_belongs_to! / impl_crud_string_id! (api/handlers.rs) — survive and are still compiled. clarity:backend/src-tauri/src/sqlite_api/api/handlers.rs:115,571,900
They serve two purposes today:
FromRow describe the columns the dynamic core reads (extra columns, string PKs).get_items_raw_warp::<Incident> for /incidents/raw (clarity:backend/src-tauri/src/sqlite_api/api/warp_routes.rs:211), alter_items_warp::<Connections> (clarity:backend/src-tauri/src/sqlite_api/api/connections.rs:313), and the tagmeta/units interceptors (create_item_warp::<Tagmeta>, update_all_warp::<Units>, …).So the typed layer is not wholesale dead code — the route macros are gone, but the trait/model/handler stack backs these specific routes. Only the parts with no callers (see § REMOVED) were dropped.
Some in-module doc-comments still call this an "Axum" or "typed router" layer (e.g. in
api/dynamic.rs,api/paths.rs). Those comments are stale — the live surface is Warp, and thebuild_dynamic_routerAxum entry (api/dynamic.rs:101-140) is defined but mounts nothing.
The per-row authorization check (formerly inline in get_item) was extracted to auth.rs so both layers share identical semantics:
check_row_access(table, row, claims) — pure/sync. Admin bypass; non-admin with empty units_id → NoUnitAccess; units table checks the row's own id; other tables check unitsId (missing/null/non-int → deny).check_row_access_with_db(db, table, row, claims) — async; for tables lacking a direct unitsId it resolves via FK traversal (dashboardplots → dashboardId → dashboards.unitsId).Returns AccessDenied; each layer maps it to its own HTTP error (warp Rejection or (StatusCode, String)). clarity:backend/src-tauri/src/auth.rs:566-740. See API Server § Row-level access policy.
delete_item_shimNew in 67ac68c. The generic DELETE /exactapi/{table}/:id handler (delete_item_shim, which gained a Storage arg) no longer issues a single dynamic::delete_row. It now runs recursive_cascade_delete, which walks the relation graph and deletes every descendant row that holds an FK to (table, id) before deleting the row itself, returning the total rows deleted. This fixes the pre-existing FOREIGN KEY constraint failed 500 that fired when deleting a parent that still had child rows. clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/shims.rs
recursive_cascade_delete(db, storage, table, id, visited) — clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/shims.rs:
HashSet<(table, id)> of visited pairs; re-entry returns 0, guaranteeing termination even if the relation map ever contains a cycle.orgs/sites/units, calls collections::cascade_collections_for_parent(db, storage, table, id) (best-effort; failures are logged and do not block the row delete) — see Collections route surface.build_relation_map().has_many plus conventional_child_fks. For each, it SELECT id FROM {child} WHERE {child_fk} = ?1 (reading the id as a rusqlite::Value and stringifying, since ids may be INTEGER or TEXT) and recurses on every child id.dynamic::delete_row(db, table, id); a residual FK error here surfaces as 500 (a relation the walk didn't cover).conventional_child_fks(conn, parent) — clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/shims.rs. The Clarity schema links most parent→child edges by convention (orgsId, siteId, unitsId, …) rather than formal REFERENCES clauses, so PRAGMA foreign_key_list returns nothing for them and build_relation_map().has_many is empty there. This helper closes the gap: it builds the candidate suffix set {parent}id / {parent}_id / {Parent}Id / {Parent}_Id and scans PRAGMA table_info over every non-sqlite_% table, returning (child_table, child_fk) for any column whose name matches (case-insensitive).
/exactapi/collectionscreate_collections_routes(db, storage) builds the /exactapi/collections CRUD stack. It is not a SQLite table (collections live on disk as metadata.json) and is mounted last in create_warp_routes's return chain — after dynamic_routes — so the dynamic layer stays first-match-wins for every real table; connections (an OPT_OUT_TABLES entry) is mounted alongside it. clarity:backend/src-tauri/src/sqlite_api/api/collections.rs:79-170, clarity:backend/src-tauri/src/sqlite_api/api/warp_routes.rs:386-406, 1487-1488
Metadatadeserialization tolerance (9847dba). TheMetadatastruct'stagsanddescriptionsfields gained#[serde(default)], so ametadata.json(or a/collectionsGET response) that omits them deserializes to empty vectors instead of failing — this is what the ingest scope cache reads viaentry.metadata.tags.clarity:backend/src-tauri/src/sqlite_api/api/collections.rs:53-58. The two collection write routes also now use the sanitizing filters (sanitized_json::<CollectionData>()/sanitized_json_value()).clarity:backend/src-tauri/src/sqlite_api/api/collections.rs:115-147
c8b9ceb)collections.rs also exposes a full LoopBack-style surface over the on-disk collections, so clients can treat them like any other entity. Docs are returned as fully-materialized raw metadata.json with the org/site/unit/grid location keys layered on and a nested metadata mirror for back-compat (build_collection_doc). clarity:backend/src-tauri/src/sqlite_api/api/collections.rs:94-210, 243-299
| Method | Path | Operation |
|---|---|---|
| GET | /collections |
List (LoopBack filter / X-Filter header: where/order/fields/skip/limit) |
| POST | /collections |
Create (idempotent — see below) |
| POST | /collections/update?where={…} |
Bulk partial update (updateAll) |
| GET | /collections/{id} |
Get by id |
| PUT | /collections/{id} |
Full replace (replaceById) |
| PATCH | /collections/{id} |
Partial merge (updateAttributes) |
| DELETE | /collections/{id} |
Delete (11-step cascade — see below) |
metadata.json now also persists the org/site/unit/grid names + per-tag descriptions, and update_collection is append-only (descriptions preserved; a descriptions-less collection is no longer invisible to GET /collection). clarity:backend/src-tauri/src/sqlite_api/api/collections.rs:243-299
POST /collections — idempotent createNew in 67ac68c: create_collection gained a Db arg and now auto-creates the org/site/unit DB rows (via sync::ensure_org/ensure_site/ensure_unit, each linked to admin) before writing metadata.json. Existing rows are reused, so re-POSTing a collection no longer fails on missing parents. clarity:backend/src-tauri/src/sqlite_api/api/collections.rs:467-505
DELETE /collections/:id — 11-step cascadeNew in 67ac68c. delete_collection(db, storage, id) tears down everything tied to a collection in a defined order. Steps 2–5 and 10 run in one BEGIN IMMEDIATE transaction (the monitor cascade is inlined rather than calling ConfigManager::delete_collection_cascade, so it shares this transaction); the cache/FS cleanup (6–11) runs after the commit, so a failed remove_dir_all leaves the DB consistent with files possibly remaining.
metadata.json first — capture the tag list and collection UUID.alarm_events → monitor_rules → monitor_collections (by collection_id = UUID).connections.collectionId where it referenced the UUID.tag_resolver::remove_tags(&tags) — drop the tags from TAG_SCOPE_MAP.storage.invalidate_metadata_cache(meta_path).storage.invalidate_mmap_cache(collection_path).storage.invalidate_day_locks_for(collection_path).tagmeta rows whose dataTagId is in the collection's tag list.fs::remove_dir_all(collection_path).The JSON reply reports per-step counts (tags_removed, alarm_events_deleted, monitor_rules_deleted, monitor_collections_deleted, connections_nulled, tagmeta_deleted, day_locks_evicted). clarity:backend/src-tauri/src/sqlite_api/api/collections.rs:595-733
cascade_collections_for_parentcascade_collections_for_parent(db, storage, table, parent_id) is the parent-level (orgs/sites/units) cascade invoked by recursive_cascade_delete. It resolves the parent's on-disk path from the DB (orgs.name / sites.{orgsId,name} / units.{siteId,name} → Base/Org[/Site[/Unit]]), walks the subtree via storage.list_collections_under_subtree, runs a DB-only cleanup per collection (delete_collection_db_only — steps 2–5, 10, no cache/FS side-effects) plus remove_tags, then does one cache sweep for the whole subtree (invalidate_metadata_cache_for_subtree + invalidate_mmap_cache + invalidate_day_locks_for) and finally fs::remove_dir_all on the parent path. It returns a CascadeSummary (totals + collections_failed) and does not delete the parent row itself — the caller's row delete does that. clarity:backend/src-tauri/src/sqlite_api/api/collections.rs:804-819, 908-1054
sync.rssync.rs reconciles the on-disk collection tree with the DB hierarchy tables.
ensure_org / ensure_site / ensure_unit — idempotent upserts that return the existing row id when a row with that name already exists, else insert one (linked to admin; ensure_site/ensure_unit also write customerId alongside orgsId). Used by POST /collections and by the nested-POST auto-collection path. clarity:backend/src-tauri/src/sqlite_api/api/sync.rs:14-165sync_folder_to_db(db, base_path) -> (triples, ensure_org_calls, ensure_site_calls) (new in 67ac68c) — walks {base_path}/<org>/<site>/<unit>/<grid>/metadata.json, collects the unique (org, site, unit) tuples that have at least one grid with a metadata.json, and calls ensure_org/ensure_site/ensure_unit for each (memoising org/site ids within the pass). Idempotent. Called once at startup to recover hierarchy rows for collections created directly on disk — chiefly backup restores — that were never POSTed through the API. clarity:backend/src-tauri/src/sqlite_api/api/sync.rs:167-271 — see API Server § Startup folder→DB sync.tagmeta_hooks.rs)tagmeta_hooks.rs wraps the generic CRUD handlers for tagmeta (and units) so that writing limLo/limHi thresholds also creates/refreshes monitor alarm rules. Each interceptor runs the original handler first and only spawns rule creation (fire-and-forget tokio::spawn) when the write succeeded and a limit is present.
| Route | Interceptor |
|---|---|
POST /tagmeta |
tagmeta_post_intercept |
POST /tagmeta/bulk |
tagmeta_bulk_post_intercept |
PUT /tagmeta/:id |
tagmeta_put_intercept |
PUT /tagmeta/bulk |
tagmeta_bulk_put_intercept |
POST/PUT /tagmeta/update?where={…} |
tagmeta_update_all_intercept (new in a18d35c) |
POST/PUT /units/update?where={…} |
units_update_all_intercept (new in a18d35c) |
Routing. The /tagmeta/update and /units/update interceptors, plus the bulk/single tagmeta interceptors, are or()-mounted before dynamic_routes in create_warp_routes's return chain so they take priority over the generic dynamic CRUD surface for those paths. (tagmeta_crud itself is now just the plain dynamic CRUD; the interceptors are mounted directly in the chain rather than pre-composed into a single tagmeta filter.) clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:111, 1198-1219, 1465-1481
String-or-number tolerance. Limit and id fields may arrive as JSON numbers or quoted strings (e.g. "10.5", "1"); parse_threshold_value and parse_i64_field accept both. clarity:backend/src-tauri/src/sqlite_api/api/tagmeta_hooks.rs:31-49
Identity resolution for /…/update. The bulk-update interceptors need (units_id, dataTagId) to name a rule. They take a fast path when both are in the request body, else fall back to resolving from the where clause:
tagmeta_update_all_intercept parses id from where, then fetch_tagmeta_fields(id) loads the row and reads dataTagId/unitsId from the dedicated columns or the meta_data JSON blob. clarity:backend/src-tauri/src/sqlite_api/api/tagmeta_hooks.rs:108-160, 555-645units_update_all_intercept resolves units_id from where ({"id": N} or {"unitsId": N}) and dataTagId from the body. clarity:backend/src-tauri/src/sqlite_api/api/tagmeta_hooks.rs:600-701fetch_tagmeta_by_where is a helper that resolves matching tagmeta (dataTagId, unitsId) pairs for a simple {"id"|"unitsId"|"dataTagId": …} filter, using COALESCE so values in the meta_data blob are found when the dedicated columns are NULL. clarity:backend/src-tauri/src/sqlite_api/api/tagmeta_hooks.rs:53-106On a PUT that changes an existing threshold, create_monitor_rules_for_tag force-closes any open alarms for the old rule before the new threshold takes effect — see Monitor § threshold-change alarm close.
configurationsEvery other entity uses an INTEGER PRIMARY KEY AUTOINCREMENT id. The configurations table instead uses a TEXT primary key holding a MongoDB-style 24-character hex id, so external references in other seed data (buId, featureId, groupId, roleId) resolve against it. Historically it was wired up through a parallel trait/macro stack rather than the standard CrudModel one — the model half survives; the route half (bottom two rows) was removed with the rest of the route macros and the table is now served by the dynamic layer:
| Standard (i64 id) | String-Id (TEXT id) | Status |
|---|---|---|
CrudModel trait |
StringIdCrudModel trait |
survives (row shape) |
impl_crud! (via register_models! in schema.rs) |
impl_crud_string_id! (in routes.rs) |
survives (invoked from routes.rs) |
crud_routes! |
crud_routes_string_id! |
removed (no routes mounted) |
get_items_warp etc. |
*_warp_string_id Warp handlers |
historical; dynamic layer serves the routes |
configurations is intentionally not registered in schema.rs; it is registered separately via impl_crud_string_id!(Configurations, "configurations"). clarity:backend/src-tauri/src/sqlite_api/api/routes.rs:1-58id TEXT column + a meta_data BLOB (no relations, no extra columns). On insert, the id is taken from the payload id if present, otherwise generated via generate_object_id(); the same value is written both as the PK and into meta_data.id. clarity:backend/src-tauri/src/sqlite_api/api/handlers.rs:51-64clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:977-1244Configurations.id field type changed i64 → String. clarity:backend/src-tauri/src/sqlite_api/models/mod.rs:552-558Migration: db/mod.rs creates configurations with id TEXT PRIMARY KEY. A pre-existing INTEGER-id table is detected via PRAGMA table_info and migrated: rename to configurations_old, recreate with TEXT id, and re-insert each row using the MongoDB id from its meta_data (falling back to the stringified old integer id). Seeding now stores the JSON id as the PK (generating one if absent). clarity:backend/src-tauri/src/sqlite_api/db/mod.rs:441-521
As of
e021b09,configurationsis also inDYNAMIC_TABLES, so the dynamic layer serves it via itsSTRING_PK_TABLESbranch (TEXT-PK handling in the genericget_row/create_row/upsert_row/etc.) rather than the typedStringIdCrudModelstack. The dynamic Warp:idroutes use aStringparam, so by-idGET/PUT/DELETE(andPATCHupsert) now work for the hex TEXT id — see Phase 5b § TEXT-PK:idroutes. The typedStringIdCrudModeltrait +impl_crud_string_id!survive (compiled, invoked fromroutes.rs) as the row-shape definition forconfigurations, but the removedcrud_routes_string_id!route macro means they mount no routes — the dynamic layer serves the table. See The typed CrudModel layer today.
The
Users(capitalised) alias is registered alongsideusersviacrud_routes_admin!(db, storage, User, "Users").clarity:backend/src-tauri/src/sqlite_api/api/warp_dynamic_routes/mod.rs:111
c41a03b — shallow-merge updates, string/array coercion, JSON soft-FK joins, cache removalChanges from PRs #289–#294 ("array+strings", "nikil2", "storage-reduction"). All citations at c41a03b.
json_patch deep-merge → LoopBack shallow-mergeEvery update path replaced meta_data = json_patch(...) with a read-modify-write using a new shallow_merge (dynamic.rs:4184-4222): top-level keys are replaced; nested objects/arrays are swapped wholesale — LoopBack 3 updateAttributes semantics (this is the "fix /update" / "fix sqlite api update request" pair of commits). Converted: dynamic::update_row (:2026-2057), dynamic::update_all (:2275-2409 — now a single-connection BEGIN IMMEDIATE transaction with rollback, fixing the prior N+1 connection acquisition), handlers.rs impl_crud update (:340-391), impl_crud_string_id update (:710-754), generic update_all (:2420-2460), and upsert (:2567).
New route: PATCH /{url}/:id (warp_dynamic_routes.rs:360-369) — the LoopBack updateAttributes id-in-path form, mirroring PUT /:id via update_item_shim (the pre-existing dynamic PATCH was an id-in-body upsert, which broke the LoopBack client contract).
Numeric-string coercion so INTEGER-column binds don't fail with "Invalid column type": new bind_json_value (utils/mod.rs:22-49) and value_to_opt_i64 (:51-64), adopted in push_value (every WHERE bind, :2493-2517), dynamic update_row/upsert_row/update_all FK binds (dynamic.rs:2014,2154,2270), handlers.rs extra-column binds (:284-306,353-370,455-473), and connections.rs create/update (:172-218). process_include/process_include_dynamic FK matching accepts string or int (handlers.rs:1453-1462; dynamic.rs:1535-1575).
Filter operators: new in-memory WhereOp::All array-containment (utils/mod.rs:1098, eval :2200-2216); contains/ncontains LIKE operators (:2231-2312); in accepted as an alias of inq (:1173,1789).
JSON-BLOB columns: is_json_blob_column("incidents","criticalTags") (warp_dynamic_routes.rs:1325) wraps the column in json_insert(..., json(CAST(… AS TEXT))) on the dynamic fast path (:1456-1467), and the incidents streaming path now runs expansion::expand_critical_tags per batch (:1598,1628) — replacing criticalTags ids with full deviation objects (recursing into faultTrees[].indicators[]; unknown id → {"id":N,"error":"Not Found or Alphanumeric"}).
tagmeta) — the "sqlite fx" fixFkRef enum Column(String) | JsonKey(String) (dynamic.rs:2952-2959); lookup_fk resolves in 3 passes — PRAGMA belongs_to → SOFT_FK_EXCEPTIONS → new JSON_SOFT_FKS (:2970-3005).JSON_SOFT_FKS (aliases.rs:227-235) declares tagmeta's orgsId/siteId/customerId/unitsId/equipmentId as JSON-key FKs; list_nested emits an INNER JOIN on CAST(json_extract(child.meta_data,'$.{key}') AS INTEGER) for them (dynamic.rs:2600-2620); create_nested/delete_nested reject JSON-only relations with a clear error.idx_tagmeta_json_orgsid/_siteid/_customerid over CAST(json_extract(meta_data,'$.X') AS INTEGER) (db/mod.rs:1319-1327) — fix >90 s full scans on those nested routes.lookup_relation_dynamic rewritten to 3-phase name matching (override → raw table → case-insensitive) (dynamic.rs:1023-1119).introspect::build_relation_map now merges SOFT_FK_EXCEPTIONS into both belongs_to and has_many so include/nested routes keep soft edges (introspect.rs:370-402).statuses.ingestconfigIdALTER TABLE statuses ADD COLUMN ingestconfigId INTEGER (db/mod.rs:452) + defensive two-way configId↔ingestconfigId backfill (:611-664); soft-FK statuses.ingestconfigId→ingestconfigs (aliases.rs:203); pre_bind_hook_statuses_dual_config_fk mirrors the two fields on write (dynamic.rs:3403,3432); models::Statuses gains ingestconfig_id (models/mod.rs:576,585).
error_to_reply maps Invalid filter / Failed to parse filter / Invalid JSON to HTTP 400 (was always 500); reply_for/reply_unit, list_items_shim, and dynamic_alter_shim route through it (warp_dynamic_routes.rs:1306).get_item_shim now honors ?filter={"include":[...]} via process_include_dynamic (warp_dynamic_routes.rs:1980-2010) — previously ignored on by-id GETs.dynamic_alter_shim streaming rewritten: parse each row once, emit 16 KB byte-buffer chunks (:1560-1640); shim table params are Arc<str> (perf).GET /_tables (warp_routes.rs:1445) and GET /_schema (:1465), mounted at :1558-1559.paths.rs::url_template now always emits :id/{child} even for degenerate length-1 paths — e.g. "configs" → configs/:id/configs (paths.rs:155), a real URL-shape change.delete_collection → storage.invalidate_last_values(...) (collections.rs:755); cascade_collections_for_parent → invalidate_last_values_under(parent_path) (:1033).db.schema_cache is now HashMap<String, Arc<Vec<String>>> (db/mod.rs:180,1410).api/query_cache.rs deleted (the in-memory LRU+TTL result cache and GLOBAL_QUERY_CACHE), along with its only consumer get_items_warp_cached and the handle_rejection recovery handler in warp_handlers.rs. Nothing replaces it as a result cache — the layer now leans on rusqlite prepare_cached, a compiled-regex cache (REGEX_CACHE/cached_regex, utils/mod.rs:66), and the Arc schema cache. The query_cache_max_entries/query_cache_ttl_seconds config knobs are now dead within sqlite_api.models::HeatRate struct + FromRow impl.utils::truncate_display and utils::sync_admin_access (no callers remained).json_patch deep-merge semantics on all update paths (behavioral removal — see above).Last updated: 2026-07-18 from commit 6800acc
Body reconciled 2026-07-18: removed the stale typed-macro /DYNAMIC_TABLESper-path-gate narration (both gone at7948df9/1355333); documented the survivingCrudModelmodel layer + its remaining custom-handler callers (/incidents/raw, connectionsalter, tagmeta/units interceptors) per a direct source re-map.
Range9847dba..c41a03bnote:hooks.rs,validation.rs,indexer.rs,rate_limiter.rs,expansion.rs,attachments.rs,tagmeta_hooks.rs,schema.rs,db_helpers.rs,renaming.rs,common.rs, andsync.rsgained#[cfg(test)]modules only (plusprepare_cachedin sync). Behavioral changes are consolidated in § c41a03b changes below.
Rangec41a03b..45a686enote:tag_mappingscustom routes mounted for the first time (endpoint contract documented above);db/mod.rsgainedinvalidate_schema_cache+CREATE TABLE tag_mappings;dynamic.rsupdate_allreads the PK viarusqlite::types::Valueso bulkPOST /{entity}/updatenow works for both INTEGER and TEXT primary keys (:2339-2363);default_bu()/CLARITY_BUdrives auto-createdbumetadata;aliases.rsresolve_include_keyresolves BelongsTo includes.