Skip to content

The OSINT investigator's workbench. One case, one folder, every tool. Local-first: your media and your investigations never leave your machine.

JavaScript
54.1 KB
Raw Blame

Case storage and performance

This document describes current case storage and the controls that keep large cases responsive. The migration from a monolithic case.json graph to per-case SQLite is complete.

Structured case data lives in SQLite. Images, videos, proof PNGs, drafts, saved Inspect and Compare sessions and note bodies remain ordinary files inside the case folder. Compare GIFs explicitly saved to the case are derived media under media/; an exported copy stays a finished file under exports/ or the selected Views folder.

The shape of the workspace

<workspace>/
  <case>/                    # one visible folder per permanent investigation
  .azimut/                   # hidden application machinery
    bundles/                 # exported bundles and transient imports
    cache/tiles/             # disposable map tile cache
    cache/terrain/           # terrain height tiles, bounded by size, not age
    cache/peaks/             # summit names from OpenFreeMap's tiles, a file a tile
    cache/buildings/         # Horizon's buildings, by source and area
    cache/turns/             # skylines a Find swept, bounded by size
    cache/water/             # where water lies, read for Find's mesh step
    lock                     # held by the one Azimut using this workspace
    runtime/                 # updated scraper distributions
    scratch/                 # complete, promotable one-shot cases
    settings/                # preferences, templates, icons, branding and cookies

Startup migrates the former cases/, scratch/, bundles/, runtime/ and tile-cache/ directories with restartable renames. Permanent cases move to the workspace root; scratch cases move under .azimut/scratch/ and then run through the same case-schema migration as permanent cases before cleanup. Different entries at the same destination stop the move without overwriting either copy.

Where the workspace is

Nothing inside a workspace records where it is: media are stored as media/photo.png, and settings hold keys and preferences. So the answer lives outside it, in a one-line pointer file at the platform's own configuration location — $XDG_CONFIG_HOME/azimut/location on Linux, ~/Library/Application Support/Azimut/location on macOS, %APPDATA%\Azimut\location on Windows. Resolution order is AZIMUT_HOME, then that pointer, then ~/Azimut. The environment variable stays in front so a workspace on a stick keeps working without rewriting the machine's configuration, and a stale pointer cannot stop a run that was told where to look.

A lost or unreadable pointer costs an address, not data: Azimut falls back to the default root, and pointing it at the real folder again is one action. A pointer naming a folder that is not there is the other case, and it stops startup: nothing is created, case routes answer 503, and the browser shows where the folder was expected. Recreating it silently is how someone concludes their work is gone.

One Azimut per workspace

A workspace holds .azimut/lock, and the process that opens it holds an advisory lock on that file — fcntl.flock on Linux and macOS, msvcrt.locking on Windows. The reason it is an OS lock rather than a file Azimut writes and deletes is that the kernel drops it when the process dies, so a crash can never leave a workspace nobody can open. It is taken before startup migrates anything, since two processes renaming case directories at once is the damage it exists to prevent, and it moves with the root when a folder is adopted or a move finishes.

That lock is unreliable on exactly the filesystems this matters for, NFS, SMB and cloud-sync folders, so the file also carries who holds it and a heartbeat rewritten every 30 seconds. The two are read together: a refused lock means someone holds it; a granted lock over a payload from another machine whose heartbeat is under five minutes old means the lock was probably a no-op, so the payload wins; an older heartbeat, or any payload naming this machine, is a corpse to take over. Two machines' clocks can disagree in a way nothing here can fix, so Settings can always overrule the verdict.

The port was never this guarantee. A second instance on the same machine failed to bind 8477 and died, which protects the port; azimut --port 8478 and two machines on one share never noticed. What breaks without the lock is not SQLite, whose own locking and busy_timeout hold on a local disk, but everything around it: settings.json read-modify-written per process loses an API key to the last writer, and startup migrations race over the same renames.

Settings can adopt a folder as it is, or move the workspace into it. Adopting writes the pointer and nothing else, which is what a folder moved by hand needs. A move copies, verifies every file's name, size and SHA-256 while rejecting a source that changes during the read, renames the copy into place, then writes the pointer — that write is the whole switch, so an interruption before it leaves the old folder authoritative and one after leaves the new one, both complete. The old folder is then renamed <name>.old-<date> and kept until the analyst drops it. Nothing under cache/ is copied, being disposable by contract, and a destination that already holds other files gets an Azimut subfolder rather than settling among them. Choosing a root is also where the path budget below is enforced: layout.room_for_workspace_root() refuses a root too long for a case to fit under it on Windows, and names it as a warning elsewhere.

The shape of a case

<case>/           # the analyst's: Azimut writes nothing here but azimut/
  README.txt      # which half of the folder is whose
  azimut/
    case.json     # small manifest: name, dates, storage format, schema, to-do lists, work folder, link-pass mark
    notes.md      # case-wide Markdown note
    notes/        # note bodies, named after their title, filed as in the notebook
    media/        # imported, downloaded and derived media
      .meta/      #   one sidecar per media
      .dl/        #   in-progress downloads (transient)
      .thumbs/    #   disposable thumbnail cache
    sheets/       # CSV tables, one file per sheet
      .meta/      #   what the grid remembers: widths, sort, colours, cell links
    proofs/       # rendered PNGs
      .meta/      #   editable specs and pasted assets
    exports/      # finished exports and the analyst's own files
    .data/        # private structured and presentation state
      case.db     #   entities, links, folders, catalog and jobs (SQLite)
      entity-images/ # private, bounded entity photos and their thumbnails
    .drafts/      # post drafts
    .inspect/     # one Inspect work per file (.v1/ keeps 0.3.0 sessions)
    .collages/    # collage layouts and their previews; the picture is media
    .compare/     # saved Compare session specs
    .horizon/     # saved Horizon views and their previews
      finds/      #   searches for where a photo was taken: the question and every point scored
      .work.json  #   the work in progress no save has kept, brought back on reload
    .analysis/    # saved areas, saved detections, runs and the frames behind their results
    .search/      # saved Grid Search state
    .layers/      # added map layers: spec, snapshot, icons
      .cache/     #   parsed GeoJSON, rebuilt from the snapshot
    .mapplates/   # map plates: the layout round the case's maps, and its preview
    .trash/       # deleted artifacts grouped by delete action, in numbered slots

Paths are stored relative to azimut/, not to the case folder: a media is media/x.png in the graph, in its sidecar and in a bundle. That is what let the wrapper arrive without rewriting a stored path, and what keeps bundles written before it importable after it.

Visible means openable and useful in another program; a dot-directory means only Azimut reads it. ensure_dir adds FILE_ATTRIBUTE_HIDDEN to those, since a leading dot means nothing to Windows Explorer. The attribute belongs to the directory rather than to its name, so a rebuilt tree loses it: opening a case puts it back over layout.hidden_dirs(), which is what covers a workspace copied from another machine.

layout.py owns this shape. It answers where each file goes for a given case root and carries the path budget — the caps on case, media, document and folder names that keep a case's longest path inside Windows' 260-character limit. tests/test_layout.py recomputes the budget from those caps and fails when a new directory level or a raised cap breaks it.

case.json is the discovery manifest: name, creation date, and the storage and schema fields, plus two pieces of case metadata that own no files, the to-do lists and the work folder. It no longer holds the graph, so the case switcher can identify a case without opening its database. case.db is the source of truth for mutable structured state (entities, links, folders, jobs). The files under media/, proofs/, sheets/, .drafts/, .inspect/, .collages/, .compare/, .horizon/, .analysis/, .layers/, .mapplates/ and notes/ are the source of truth for their own content.

App-wide preferences stay outside cases under <workspace>/.azimut/settings/: settings.json, templates.json, signature.png, the icon library in icons/ and the optional cookies.txt. Startup moves the former workspace-root or .settings/ files there with independent atomic renames, so a partial move resumes. If both locations differ, the current hidden file wins and the legacy copy is retained beside it under a .legacy name.

For media, proof PNGs, notes, drafts, Inspect works, collages and Compare sessions, the human-readable filename stem is the name Azimut shows. Spaces, case and Unicode survive; characters forbidden by Windows are replaced and the returned canonical stem is written back to the UI. Renaming moves the file and its companions, rewrites exact stored paths in the graph, jobs, sidecars and tool specs, and rebinds the same entity. A rename onto a name another item holds is refused before anything moves.

Every mutation used to re-read and rewrite the whole case.json under a per-case lock. At 10k entities that rewrote megabytes per edit and shipped the entire graph to the browser on open. SQLite removes both ceilings: a single edit touches one row in one short transaction, and case open ships a manifest instead of a graph.

Source of truth

case.db owns the graph. A case bundle is the portable case archive: a cleaned database plus declared files under a SHA-256 manifest. Rollback journaling leaves no WAL checkpoint outside the case folder. Media, proofs and note bodies remain inspectable files.

Case Doctor

The case switcher can inspect a permanent or scratch case without opening its database. Discovery reads case.json, so renaming the outer case folder does not orphan it. Diagnosis is read-only and reports:

  • a missing or unreadable case.db;
  • media and capture entities whose file no longer exists;
  • visible files placed directly in media/ without a graph entity;
  • a temporal projection that no longer matches Claims or media metadata.

Repairs are separate API actions. A deleted database is rebuilt in a temporary SQLite file from media sidecars, note files and proof specs, then renamed into place only after the rebuild completes. Relations, catalog folders and database-only provenance cannot be recovered and the dialog says so first. Missing media can be removed through the normal Trash path or rebound to an unregistered file already inside media/. An unknown file goes through the normal media registration pipeline, gaining a hash, sidecar, index row and provenance. A stale temporal projection can be rebuilt from its authorities. None of these actions runs at startup, on case open or during a diagnostic request.

Ownership rules

Each value has one owner. Cached copies may exist, but they are disposable and carry enough information to detect staleness.

Data Owner Notes
Case name, storage version, to-do lists, work folder, link-pass mark case.json Small manifest, atomically replaced; the mark is how far the link pass has read
Entities, links and folders case.db Transactional and versioned
Entity photo galleries case.db (entity_images) Ordered private photos or Media references, with one primary
Private entity photo bytes .data/entity-images/ Bounded JPEG plus dedicated thumbnail; never indexed as media
Entity labels and notes case.db Indexed for catalog search; file-backed labels mirror their filename stem
Media visible name Media filename stem Mirrored as title to the sidecar, graph and browse index
Media notes and folder Media sidecar Mirrored to the graph and browse index
Image, video and audio bytes Filesystem Never stored as database blobs
Proof and session content Their JSON/PNG files Registered as artifacts in the database
Acquisition provenance Media sidecar Immutable after registration
Media browse fields case.db (media_items) Search index mirrored from sidecars
Background jobs case.db (jobs) Durable, recoverable, one worker
Trash journal case.db (trash) One row per delete action; payload loaded only for restore
Deleted artifact bytes .trash/<group>/ Numbered slots paired to their origin by the journal, retained until purge
Thumbnails media/.thumbs/ Cache; safe to remove at any time
Video overviews media/.thumbs/ Inspect's hover strip, waveform and last scan; cache, safe to remove
Note bodies notes.md, notes/<folder>/<title>.md Graph keeps only id, title, folder, path; uniqueness is per folder

Packaging and frozen-binary constraints

The same storage layer serves pip install azimut and PyInstaller binaries for Windows, macOS and Linux:

  • Bundle encryption uses cryptography. Intel macOS resolves the latest compatible 48.x universal wheel; other platforms resolve 49.x. Both ranges are bounded below the next untested major. SQLite remains the stdlib sqlite3 module.
  • Feature availability is per-binary, not per-Python. sqlite3 links whatever SQLite the build environment provides, so FTS5, JSON1 and RTree can be present on the dev machine yet missing from a shipped binary. Nothing beyond core SQL enters the contract without a runtime probe and a fallback beside it:
    • find_entity scans and matches attributes in Python.
    • The Board's field filter (attr/value) is the one JSON1 caller, behind _has_json1(). Present, json_extract compares the top-level field; absent, the same question runs as LIKE over the exact text json.dumps wrote, which matches the field at any depth — a nested object holding the same key and value matches too. tests/test_repository.py asserts the probe answers true, so CI proves on all three platforms and the 3.11 floor that the exact path is the one that ships, and exercises the fallback on every platform by forcing it.
    • The field/value menus behind that filter (attr_facets) scan in Python, so a menu never depends on an extension. Any further indexed projection (geo, time, full-text) follows the same rule.
  • case.db lives under the workspace root, inside the case folder, never beside the frozen executable, which may sit on read-only media.
  • Dev-only tooling stays out of the artifact. The synthetic fixture (tests/bigcase.py) lives outside src/azimut/, so hatchling never packages it, and uses only the standard library.

The storage boundary

repository.py defines CaseRepository, the one interface every tool and route uses to read and mutate a case's graph, catalog and jobs. Nothing outside the implementation reaches into a raw case.json shape. workspace.Case is the filesystem shell (manifest, notes, media, lifecycle, path resolution) and delegates every graph, catalog and job method to a SqliteCase (sqlite_backend.py) over case.db. That delegation is casestore.CaseStore, mixed into Case: every method there forwards and does nothing else, so what stays on Case is the half that also has a folder to keep in step. engine/links.py and the api/cases/ routers read through get_entity / links_of / snapshot, never the file.

sqlite_backend.py owns the schema, the connection and SqliteCase. What that class is built out of lives in store/, one module per concern: statement shaping (sql), the predicates a case is narrowed by (filters), keyset cursors (cursors), the denormalised columns a record is indexed on (rows), the derived temporal projection (temporal) and the versioned schema upgrades (migrations). The case-folder reshaping pass is caselayout.py, beside workspace.py.

snapshot() returns the whole graph for delete planning, export, migration checks and test assertions. overview() is the case-open view: the manifest and folder list, without the entity/link arrays. Direct storage access outside Case/SqliteCase should fail review.

Database shape

case.db is at SQLite schema 19: schema 8 adds nullable links.confidence, schema 9 rebuilds every row's search_text, schema 10 stores graph pins per lens, schema 11 adds links.nature, schema 12 adds entity photo galleries, schema 13 adds saved analysis views with their bounded-list count, and schema 14 indexes the two columns the catalog orders the whole case by. Schema 15 adds the rebuildable temporal projection, schema 16 extends saved analysis views to Timeline recipes and snapshots, schema 17 rewrites temporal bounds to fixed microsecond width, and schema 18 rebuilds the projection once more, now that a proof's stated date is projected beside the dates its files carry. Schema 19 folds the entity and media search indexes, adds subject merge redirects and undo records, and gives the temporal projection a tz column for the zone a value was stated in. These changes share one migration from released schema 18; the case manifest stays at 11. The schema counter is independent of the JSON CASE_SCHEMA: the manifest's azimut.storage field selects the backend, and each format counts its own shape upgrades.

The case manifest is at CASE_SCHEMA 11. Schema 3 is the last released folder shape before the azimut/ boundary; schemas 4–8 were development checkpoints and never became public formats. FolderMigration therefore declares a target schema instead of assuming +1: every schema-3 case and any development case at 4–8 runs the same idempotent normalizer and is stamped 9 only after the whole layout and visible-name contract are valid. A stopped migration keeps its older stamp and resumes the same normalizer on the next open. Schema 9 is what 0.3.0 wrote; one step takes it to 11, giving each file one Inspect work, then dating every kept Detect pin (analysis_dating.backfill) and standing every saved comparison on the map (comparisons.backfill). Schema 10 was a development checkpoint and runs only those last two. Media moves additionally use .data/rename.json, so a restart can finish references after the bytes moved.

Tables

entity_redirects : Absorbed id, surviving id, original label, optional coordinate key, merge id and timestamp. Chains are compressed when another merge moves the survivor. These rows travel in bundles when their target is still present; the merge id is cleared.

entity_merges : Local undo journal containing the absorbed entity, original links and photos, graph positions, changed live views, survivor fields and sheet edits. Completed records remain until Undo or permanent deletion. Bundles omit this table's rows.

meta : Schema version, case name and timestamps.

schema_migrations : Every applied SQLite-schema migration and its completion time.

entities : id, type, label, attrs_json, an indexed folder (denormalised from attrs.folder), denormalised search text, provenance fields and status. Search covers label, type, folder and notes. label (under NOCASE) and prov_at are indexed as well, because the catalog orders the whole case by them. Unknown types and attributes stay valid.

links : id, source, target, type, provenance, status and a nullable confidence. Foreign keys forbid a link to a missing entity. The delete policy lives in the dependency-aware service (engine/links.py), not in a blind SQL cascade. confidence is the closed ordinal used only by ratable ordinary relations; NULL means not assessed. Claim confidence lives in the Claim entity, while Claim connectors, mentions, lineage and same-image-as reject edge ratings (ONTOLOGY §3).

graph_pins : Saved graph coordinates keyed by entity and lens. They are presentation state and cascade when the entity is deleted.

entity_images : Ordered private photos or references from supported hand-made entities to image media or capture entities. A partial unique index permits one primary image per entity. Direct computer imports are bounded JPEGs under .data/entity-images/ and create no Media entity or browse-index row. A Media reference owns no duplicate bytes; its original media record remains the owner.

analysis_views : Case-owned live recipes and immutable Board, Graph or Timeline snapshots. List reads carry metadata and snapshot_count, never the snapshot payload. A live recipe stores Search+ and presentation state, including view-local graph pins and camera, and autosaves after edits. Timeline recipes store their ordered track queries, folds, grouping, visible categories, hidden and pinned entries, entity scope and UTC window. An entity snapshot stores copied entities, provenance, closed relations and embedded bounded preview thumbnails. A Timeline snapshot stores the matching temporal rows and exact track assignments. Neither consults current graph rows or files when read.

folders : Normalized /-separated logical paths for the analyst's organisation. Not filesystem directories, not semantic links. Every entity write registers the folder it carries and its parents, and the list read adds any folder an entity sits in, so a folder exists whichever door it came in by.

jobs : Durable local background work: id, kind, an optional job_key, state, attempts, max_attempts, payload_json, error and timestamps. See "Thumbnails and background jobs".

trash : One head row per delete action (id, time, label, type, item count and byte size), a recovery state and a JSON restore recipe. Sidebar listing reads only completed groups and never loads the recipe.

media_items : Queryable browse metadata mirrored from each media sidecar. It indexes path, kind, folder, display name, size, date, source, imagery mode, the media entity it belongs to and whether the file states a position (has_gps). The coordinates themselves travel in the mirrored sidecar JSON and need no column; presence does, because "only the files that carry a position" has to filter over the whole case rather than over the page the client holds. Enrichment's full metadata dumps (exif, video_metadata — hundreds of rows per file) are the one thing the mirror leaves out: this index is read 200 rows at a time by the grid and whole by the pickers, and one fat field would multiply every one of those responses. A surface showing a single file reads it from the sidecar through GET /api/cases/{id}/media/item. The original files and sidecars remain the file-level records.

temporal_items : Rebuildable, homogeneous rows for dated Claims, intrinsic media dates and case activity. Each row keeps the raw value beside normalized half-open bounds, precision, timezone kind, the named zone it was stated in (tz) and uncertainty flags. Claims in entities.attrs, indexed media sidecars and entity provenance remain authoritative. Deleting this table's contents and running rebuild_temporal_projection() recreates the same rows. Window/category/owner indexes let Time and Timeline page without scanning entity JSON or opening sidecars. Each page also returns the complete dated extent for the selected categories and entity scope, independent of the visible window, so the UI can reset and position its density overview without loading every row. Density buckets are cut by year, month, day or hour, and include their per-category counts plus the span of their own entries, so the minimap can stack its columns and open a clicked one onto what it holds without another query. A track query applies the shared Search+ entity predicate through the temporal owner, subject, place or evidence connector, plus time-role and view-local hiding. Its counts distinguish an absent date (undated) from a raw local or invalid value that cannot be positioned yet (unplaced). A caller drawing the page on an axis asks for a spread read (spread=true): the same ordering is cut into stride = ceil(total / limit) interleaved slices, one per page, so the first page samples the whole window at the density the overview draws instead of stopping wherever the front of the window runs out of room, and later pages fill the sample in without a repeat or a gap. Chronological cursors and phase cursors are not interchangeable.

The same table answers the fact-time window Board, Graph and their saved views filter by. It is one EXISTS over the window index beside the catalog's other terms, reaching a row either as the owner of a temporal entry or through an about/at/cites edge from one, so the period narrows the page, the totals and a capture through the same predicate rather than through three readings of it. The boundaries are read once, at the edge, and a reduced date names its whole civil period on the upper end — a saved 2024-03 is the whole of March wherever it is asked from.

Indexes and migrations

Indexes cover entity type/status/folder, link source/target/type and media browse fields. Job state and a partial unique index on (kind, job_key) keep a keyed job from being enqueued twice. SqliteCase.open upgrades an older case.db in place through _SQLITE_MIGRATIONS, each step in its own immediate transaction, re-reading the version inside the transaction so a raced second opener applies nothing; a newer schema is refused rather than mangled. The shipped migrations add the indexed folder column (1→2), the jobs table (2→3), entity search text and the media browse index (3→4), the has_gps flag (4→5), the trash journal (5→6), interrupted-operation recovery state (6→7), relation confidence (7→8), search-text rebuilding (8→9), graph pins (9→10), relation qualifiers (10→11), entity galleries (11→12), saved analysis views with their denormalised snapshot count (12→13), the catalog's ordering indexes (13→14), and the temporal projection with a full authority backfill (14→15). Schema 16 rebuilds the analysis-view table with Timeline as an accepted surface while preserving every existing view. Schema 17 rebuilds the temporal projection so whole seconds and sub-second values share one sortable width. The 4→5 backfill reads the sidecar JSON already held in each index row, so a case enriched before the column existed gains its position filter on open with no file scan.

The schema-4 media backfill scans sidecars once on first open. Its completion marker and index rows commit together, so an interrupted backfill retries safely. Normal media creation, edits, thumbnail changes and deletion keep the index in sync. The same writes refresh the media-owned temporal rows. Entity create, update, delete and Trash restore refresh or cascade the entity-owned rows. Bundle databases carry the projection, while an explicit rebuild after import remains safe and idempotent.

Trash and artifact ownership

engine/artifacts.py is the single declaration of files owned by each entity type. Delete writes a recovery journal before moving files to .trash/<group>/ and removing graph rows. The group directory is flat: each file waits under a number, and the journal's slots list says where it came from, so the trash never stacks a second copy of the case tree under itself. Restore refuses occupied destinations, then restores the original entity ids, surviving gallery attachments, links and tombstones. An interrupted delete is rolled back or published on the next case open; an interrupted restore is completed. Shared thumbnails do not move; they are discarded and queued again after restore. Purge removes the journal only after its directory is gone, so a Windows file lock leaves a retryable group instead of orphaned bytes.

Saved analysis views are case state too. Deleting one writes its full recipe or capture to Trash, and restore reinserts the same id. Snapshots are bounded to 2,000 entities, 10,000 closed relations or 5,000 temporal rows, and an 8 MiB JSON spec. Embedded previews are thumbnail copies, at most 512 KiB each and 4 MiB total; they are visual context, not original evidence and not a cryptographic attestation. There is no standalone view file. Case bundles carry the analysis_views rows with the rest of case.db, so transmission cannot separate a reading from the case it describes.

Case bundles

Exports run as bundle-export jobs and write atomically to <workspace>/.azimut/bundles/, then stream to the browser as an attachment. The first ZIP member is bundle-header.json; the last is bundle.json, which lists every other member with its size and SHA-256. The database is copied consistently, its trash and every job are removed (a finished export or import still names folders of this machine), and it is vacuumed before packaging. .trash/, media/.thumbs/, media/.dl/ and SQLite's journal beside case.db never travel; a journal an older export packed is verified and then dropped on import. Export also reserves filesystem headroom before writing its temporary output.

Bundle format 2 carries the complete case under a case/ member prefix: case/azimut/ for application state and every regular entry beside it for the analyst's free zone. The prefix keeps the bundle metadata outside that free zone, so even analyst files called bundle.json or bundle-header.json round trip. A case-root name that case-insensitively collides with azimut/ is refused, as are symbolic links on both export and import. A folder in azimut/ that this version has no bundle decision for is work a newer Azimut wrote there, so the export refuses rather than leave it out. There is no fixed size ceiling: exports and imports use current free space plus a safety reserve, and the import dialog warns when the temporary requirement reaches 10 GiB.

Password protection encrypts the complete ZIP in 1 MiB AES-256-GCM chunks. The key comes from scrypt (n=2^15, r=8, p=1), and only the envelope parameters remain clear. Passwords live in a process-memory map keyed by job id, never in the durable payload.

Import pre-flights format and database versions before creating a destination. A refusal of a bundle made by a newer Azimut names that version, so the analyst knows which one to update to. Jobs never travel, so a Horizon search that was under way lands stopped, keeping what it had found. The browser file picker uploads to <workspace>/.azimut/bundles/.imports/; rejected and cancelled uploads are removed immediately, completed imports remove their source, and abandoned uploads expire after 24 hours. It verifies unique safe member names, declared and actual byte counts, archive member count, and rejects symlinks. It extracts only the manifest allowlist after each hash matches. There is no small fixed case-size cap: preview and import compare the required temporary space with current filesystem capacity, keep a safety reserve, and warn for imports above 10 GiB. Work is staged under <workspace>/<id>.incoming/; the existing shell and completed staging directory are swapped by rename for Windows compatibility. Both carry an .azimut-importing mark until the swap, so the switcher leaves them out and the next start removes any a crash left behind. Every import creates a new case and records origin_case_id plus imported_at in database metadata. Entities, links and entity photo galleries keep their original ids and state.

Geographic, temporal and full-text projections are deferred until a query needs them. Each projection requires its own migration and tests.

Connection policy

  • Foreign keys on every connection; a bounded busy_timeout instead of failing a short write immediately.
  • Transactions are short. File hashing and ffmpeg work happen outside them.
  • Rollback journal with synchronous=FULL (not WAL): the app is single-user and portability matters more than write concurrency, so a plain folder copy of a closed case is always complete with nothing to checkpoint.
  • A fresh connection per operation, closed before any rename, so Windows' rules for open files and directory replacement are respected. Writes run in one BEGIN IMMEDIATE..COMMIT and roll back on error.

Automatic migration of legacy cases

Legacy json cases still open. Case.open runs migrate: it applies the json-shape migrations up to JSON_SCHEMA, materializes any inline note bodies to files, then convert_json_to_sqlite builds case.db and the manifest is flipped to {"schema": 3, "storage": "sqlite"} last. A crash before the flip leaves the legacy json case active. A case.pre-migrate-v<n>.json backup is taken once before the first rewrite and never overwritten, so the conversion is recoverable.

The converter builds case.db.tmp, imports the whole graph in one transaction, runs foreign_key_check and integrity_check, then atomically renames into place; any failure removes the temp file and leaves the target untouched. A link to a missing endpoint is reported (MigrationReport.missing_endpoints) and dropped, never erasing an entity. Recorded media hashes are imported as-is; migration does not rehash large videos; an integrity scan is a separate, explicit action.

The live in-file JSON graph backend has been removed. Case.create always makes a case.db; no code path writes an entity/link graph back into case.json. The only JSON code left is the one-way importer and the on-open migration.

Bounded catalog API

SQLite only helps the interface when queries and rendering are bounded. Case open ships overview() (manifest + folders), and the catalog loads through cursor-paged endpoints:

  • GET /api/cases/{id}/catalog/entities returns {items, next_cursor} in stable insertion order, with a clamped page size and server-side filters: a comma-separated type set, status, q over label/type/folder/notes, folder (unfiled=true or an exact path, with optional descendants), one stored field holding one value (attr + value), having a neighbour of a type (linked) or none at all (unlinked), and how the row got here (since, until, a comma-separated by). One predicate answers all of them and the graph's own ranking, so the board and the drawing cannot mean two things by one filter.
  • order sorts the whole filtered set rather than the page — created and label, each with a - prefix for the other direction — because the newest in this case and the newest of the hundred rows loaded are different answers and only the first is worth having. The default cursor keys on rowid, so a background import appending rows never shifts a page already scrolled past and a deletion before the cursor never skips a live row; an ordered one keys on its sort column and the rowid, spelled <rowid>:<key>, so a hundred rows filed in the same second cannot tie the paging into a loop. Both sort columns are indexed (schema 14), so an ordered page walks an index instead of sorting the filtered set in a temp B-tree; tests/test_repository.py reads the query plan and holds it there. events (the Board's Most noted) is the one ordering no index can serve: the count of Claims naming each row. It is refused without a type set, so what it costs is one Board group's rows, each an index search on links.to_id, and the same test holds that plan too.
  • counts=type adds by_type to a page, the matching count per type under the same terms, which is how the Board sizes every group of an answer in one request.
  • POST /api/cases/{id}/catalog/events takes a page of ids (200 at most) and says, per id, how many Claims name it, when the dated ones fall, how many sources and places they reach, and twelve buckets over the case's own span of dated Claims: the Board reads it once per page of subject rows, and Details once per entity.
  • GET /api/cases/{id}/catalog/summary returns {total, by_type, by_status, by_folder, by_source, linked_to, unlinked, countable, lacks} without shipping the graph. lacks prices the two Claim questions, no source and not assessed. linked_to counts entities that have a neighbour of each type, which is not how many of that type the case holds: it is what the filter menu has to price itself with, and the other number looks like an answer without being one. countable is how many statements would draw a line if the case were added up — carrying a number, about something — which is what dims the Board's Totals view before it can answer.
  • GET /api/cases/{id}/catalog/tally adds those statements up per subject over the same narrowing terms as the catalog page, bounded at 2 000 statements and reporting the cut. GET /entities/{id}/tally is the same arithmetic for one subject, which is what the Details panel reads.
  • GET /api/cases/{id}/entities/{id}/chain (neighbour derivation), GET /entities/lookup (one entity by attribute), and GET /entities/{id}/derivation (transitive derived-from closure) are the bounded single-entity reads, each built on links_of rather than the whole graph.

The sidebar pages the catalog through buildCatalogQuery and uses a generation guard, so a stale page never lands after a case or filter switch. fetchAllEntities walks the pages server-side for the whole-slice cases and accepts an AbortController signal; lookupEntity and fetchDerivation cover the single-entity and closure cases. These helpers live in frontend/src/lib/catalog.js. The sidebar, Files, Notebook, Satellite, Media Library, Inspector and the composers use them; none loads caseState.current.entities/.links, which no longer exist on the case-open response.

Deferred to their first consumer: date filters (a timeline filter), links pagination (a relations/graph view), and ranked full-text search (gated on per-binary FTS5).

An id set larger than a statement can bind

SQLite's compiled variable ceiling is 999 on the oldest builds still shipped, so _ID_CHUNK caps an IN (…) at 250 ids and _chunks splits a wider set. That is the right answer for every question a chunk can settle alone — which of these entities exist, how many edges does each of these carry — and it costs one statement per chunk, linear in the set.

It is the wrong answer for the closed edge set, which is what the graph draws: an edge with both ends in the set has ends that can fall in two different chunks, so no chunk holds it. Asking every pair of chunks closed that hole and bought a quadratic one — 9 statements at 650 nodes, 400 at 5 000.

_scope_table puts the ids in a TEMP table keyed on id, and the closed set becomes a join on that key: one statement at any size, binding no ids at all. Measured on a 5 000-node star, 133 ms → 28 ms, and linear rather than square; at one chunk the two are within a tenth of a millisecond. Temp tables belong to the connection and _connect opens a fresh one per operation, so a scope cannot leak into the next read. tests/test_repository.py traces the statements a read runs and holds it to one.

Thumbnails and background jobs

Thumbnails are disposable pixels: a broken or missing one never blocks access to the original. engine/thumbnails.py owns their whole lifecycle, and the durable jobs table is the general background-work model behind it (EXIF, OCR and transcripts will reuse it).

Cache identity, atomic generation

A thumbnail's file name folds in the original's SHA-256 and the generator version (THUMB_GEN): media/.thumbs/<sha[:24]>-g<gen>.jpg. A changed original or a bumped generator therefore maps to a new file rather than serving stale pixels; the superseded ones become orphans that repair sweeps. Pixels are rendered to a unique temp file, validated, then renamed into .thumbs/. Readers never see a half-written thumbnail, and the Windows rename rules hold. Images decode through the process-wide Pillow pixel clamp.

Inspect keeps a video's overview in the same directory under the same identity (VIDEO_CACHE_GEN): <sha[:24]>-strip-g<gen>.jpg holds the hover pictures, one per second up to 120, -overview-g<gen>.json their layout and the waveform, and -scan-g<gen>.json the last shot and sharpness scan. The overview is made when the analyst opens the video in Inspect, from one keyframe-only decode and one 8 kHz mono audio decode reduced as it streams; the scan only when Scan is pressed. repair counts all three as referenced while the video is in the case, and the budget prunes them with the thumbnails.

Inline for cheap, queued for heavy

A registered image gets its thumbnail rendered inline with Pillow. Videos use the CPU-heavy ffmpeg path and are queued, as are images whose inline render fails. Jobs are keyed on media path, so retrying or regenerating does not stack duplicates.

One worker, recoverable

A single background worker drains thumbnail jobs, so only one ffmpeg process runs at a time. Work starts only from a user action (an import, a regenerate) or crash recovery, never from merely opening a case or tab. A job lifecycle is queued → running → ready, or failed once its retry budget is spent, or cancelled (its media is gone). A job left running by an interrupted process is reclaimed to queued (or failed) on server startup and whenever the workspace root changes (workqueue.recover_all, which runs Case.recover_jobs on every case), so work resumes instead of stalling.

Budget, repair, retry states

prune_cache evicts least-recently-used thumbnails (by mtime) past a size budget; repair removes abandoned temp files and orphaned thumbnails no live sidecar references. Both only ever touch the cache, never originals or database rows. A content-addressed thumbnail can be shared by identical-bytes captures, so deleting one media file drops the cached thumbnail only when no surviving sidecar still points at it.

The media listing and the satellite capture listing both tag each item with a thumb_state (ready, queued, running, failed, or none). The Media Library renders the image when ready (lazy-loaded, async-decoded, with an onerror fallback to the type icon that reports once and does not retry per render), a "Generating…" placeholder while queued, and a retry affordance on failure. POST /api/cases/{id}/media/thumbnails/regenerate re-queues one item (the per-card retry) or every missing/failed one; the grid polls the listing while anything is pending, and stops on its own once nothing is.

Every picker cell shows the cached thumbnail or a placeholder, never the original: a proof panel picker rendering full-size captures in 150px cells was the slowest surface in the app. The proof pickers poll the listing while anything is pending, like the grid does.

Entity galleries reuse the same thumbnails. Details constrains the main image, while the Board, sidebar and graph read only the primary thumbnail and fall back to the entity glyph when none exists.

A saved proof's export is thumbnailed through the same cache: POST /cases/{id}/proofs renders one from the bytes it just wrote and records it in the spec, and GET /cases/{id}/proofs backfills any proof that has none (saved before this existed, or evicted) at the cost of one hash of the export. The open dialog falls back to the export itself when no thumbnail could be produced. Proof thumbnails count as referenced by repair, which otherwise sweeps anything no media sidecar points at.

GET /files/{case}/{path} serves with an ETag and answers a matching If-None-Match with a 304, so reopening a picker revalidates instead of refetching. Thumbnail URLs fold in the content hash and the generator version, so they can never change meaning and are served immutable — the browser stops asking for them at all.

A media file keeps the name it arrived with, and a download can land as #3deenero2025.mp4, so the path is percent-encoded per segment before it reaches this route: raw, that # opens a fragment and the request arrives as the directory. lib/fileUrl.js is the UI's only builder for these URLs, and notes store them encoded (the PDF export unquotes them back to a path).

The Media Library and Files browse media through GET /api/cases/{id}/media/page, a bounded SQLite query with q, kind, source-derived category, folder, sort, limit and cursor. It returns total plus full-result kind, folder and category facets. Large cases load one page and search without opening or sorting every sidecar. Files requests thumbnail, kind and size metadata only for paths it can render through POST /api/cases/{id}/media/metadata. The unbounded GET /api/cases/{id}/media stays for consumers that genuinely need the whole index, such as composer pickers, satellite crops and derivation traces.

The Map's Saved panel opens on GET /api/cases/{id}/satellite/index: one compact row per place, capture and filed screenshot — id, kind, title, coordinates, geography, thumbnail, provider, dates, notes, how precise the point is (radius_m, footprint), and nothing else. The tree, the search modal and the map overlay all read that one response, so hundreds of saved items cost tens of KB instead of every capture's full media row and full-size image. Rows come newest first on a single normalised timestamp, and the endpoint makes no network call.

footprint is the one field whose size is not fixed per row: it carries the place's own geometry so the overlay can draw a traced area rather than a pin, and entities.MAX_FOOTPRINT_POINTS caps it at 4 096 vertices. Nothing traces one today — the field is API-only until the drawing gesture ships with the map engine (SPEC §6, v2) — so in practice the row is as compact as the list above. That gesture is when to decide whether the index keeps carrying whole geometries or sends a bounding box and fetches the shape per place. Geography rides on the entity's attrs.geo (ok/nocoords/nocountry/failed); continent is never stored, it is derived from the country code at read time (engine/continents.py), so correcting that table repairs existing cases with no migration. Countries that span two continents (Russia, Kazakhstan, Turkey) are split by the point's own coordinates, so a Vladivostok capture files under Asia. Saving resolves the country inline, bounded by a short timeout; POST /api/cases/{id}/satellite/locate?limit=N backfills up to 25 at a time, waiting ~1.1s between lookups for Nominatim's rate limit, and reports remaining so the client can loop. Progress is the stored geography itself, which makes the pass resumable and idempotent.

Filesystem and database consistency

A subject merge writes entities, links, galleries, graph positions, live views, redirects, temporal rows and its undo journal in one database transaction. Preview uses the same operation in a rolled-back savepoint. Incompatible links or cycles refuse the whole merge. Parallel relations with different confidence, nature or provenance remain separate. A snapshot is never rewritten.

Sheet sidecar edits are recorded before the database commit, then applied with an atomic file replacement. Case open resumes pending edits, including interrupted Undo. The CSV and Notebook text are untouched. Subsequent sheet edits are preserved and reported when they prevent restoration. A pending sidecar write blocks another merge or bundle export until it can finish. Direct photos keep their existing paths; their database owner changes. Purging a survivor removes its redirects and merge history. Doctor reports redirects whose target is neither live nor in the Trash.

SQLite cannot atomically commit a filesystem rename, so file-backed operations are recoverable. Creation produces a file under a unique temp name, validates it, renames it to its final path, then registers it in a short transaction. Deletion removes the file and sidecar, then settles the database. Thumbnail generation follows the same temp-then-rename discipline. The rollback journal means a copied closed case is always complete.

Direct entity photos follow the same boundary without entering the media pipeline: Pillow applies the process-wide pixel clamp, EXIF orientation is normalized, the display copy is capped at 2048 px and its thumbnail at 512 px. Detaching one removes both private files. A Media Library choice only removes the reference.

Performance

The deterministic large-case fixture (tests/bigcase.py) builds entities, links, media, nested folders, notes, suggestions, unknown types, mixed thumbnail states, proof/post/Inspect artifacts, missing files and tombstones. Storage and release tests open that fixture through the SQLite migration boundary. The same arguments produce byte-identical graph rows.

Interaction budgets on the reference machine:

Operation Target
Open case and render the first useful page, cold 2 seconds or less
Open case, warm filesystem cache 750 ms or less
Return a warm filtered/search page 300 ms or less
Update one metadata record 200 ms or less
Mounted catalog rows/cards 300 or fewer
Default CPU-heavy background jobs 1 at a time

These are interaction budgets, not release claims. Shared CI hardware varies, so the automated suites enforce bounded queries, result sizes, and mounted-row limits instead of machine-specific timings.

The map keeps to work counts the same way, each one a lag that was measured and removed (2026-10-23):

On the map Held to
A point of a layer on the 3D view one projection a frame, whatever layers stand on it
A feature a layer's filter refuses at every zoom never weighed
A dot that paints nothing (a click target) placed for clicks, never drawn
Names in view measured all, written only once placed; placed a few times a second, not every frame
The flat map under the 3D view not drawn again while the globe moves
A personal layer read back unchanged not handed to the engine again
A free provider's imagery on a flat map asked in batches, a handful of requests a screen
Turning the map its address written once the turn ends

How it is verified

  • Graph contract (tests/test_repository.py): entity/link/folder CRUD, dedupe, sync_links id preservation, folder subtree removal, cursor paging and summaries, the derivation closure, and the durable job queue (idempotent enqueue, claim, retry-then-fail, recover, prune) held against Case (SQLite-backed).
  • Store specifics (tests/test_sqlite_backend.py): create/open, newer-schema refusal, foreign keys, rollback, the in-place schema upgrade through every migration, keyset paging, media browse search/facets, and the atomic converter (roundtrip, dangling-link report, failure leaves no db, large-case integrity).
  • Entity photos (tests/test_entity_images.py, tests/test_trash.py, tests/test_bundles.py): supported types, primary promotion, API validation, no Media row for computer imports, bounded output, catalog and graph thumbnails, Trash restore and bundle round trips for both storage forms.
  • Migration (tests/test_migrations.py): legacy json → sqlite on open, backup recoverability, a failed activation leaving the json case usable, forward-compat refusal.
  • Thumbnails and jobs (tests/test_thumbnails.py, tests/test_media_api.py): inline vs queued, atomic generation with no partial/temp on failure, content-key by generator version, drain + retry-then-fail, cancel on missing media, LRU budget eviction, orphan/temp repair, shared-thumbnail delete safety, the background worker, startup recovery, and the thumb_state + regenerate API.
  • File serving (tests/test_files_api.py): revalidation to 304, immutable thumbnails, and an edited original invalidating its own ETag.
  • Proof exports (tests/test_proofs_api.py): a thumbnail on save, the listing backfilling an older proof and recording it, the fallback when none can be rendered, and repair leaving proof thumbnails alone.
  • Release gate (tests/test_release_gate.py): a legacy case migrates and every workflow answers, a closed-case folder copy opens identically, a large migrated case opens through bounded queries, and the frozen-binary packaging constraints hold.
  • Frontend (vitest + svelte-check + the production build): first-page paging, complete server search, full-result media facets, request cancellation on case switch, scoped metadata loading and thumbnail placeholder/failure/retry markup.
  • Map smoothness: the counts above, at a few thousand features and names (frontend/src/lib/globe/smoothness.test.js, mirror.test.js, standing.test.js, glyphs.test.js, lib/map/addedLayer.test.js, basemap.test.js, tileBatch.test.js), and in a real browser the flat map left undrawn under a turning globe, the address written once a turn and the imagery asked in batches (e2e/satellite-3d.spec.js, e2e/satellite-overlays.spec.js).

The backend suite runs on Python 3.11 and the three release operating systems.

Manual release steps

Two parts of the release gate are inherently manual and are done at release time, not in CI:

  • Record the reference-machine numbers (the profile above) and calibrate the relative CI thresholds against them.
  • Migrate several disposable real-world case copies end to end and confirm every workflow before tagging.

Roadmap compatibility

Later tools reuse this storage, job and event model rather than inventing their own: EXIF/OCR/transcript jobs on the durable queue with text artifacts and a future full-text index; the Notebook's file-backed content with indexed text and entity references; Board/Relations over indexed entities and typed links; Map and Timeline over geographic and temporal projections added when their first query lands; the Evidence Locker exporting committed events idempotently to evidence.jsonl; cross-case search over a rebuildable workspace index. New typed projections arrive with their own migration and tests; the core identifiers, provenance and link rules stay stable.