david/library-manager
1
0
Fork 0
Code Issues 2 Pull requests Projects Releases 10 Packages Wiki Activity Actions

Design scale-aware scanner persistence and database strategy #4

New issue
Closed
opened 2026-06-10 16:46:36 -07:00 by david · 4 comments
david commented 2026-06-10 16:46:36 -07:00
Owner
Copy link

Goal

Design the M2 persistence model around large existing libraries. Initial scans of 10,000+ files are expected and should shape database and indexing decisions.

Scope

  • Define tables/entities for scan runs, files/assets, candidates, metadata evidence, duplicate signals, and dry-run import plans.
  • Define indexes for operator workflows: title, author, narrator, series, media kind, warning type, duplicate fingerprint, path, import status, and scan state.
  • Define incremental/resumable scan behavior using path, size, modified time, and later hashes/fingerprints.
  • Validate whether SQLite is sufficient for the expected scale and history volume before locking it in.
  • Identify when PostgreSQL or another option would become necessary.

Acceptance

  • Architecture docs include a scale-aware schema sketch and database choice rationale.
  • Roadmap/decisions/wiki capture the accepted database direction and validation criteria.
  • Any prototype should include a synthetic large-library test or benchmark before real library scanning.
## Goal Design the M2 persistence model around large existing libraries. Initial scans of 10,000+ files are expected and should shape database and indexing decisions. ## Scope - Define tables/entities for scan runs, files/assets, candidates, metadata evidence, duplicate signals, and dry-run import plans. - Define indexes for operator workflows: title, author, narrator, series, media kind, warning type, duplicate fingerprint, path, import status, and scan state. - Define incremental/resumable scan behavior using path, size, modified time, and later hashes/fingerprints. - Validate whether SQLite is sufficient for the expected scale and history volume before locking it in. - Identify when PostgreSQL or another option would become necessary. ## Acceptance - Architecture docs include a scale-aware schema sketch and database choice rationale. - Roadmap/decisions/wiki capture the accepted database direction and validation criteria. - Any prototype should include a synthetic large-library test or benchmark before real library scanning.
david commented 2026-06-10 16:54:18 -07:00
Author
Owner
Copy link

M2 persistence planning progress:

  • Added db/schema.sql with scale-aware scanner/domain/acquisition tables and indexes.
  • Added docs/data-model.md with database direction, SQLite validation criteria, incremental scan rules, and indexing requirements.
  • Added npm run check:large-scan, which creates a temporary 10,000-file library outside the repo and scans it.
  • Added schema contract tests.

Verification passed:

  • npm test
  • npm run check:large-scan
  • git diff --check

Latest synthetic scan result: 10,000 files, 10,000 candidates, 10,000 evidence records, 10,000 dry-run import actions. Database write/read validation remains the next decision point before locking in SQLite.

M2 persistence planning progress: - Added db/schema.sql with scale-aware scanner/domain/acquisition tables and indexes. - Added docs/data-model.md with database direction, SQLite validation criteria, incremental scan rules, and indexing requirements. - Added npm run check:large-scan, which creates a temporary 10,000-file library outside the repo and scans it. - Added schema contract tests. Verification passed: - npm test - npm run check:large-scan - git diff --check Latest synthetic scan result: 10,000 files, 10,000 candidates, 10,000 evidence records, 10,000 dry-run import actions. Database write/read validation remains the next decision point before locking in SQLite.
david commented 2026-06-10 17:09:45 -07:00
Author
Owner
Copy link

Migrated the scanner foundation from JavaScript to Go for the M2 scale/database direction.

Verification now includes:

  • make test -> go test ./... passed.
  • make check-large-scan processed 10,000 synthetic files in 112 ms and produced 10,000 candidates/evidence records/dry-run actions.

Next step for #4 is SQLite write/read validation from Go and then persisted scan runs.

Migrated the scanner foundation from JavaScript to Go for the M2 scale/database direction. Verification now includes: - `make test` -> `go test ./...` passed. - `make check-large-scan` processed 10,000 synthetic files in 112 ms and produced 10,000 candidates/evidence records/dry-run actions. Next step for #4 is SQLite write/read validation from Go and then persisted scan runs.
david commented 2026-06-11 08:57:51 -07:00
Author
Owner
Copy link

Added the first SQLite persistence validation checkpoint for scanner reports.

Local verification:

  • make test passed.
  • make check-large-scan processed 10,000 synthetic files in 135 ms.
  • make check-persistence scanned 10,000 synthetic files in 116 ms and persisted the report in 1021 ms.

Persisted counts:

  • 10,000 scan_files
  • 10,000 candidates
  • 10,000 candidate_files
  • 10,000 metadata_evidence rows
  • 1 import_plan
  • 10,000 import_plan_actions

Next M2 validation should cover re-scans, missing/moved file state, and indexed read/query behavior before SQLite is fully locked in.

Added the first SQLite persistence validation checkpoint for scanner reports. Local verification: - `make test` passed. - `make check-large-scan` processed 10,000 synthetic files in 135 ms. - `make check-persistence` scanned 10,000 synthetic files in 116 ms and persisted the report in 1021 ms. Persisted counts: - 10,000 scan_files - 10,000 candidates - 10,000 candidate_files - 10,000 metadata_evidence rows - 1 import_plan - 10,000 import_plan_actions Next M2 validation should cover re-scans, missing/moved file state, and indexed read/query behavior before SQLite is fully locked in.
david commented 2026-06-11 09:12:15 -07:00
Author
Owner
Copy link

M2 persistence checkpoint completed.

Implemented:

  • 30,000-file scanner stress target.
  • 30,000-file SQLite persistence stress target.
  • Source-root-scoped candidates.
  • Missing-file re-scan state.
  • Conservative same-name/same-size moved-file detection.
  • Indexed review counters for unreviewed, held, duplicate, missing, and moved states.
  • Repeated scan/import-plan history validation.

Verification:

  • make test passed.
  • make check-large-scan: 30,000 files in 404 ms.
  • make check-persistence: scan 409 ms, persist 3209 ms, 30,000 rows across scan files/candidates/evidence/import actions.

SQLite is accepted for the first app scaffold, with the option to revisit after real-library testing.

M2 persistence checkpoint completed. Implemented: - 30,000-file scanner stress target. - 30,000-file SQLite persistence stress target. - Source-root-scoped candidates. - Missing-file re-scan state. - Conservative same-name/same-size moved-file detection. - Indexed review counters for unreviewed, held, duplicate, missing, and moved states. - Repeated scan/import-plan history validation. Verification: - `make test` passed. - `make check-large-scan`: 30,000 files in 404 ms. - `make check-persistence`: scan 409 ms, persist 3209 ms, 30,000 rows across scan files/candidates/evidence/import actions. SQLite is accepted for the first app scaffold, with the option to revisit after real-library testing.
david closed this issue 2026-06-11 09:12:16 -07:00
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
main
v0.2.8
v0.2.7
v0.2.6
v0.2.5
v0.2.4
v0.2.3
v0.2.2
v0.2.1
v0.2.0
v0.1.0
Labels
Clear labels
No items
No labels
Milestone
Clear milestone
No items
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
david/library-manager#4
No description provided.