Database Design

Database responsibilities by design

Indexly’s database schema is intentionally simple and stable. Semantic intelligence lives in the indexing layer — not the schema.

Core fields and responsibilities

Field Purpose
content Tier 1 + Tier 2 (searchable)
clean_content Tier 1 only (noise-reduced)
file_metadata Tier 2 + Tier 3 (structured + JSON)

No schema changes were required to introduce semantic filtering.

Why clean_content exists

clean_content stores only human text, aggressively filtered.

This enables future features such as:

  • high-precision search modes
  • relevance tuning
  • hybrid ranking strategies

At the moment, all searches still use content, ensuring backward compatibility.

Metadata handling

Metadata is stored in two forms:

  1. Structured columns (for querying)
  2. Raw JSON (for traceability)

Only semantic metadata (Tier 2) is ever converted into FTS tokens.

file_metadata
├─ structured columns
│  ├─ title
│  ├─ author
│  ├─ camera
│  └─ …
└─ metadata (JSON)

Technical metadata remains queryable without polluting search results.

Safety and scalability

This design provides:

  • zero migration risk
  • predictable performance
  • clean rollback paths
  • future-proof evolution

Search index deletion scope

indexly clear-search is a maintenance command for the search database only. It removes matching paths from:

  • file_index
  • file_tags
  • file_metadata

The command does not delete source files and does not touch the separate cleaned_data database used by analysis persistence. For full behavior and recovery steps, see Clear Search Results Safely.

Runtime and analysis databases

Indexly keeps search indexing and analysis persistence separate:

Database Default purpose Typical location
fts_index.db FTS5 search index, tags, and file metadata used by search workflows Indexly runtime directory, such as %APPDATA%/indexly on Windows
indexly.db Persisted cleaned analysis data in cleaned_data, plus the analytical dataset catalog and artifact metadata ~/.indexly/indexly.db

This separation matters for maintenance:

  • indexly clear-search affects only search-index tables.
  • analysis commands persist cleaned data separately.
  • CSV analysis can register analytical dataset metadata and Parquet artifact paths for inference while keeping legacy JSON readable.
  • indexly doctor can inspect both databases and reports them as separate sections.
  • indexly doctor --full-integrity runs a read-only SQLite integrity check when a deeper scan is needed.

FTS5 virtual tables are also different from ordinary SQLite tables. Schema repairs can add normal-table columns safely, but FTS5 rebuilds require explicit operator intent. Indexly compares the module, ordered user columns, optional UNINDEXED markers, tokenizer, prefix sequence, and supported options semantically and refuses to automatically rebuild malformed or unsupported definitions.

An authorized rebuild uses a verified SQLite-consistent snapshot, keeps data movement inside SQLite, verifies logical rows and FTS behavior before the transactional table swap, and increments the search-index generation with the swap. A failed preflight or transaction leaves the original database intact. A post-commit verification failure reports the snapshot recovery path instead of claiming success.

Performance evidence is separate

The performance record is not stored in fts_index.db or the analysis database. It lives under the configured runtime directory in perf/performance-v1.json, with a previous validated copy for recovery.

indexly perf --show opens a non-WAL search database read-only and writes only the performance record. It refuses WAL mode before opening SQLite so a read-only connection cannot create or update a shared-memory sidecar. indexly perf --read does not open SQLite or write files. The record keeps bounded numeric evidence and a non-reversible local database identity; it excludes paths, filenames, content, query terms, raw logs, and network telemetry.

Theoretical capacity or free-page values are modelled descriptions, not evidence that schema changes, VACUUM, or a new database are required. See Performance Diagnostics and Optimization.

The separate guarded apply path currently supports only an evidence-backed SQLite 3.46+ planner-stat refresh or one 500-page FTS merge. It matches the recorded database identity, FTS/schema fingerprint, Indexly and SQLite versions, journal mode, page size, page count, file bytes, SQLite header change counter, document count, size bucket, and search-index generation under an immediate writer reservation. It creates and verifies a generic SQLite backup before mutation and preserves schema, indexed-row count, page size, and search generation invariants. Verification uses SQLite-side quick_check and compact invariants. A guarded FTS merge also requires FTS5 integrity-check to pass on both the completed backup and the post-action live database before commit.

Both actions require measured semantic FTS inspection state match. An absent legacy readiness metric returns collect_evidence; unavailable or unmeasured readiness returns unavailable without asserting schema damage. Measured readiness 0 for drift, external-content FTS, a missing table, or a malformed, unsupported, or uninspectable definition returns repair_required and remains a Doctor concern. The performance path does not normalize schema.

Action outcomes belong to the stable database identity, not to one baseline. They survive a same-database size-bucket reset, while a post-action report crossing buckets is rejected as non-comparable.