fix: exercise + assess the sqlite results database (scrape + direct-write) — aggregator Phase D

[Jammy2211]

Overview

Phase D follow-up of the aggregator profiling task (#1375, phases A+B merged as #1376 + autofit_workspace_test#48). The sqlite results database (autofit/database/) has two paths: build-from-directory (scrape) and direct-write during a fit (session). Neither has been used in a long time and the direct-write path is suspected buggy. Goal: exercise both with the merged mock-results harness, fix the cheap bugs, and produce a written where-it's-at assessment for whatever isn't cheap — explicitly per the original prompt: "If its hard to fix dont bother, but its good to work out where its at."

Plan

• Run the existing autofit_workspace_test/scripts/database/{directory,scrape,session} scripts against current main — record which still pass, triage what fails.
• Exercise the scrape/build path at scale using the merged mock-results generator (scripts/profiling/aggregator/mock_results.py); fix bugs where cheap.
• Extend the profiling grid with database stages (build time, query time) so sqlite loading can be compared against the directory Aggregator — answering "when is building the database worth it?".
• Assess the session/direct-write path: fix if cheap, otherwise a written assessment (what works, what's broken, what a fix would take) posted to this issue.
• Assessment-first: source edits happen only after the failure survey, with the fix plan checkpointed.

Detailed implementation plan

Work Classification

Both (library + workspace) — bug workflow.

Worktree root

~/Code/PyAutoLabs-wt/aggregator-sqlite/

Affected Repositories

• PyAutoFit (primary — autofit/database/: aggregator/scrape.py, aggregator/aggregator.py, model/, sqlalchemy_.py, migration/)
• autofit_workspace_test (scripts/database/, scripts/profiling/aggregator/)

Branch Survey

Repository	Current Branch	Dirty?
./PyAutoFit	main	clean
./autofit_workspace_test	main	clean

Suggested branch: feature/aggregator-sqlite

Implementation Steps

1. Survey: run scripts/database/directory/*.py, scripts/database/scrape/*.py, scripts/database/session/*.py under PYAUTO_TEST_MODE; tabulate pass/fail + tracebacks.
2. Scale exercise: mock N=100/1000 result sets → af.Aggregator(database file) scrape/add_directory; catalogue failures.
3. Cheap fixes in autofit/database/ with unit tests (numpy-only).
4. Profiling stage: add database_build + database_query stages to profile_aggregator.py (or a sibling profile_database.py).
5. Session/direct-write assessment; cheap fixes or written verdict on this issue.

Key Files

• PyAutoFit/autofit/database/aggregator/scrape.py — build-from-directory path
• PyAutoFit/autofit/database/aggregator/aggregator.py — sqlite Aggregator + queries
• PyAutoFit/autofit/non_linear/paths/database.py — direct-write (session) paths
• autofit_workspace_test/scripts/database/ — existing coverage
• autofit_workspace_test/scripts/profiling/aggregator/ — merged harness to reuse

Verification

• Existing database scripts pass (or failures documented with tracebacks and triage).
• pytest test_autofit/database/ (plus aggregator subtree) green.
• Profiling table showing database build/query vs directory-aggregator loading on the same mock sets.

Original Prompt

Click to expand starting prompt

Phase D follow-up of the aggregator profiling task (PyAutoFit#1375; phases A+B merged as PyAutoFit#1376 + autofit_workspace_test#48). Original goal 3 of that prompt, verbatim:

"dont work on sqlite database builds on 1 or 2, but then do a follow up where you use this functionality, inevitably encounter and fix bugs for its use, and assess the code which writes directly to the sqlite database, which has not been used in a long time and I'm sure is buggy. If its hard to fix dont bother, but its good to work out where its at."

Concretely, in PyAutoFit (autofit/database/) driven from autofit_workspace_test:

1. Use the merged mock-results harness (scripts/profiling/aggregator/mock_results.py) to exercise the sqlite build-from-directory path (autofit/database/aggregator/scrape.py, af.Aggregator(database file) + add_directory) at scale; fix the bugs encountered where they are cheap; extend the profiling grid with a database-build + database-query stage so sqlite loading can be compared against the directory Aggregator.

2. Assess the direct-write path (session-based writing during a fit, database.py paths) — long unused and suspected buggy. Fix if cheap; otherwise produce a written where-it's-at assessment (what works, what is broken, what it would take) rather than sinking time.

3. The existing scripts/database/{directory,scrape,session}/ scripts in autofit_workspace_test are the current coverage — run them, note which still pass.

[Jammy2211]

Phase D assessment — where the sqlite database is at

Survey of existing coverage (8 scripts, PYAUTO_TEST_MODE)

script	before	after
database/directory/general	FAIL — scraped output/… while fits land in output/test_mode/…	PASS
database/directory/multi_analysis	FAIL (same)	PASS
database/scrape/general	FAIL (same)	PASS
database/scrape/multi_analysis	FAIL (same, + reused general's DB filename)	PASS
database/scrape/grid_search	PASS (already used with_test_mode_segment)	PASS
database/scrape/sensitivity	PASS	PASS
database/session/general	PASS	PASS
database/session/multi_analysis	PASS	PASS

The 4 failures were all one trap: manual output paths missing the test-mode segment — the same fix the two passing scrape scripts already carried. These are very likely the standing "workspace validation not passing" failures Heart has been reporting since 2026-07-09.

Bugs found and fixed (PyAutoFit)

1. Database aggregator slicing was inverted (database/aggregator/aggregator.py::__getitem__): agg[:5] on 26 fits returned 21 (len − stop items instead of stop − start); agg[3:8] returned 15. The existing unit test passed only by coincidence (2 fits, stop=1, where len−stop == stop). Fixed + regression test with 3 fits.
2. Direct-write (session) fits never stored samples_summary: DatabasePaths fell through to the abstract no-op, so summary-based workflows (the lightweight path we just made central in phases A+B, and AggregateCSV) failed with AttributeError on any session-written database. Implemented save_samples_summary/load_samples_summary on DatabasePaths (mirroring the directory filter semantics; missing summary maps to the FileNotFoundError the resume check expects). Verified end-to-end: a session-written fit's database now serves values("samples_summary").

Not bugs, but traps documented

• Scrape dedupes by identifier (hash of search + model + unique_tag) — identical copies collapse to one row. The mock harness now stamps a distinct unique_tag into each copy's search.json so database exercise is faithful.
• open_database silently prepends conf.instance.output_path (plus the test-mode segment) to relative sqlite paths — a relative path you pass is not where the file goes, and an empty decoy file can appear at the literal path. Absolute paths bypass both. The new profiler uses absolute paths.
• Direct-write stores minimised samples by design (save_all_samples=False → samples.minimise()), so session databases legitimately have ~1-row sample lists unless configured otherwise — not a bug, but surprising next to scrape databases that keep the full csv.

Performance: database vs directory (mock results, same data)

Quick grid (per-result ms; 5-component model; values("samples") on identical mock data):

cell	db build	db query	db values("samples")	directory values("samples")	db size
10 × 1k samples	394	1.0	206	51	0.18 MB/result
50 × 1k samples	426	1.2	354	147	0.17 MB/result
50 × 100 samples	403	0.6	252	26	0.05 MB/result

The 1000-result × 1k-sample cell was abandoned mid-run after ~50 min of CPU on that single cell (its 173 MB database did build; the process sat at ~3.4 GB RSS in the sample-loading stages) — the directory Aggregator's entire 9-cell grid including a 3000-result cell completes in ~8 min. Build cost is ~0.4 s/result up front (≈20 min for a 3000-lens catalogue) and is dominated by loading every result's full samples into the ORM.

Verdict: at current state the sqlite path offers no loading-speed advantage — build costs ~0.4s/result up front and values("samples") from the ORM is several times slower than the (post-#1376) directory Aggregator on identical data. Queries are fast once built, and the single-file property remains the real use case (HPC inode limits). Making the database fast would mean attacking ORM object reconstruction — out of "low-hanging" scope; recorded here instead.

Direct-write path — verdict

Contrary to suspicion it is not broken at the smoke level: both session scripts pass, fits/queries/samples round-trip. Its real gaps were the missing samples_summary (fixed above) and the minimised-samples surprise (documented). It remains less exercised than the scrape path — treat it as working-but-thinly-tested.

[Jammy2211]

Shipped and merged (2026-07-16): #1380 + PyAutoLabs/autofit_workspace_test#49 (heart RED acked — 5 pre-existing unrelated reasons, recorded in Mind; this PR pair actually fixes part of the workspace-validation reason). Closing — the assessment above is the Phase D deliverable; making the sqlite path fast (ORM reconstruction) is recorded there as explicitly out of low-hanging scope.