lhumina_code/hero_proc
2
0
Fork 0
Code Issues 27 Pull requests Projects Releases 2 Packages Wiki Activity Actions

Daily & Context-Scoped Log Storage in SQLite #15

New issue
Closed
opened 2026-03-20 05:05:45 +00:00 by despiegk · 3 comments
despiegk commented 2026-03-20 05:05:45 +00:00
Owner
Copy link

Minimal Spec — Daily & Context-Scoped Log Storage in SQLite

Goal

Change log storage from a single SQLite database to a partitioned SQLite layout:

  • one database per day
  • one directory tree per context
  • keep the API as stable as possible
  • support fast prefix search on src
  • support search across multiple day databases
  • ensure databases are opened lazily and released again
  • add performance and integration tests for large volumes

Current Table

Existing schema remains the logical base:

CREATE TABLE logs (
    logid        INTEGER PRIMARY KEY AUTOINCREMENT,
    context_name TEXT    NOT NULL DEFAULT 'core',
    src          TEXT    NOT NULL DEFAULT '',
    tags         TEXT    NOT NULL DEFAULT '[]',
    epoch        INTEGER NOT NULL,
    loglevel     INTEGER NOT NULL DEFAULT 0,
    error        INTEGER NOT NULL DEFAULT 0,
    content      TEXT    NOT NULL
);

New Storage Model

Partitioning rules

Logs are stored:

  • per context
  • per calendar day
  • in a dedicated SQLite database for logs only

Each database contains only the logs for:

  • one context_name
  • one UTC day or configured system day basis

Directory Layout

Base path:

/logs/<context>/<year>/<day>/logs.sqlite

Example:

/logs/default/2026/030/logs.sqlite
/logs/core/2026/030/logs.sqlite
/logs/hero_embedder/2026/031/logs.sqlite

Path rules

  • context is normalized to a safe filesystem name
  • year is 4 digits
  • day is day-of-year, zero-padded to 3 digits (001–366)
  • default context should be "default"

Example for March 20, 2026:

/logs/default/2026/079/logs.sqlite

Database Naming

Use a fixed filename inside each partition directory:

logs.sqlite

Optional side files may exist:

logs.sqlite-wal
logs.sqlite-shm
meta.json

Fixed filename is preferred over embedding date/context in the filename because the directory already defines identity.

Partition Selection

For each log write:

  1. determine effective context_name
  2. determine day partition from epoch
  3. resolve path
  4. open or reuse that SQLite database
  5. insert row

For each search:

  1. determine requested context(s)
  2. determine requested time range
  3. resolve all matching day partitions
  4. query one or more SQLite databases
  5. merge results into one response

API Requirements

Compatibility

External API should remain mostly unchanged.

New/required search input

UI and backend must support explicit time range selection:

  • today
  • last 24h
  • last 7d
  • custom from / to

Backend search must no longer assume one single database.

Search semantics

Search API must transparently support:

  • single-day search
  • multi-day search
  • multi-context search if needed later

Result format should remain unchanged as much as possible.

Schema Per Partition

Each daily database uses the same schema, with context_name retained for compatibility, even though the DB is already context-scoped.

CREATE TABLE logs (
    logid        INTEGER PRIMARY KEY AUTOINCREMENT,
    context_name TEXT    NOT NULL DEFAULT 'default',
    src          TEXT    NOT NULL DEFAULT '',
    tags         TEXT    NOT NULL DEFAULT '[]',
    epoch        INTEGER NOT NULL,
    loglevel     INTEGER NOT NULL DEFAULT 0,
    error        INTEGER NOT NULL DEFAULT 0,
    content      TEXT    NOT NULL
);

Indexing Requirements

Required indexes

CREATE INDEX idx_logs_epoch ON logs(epoch);
CREATE INDEX idx_logs_loglevel ON logs(loglevel);
CREATE INDEX idx_logs_error ON logs(error);
CREATE INDEX idx_logs_src ON logs(src);

Prefix search on src

src contains dot-separated names, for example:

hero.proc.worker
hero.proc.api
hero.embedder.indexer

We need efficient prefix search such as:

hero.proc%
hero.embedder%

Requirement

Queries must use a form that can benefit from the src index.

Preferred:

WHERE src >= ? AND src < ?

instead of relying only on:

WHERE src LIKE 'hero.proc%'

Because range queries are more predictable for indexed prefix search.

Backend should convert prefix input:

  • prefix: hero.proc

into bounds:

  • lower: hero.proc
  • upper: next lexical prefix boundary

Implementation detail may vary, but the requirement is:

  • fast indexed prefix search
  • no full table scan for normal prefix queries

Write Path

Behavior

On write:

  • resolve partition from context_name + epoch
  • create directory if missing
  • create database if missing
  • ensure schema + indexes exist
  • insert record

Requirements

  • schema creation must be idempotent
  • first write to a new day automatically creates the new daily DB
  • write path should not require manual rotation jobs

Read/Search Path

General

Search may span one or many daily databases.

Requirements

  • resolve relevant database list from requested time range
  • do not scan unrelated days
  • execute search per selected database
  • merge results in memory
  • sort globally by requested order
  • support paging/limit

Query strategy

For short ranges, search directly across selected partitions.

For longer ranges:

  • still use per-partition queries
  • merge incrementally
  • avoid loading full result sets into memory if possible

Connection Management

Goal

Do not keep all SQLite files open forever.

Requirements

Implement a small DB-handle cache:

  • open databases lazily
  • reuse recently used handles
  • close idle handles after timeout
  • configurable max open handles
  • explicit cleanup on shutdown

Expected behavior

  • databases not used anymore must become closable/unloadable
  • verify this with tests
  • no unbounded growth in open file descriptors
  • no permanent memory growth due to long search history

Suggested policy

  • LRU cache for open DB handles
  • idle close timeout, e.g. 30–120 seconds
  • hard cap on open handles, e.g. 32 or configurable

UI Changes

Admin/UI log viewer must include time range selection.

Minimum options

  • Today
  • Last 24 hours
  • Last 7 days
  • Custom range

Behavior

  • selecting a longer range causes backend to search multiple DBs
  • this must be transparent to the user
  • UI should communicate that wider ranges may be slower

Migration / Compatibility

Minimal requirement

No forced migration of old single-file log DB is required initially.

System may support:

  • old legacy single DB
  • new partitioned DBs for new writes

Optional later migration can be added separately.

Startup behavior

New installations should use partitioned storage directly.

Non-Functional Requirements

Performance

System must scale to:

  • millions of log records
  • many daily partitions
  • fast prefix search on src
  • acceptable multi-day scans

Reliability

  • safe creation of new daily DBs
  • no corruption due to day rollover
  • graceful behavior if a partition DB is missing or damaged

Observability

Add internal metrics/logging for:

  • opened DB count
  • closed DB count
  • search duration
  • partitions scanned
  • rows matched
  • insert throughput

Test Requirements

1. Integration Tests

Cover:

  • create new DB on first write of day
  • create different DBs for different contexts
  • create different DBs for different days
  • search only today
  • search across multiple days
  • prefix search on src
  • sorting and paging across multiple DBs
  • idle DB closing / reopening
  • shutdown cleanup

2. Performance Tests

Add performance test tooling in admin section of hero_prog.

Requirements

Synthetic generator must support:

  • writing millions of records
  • synthetic timestamps spanning multiple days
  • multiple contexts
  • configurable src prefixes
  • configurable content size
  • configurable batch size

Must measure

  • insert throughput

  • search latency for:

    • exact day
    • 7-day range
    • 30-day range
    • prefix search on src
    • prefix search + time filter

  • open/close handle behavior

  • memory usage during long-range search

3. Large Dataset Test Cases

Minimum scenarios:

Scenario A

  • 1 context
  • 1 day
  • 1,000,000 rows

Scenario B

  • 1 context
  • 30 days
  • 10,000,000 rows total

Scenario C

  • 10 contexts
  • 30 days
  • millions of rows with mixed searches

Scenario D

  • repeated searches over wide ranges
  • verify handles are released again after idle timeout

Acceptance Criteria

Implementation is accepted when:

  1. logs are written into per-context, per-day SQLite DBs
  2. directory structure is clean and deterministic
  3. API remains mostly unchanged
  4. UI supports explicit time-range selection
  5. multi-day search works transparently
  6. src prefix search is indexed and fast
  7. DB handles are not kept open forever
  8. integration tests cover partitioning and search behavior
  9. performance tooling can generate and query millions of records
  10. measured performance is acceptable and memory/file-handle behavior remains bounded

Recommended Defaults

  • default context name: default
  • partition basis: UTC day
  • path format: /logs/<context>/<year>/<day>/logs.sqlite
  • day format: day-of-year, zero-padded to 3 digits
  • max open DB handles: configurable, default 32
  • idle close timeout: configurable, default 60s
# Minimal Spec — Daily & Context-Scoped Log Storage in SQLite ## Goal Change log storage from a single SQLite database to a **partitioned SQLite layout**: * **one database per day** * **one directory tree per context** * keep the **API as stable as possible** * support **fast prefix search on `src`** * support **search across multiple day databases** * ensure databases are **opened lazily and released again** * add **performance and integration tests** for large volumes --- ## Current Table Existing schema remains the logical base: ```sql CREATE TABLE logs ( logid INTEGER PRIMARY KEY AUTOINCREMENT, context_name TEXT NOT NULL DEFAULT 'core', src TEXT NOT NULL DEFAULT '', tags TEXT NOT NULL DEFAULT '[]', epoch INTEGER NOT NULL, loglevel INTEGER NOT NULL DEFAULT 0, error INTEGER NOT NULL DEFAULT 0, content TEXT NOT NULL ); ``` --- ## New Storage Model ### Partitioning rules Logs are stored: * **per context** * **per calendar day** * in a **dedicated SQLite database for logs only** Each database contains only the logs for: * one `context_name` * one UTC day or configured system day basis --- ## Directory Layout Base path: ```text /logs/<context>/<year>/<day>/logs.sqlite ``` Example: ```text /logs/default/2026/030/logs.sqlite /logs/core/2026/030/logs.sqlite /logs/hero_embedder/2026/031/logs.sqlite ``` ### Path rules * `context` is normalized to a safe filesystem name * `year` is 4 digits * `day` is day-of-year, zero-padded to 3 digits (`001`–`366`) * default context should be `"default"` Example for March 20, 2026: ```text /logs/default/2026/079/logs.sqlite ``` --- ## Database Naming Use a fixed filename inside each partition directory: ```text logs.sqlite ``` Optional side files may exist: ```text logs.sqlite-wal logs.sqlite-shm meta.json ``` Fixed filename is preferred over embedding date/context in the filename because the directory already defines identity. --- ## Partition Selection For each log write: 1. determine effective `context_name` 2. determine day partition from `epoch` 3. resolve path 4. open or reuse that SQLite database 5. insert row For each search: 1. determine requested context(s) 2. determine requested time range 3. resolve all matching day partitions 4. query one or more SQLite databases 5. merge results into one response --- ## API Requirements ### Compatibility External API should remain **mostly unchanged**. ### New/required search input UI and backend must support explicit time range selection: * `today` * `last 24h` * `last 7d` * custom `from` / `to` Backend search must no longer assume one single database. ### Search semantics Search API must transparently support: * single-day search * multi-day search * multi-context search if needed later Result format should remain unchanged as much as possible. --- ## Schema Per Partition Each daily database uses the same schema, with `context_name` retained for compatibility, even though the DB is already context-scoped. ```sql CREATE TABLE logs ( logid INTEGER PRIMARY KEY AUTOINCREMENT, context_name TEXT NOT NULL DEFAULT 'default', src TEXT NOT NULL DEFAULT '', tags TEXT NOT NULL DEFAULT '[]', epoch INTEGER NOT NULL, loglevel INTEGER NOT NULL DEFAULT 0, error INTEGER NOT NULL DEFAULT 0, content TEXT NOT NULL ); ``` --- ## Indexing Requirements ### Required indexes ```sql CREATE INDEX idx_logs_epoch ON logs(epoch); CREATE INDEX idx_logs_loglevel ON logs(loglevel); CREATE INDEX idx_logs_error ON logs(error); CREATE INDEX idx_logs_src ON logs(src); ``` ### Prefix search on `src` `src` contains dot-separated names, for example: ```text hero.proc.worker hero.proc.api hero.embedder.indexer ``` We need efficient prefix search such as: ```text hero.proc% hero.embedder% ``` ### Requirement Queries must use a form that can benefit from the `src` index. Preferred: ```sql WHERE src >= ? AND src < ? ``` instead of relying only on: ```sql WHERE src LIKE 'hero.proc%' ``` Because range queries are more predictable for indexed prefix search. Backend should convert prefix input: * prefix: `hero.proc` into bounds: * lower: `hero.proc` * upper: next lexical prefix boundary Implementation detail may vary, but the requirement is: * **fast indexed prefix search** * **no full table scan for normal prefix queries** --- ## Write Path ### Behavior On write: * resolve partition from `context_name + epoch` * create directory if missing * create database if missing * ensure schema + indexes exist * insert record ### Requirements * schema creation must be idempotent * first write to a new day automatically creates the new daily DB * write path should not require manual rotation jobs --- ## Read/Search Path ### General Search may span one or many daily databases. ### Requirements * resolve relevant database list from requested time range * do not scan unrelated days * execute search per selected database * merge results in memory * sort globally by requested order * support paging/limit ### Query strategy For short ranges, search directly across selected partitions. For longer ranges: * still use per-partition queries * merge incrementally * avoid loading full result sets into memory if possible --- ## Connection Management ### Goal Do not keep all SQLite files open forever. ### Requirements Implement a small DB-handle cache: * open databases lazily * reuse recently used handles * close idle handles after timeout * configurable max open handles * explicit cleanup on shutdown ### Expected behavior * databases not used anymore must become closable/unloadable * verify this with tests * no unbounded growth in open file descriptors * no permanent memory growth due to long search history ### Suggested policy * LRU cache for open DB handles * idle close timeout, e.g. 30–120 seconds * hard cap on open handles, e.g. 32 or configurable --- ## UI Changes Admin/UI log viewer must include time range selection. ### Minimum options * Today * Last 24 hours * Last 7 days * Custom range ### Behavior * selecting a longer range causes backend to search multiple DBs * this must be transparent to the user * UI should communicate that wider ranges may be slower --- ## Migration / Compatibility ### Minimal requirement No forced migration of old single-file log DB is required initially. System may support: * old legacy single DB * new partitioned DBs for new writes Optional later migration can be added separately. ### Startup behavior New installations should use partitioned storage directly. --- ## Non-Functional Requirements ### Performance System must scale to: * millions of log records * many daily partitions * fast prefix search on `src` * acceptable multi-day scans ### Reliability * safe creation of new daily DBs * no corruption due to day rollover * graceful behavior if a partition DB is missing or damaged ### Observability Add internal metrics/logging for: * opened DB count * closed DB count * search duration * partitions scanned * rows matched * insert throughput --- ## Test Requirements ## 1. Integration Tests Cover: * create new DB on first write of day * create different DBs for different contexts * create different DBs for different days * search only today * search across multiple days * prefix search on `src` * sorting and paging across multiple DBs * idle DB closing / reopening * shutdown cleanup --- ## 2. Performance Tests Add performance test tooling in admin section of `hero_prog`. ### Requirements Synthetic generator must support: * writing millions of records * synthetic timestamps spanning multiple days * multiple contexts * configurable `src` prefixes * configurable content size * configurable batch size ### Must measure * insert throughput * search latency for: * exact day * 7-day range * 30-day range * prefix search on `src` * prefix search + time filter * open/close handle behavior * memory usage during long-range search --- ## 3. Large Dataset Test Cases Minimum scenarios: ### Scenario A * 1 context * 1 day * 1,000,000 rows ### Scenario B * 1 context * 30 days * 10,000,000 rows total ### Scenario C * 10 contexts * 30 days * millions of rows with mixed searches ### Scenario D * repeated searches over wide ranges * verify handles are released again after idle timeout --- ## Acceptance Criteria Implementation is accepted when: 1. logs are written into per-context, per-day SQLite DBs 2. directory structure is clean and deterministic 3. API remains mostly unchanged 4. UI supports explicit time-range selection 5. multi-day search works transparently 6. `src` prefix search is indexed and fast 7. DB handles are not kept open forever 8. integration tests cover partitioning and search behavior 9. performance tooling can generate and query millions of records 10. measured performance is acceptable and memory/file-handle behavior remains bounded --- ## Recommended Defaults * default context name: `default` * partition basis: UTC day * path format: `/logs/<context>/<year>/<day>/logs.sqlite` * day format: day-of-year, zero-padded to 3 digits * max open DB handles: configurable, default 32 * idle close timeout: configurable, default 60s
despiegk commented 2026-03-20 05:24:49 +00:00
Author
Owner
Copy link

Implementation Spec for Issue #15 — Daily & Context-Scoped Log Storage in SQLite

Objective

Replace the current single-SQLite log storage with a partitioned SQLite layout where each partition is identified by (context, year, day-of-year). External API — RPC methods, LogEntry, LogFilter, LoggingApi, HeroProcDb — remains structurally stable. The server's background cleanup task is adapted. The UI gains a time-range selector.

Requirements

  • Storage layout: /logs/<context>/<year>/<day>/logs.sqlite (context normalised, year 4 digits, day 3-digit day-of-year 001–366)
  • Default context: "default" on disk
  • Write path: derive partition from (context_name, epoch) → open/create DB lazily → insert
  • Read path: resolve DBs from time range, query each partition, merge & sort in memory
  • Connection pool: LRU cache, max 32 open handles, idle close after 60 s
  • Indexes on epoch, loglevel, error, src; prefix search via range queries (WHERE src >= ? AND src < ?)
  • list_sources, delete_by_src, delete_older_than adapted to iterate partitions
  • UI: time-range selector (Today / Last 24 h / Last 7 d / Custom)
  • Integration & performance tests (1M+ rows, < 500 ms query)

Files to Modify/Create

New files:

  • crates/hero_proc_lib/src/db/logs/partition.rs — Partition key derivation, path resolution, schema init
  • crates/hero_proc_lib/src/db/logs/pool.rs — LRU connection pool (PartitionPool)
  • crates/hero_proc_lib/src/db/logs/store.rs — PartitionedLogStore: insert, query, count, list_sources, delete, export/import

Modified files:

  • crates/hero_proc_lib/src/db/logs/mod.rs — Delegate to PartitionedLogStore
  • crates/hero_proc_lib/src/db/logs/model.rs — Update schema init (drop context_name col, add indexes)
  • crates/hero_proc_lib/src/db/factory.rs — LoggingApi holds Arc<PartitionedLogStore>; HeroProcDb::new accepts logs_base_dir
  • crates/hero_proc_server/src/main.rs — Update HeroProcDb::new call; verify cleanup task
  • crates/hero_proc_server/src/rpc/log.rs — Verify compile; add optional context param to list_sources
  • crates/hero_proc_ui/templates/index.html — Time-range toolbar in #tab-logs
  • crates/hero_proc_ui/static/js/dashboard.js — Wire time-range selector to loadLogs()
  • Cargo.toml — Add lru = "0.12"

Implementation Plan

Step 1: Partition key primitives and per-partition DB init

Files: partition.rs, model.rs

  • PartitionKey { context_safe, year, day } (Hash, Eq, Clone)
  • partition_key(context, epoch) — uses chrono to derive year + day-of-year; normalises context with name_fix
  • partition_path(base_dir, key) → <base>/<ctx>/<year>/<day_padded>/logs.sqlite
  • open_partition(path) — WAL mode + init_partition_schema
  • Schema: logs table without context_name col; indexes on epoch, loglevel, error, src
  • Unit tests: known epoch → correct path/key
    Dependencies: none

Step 2: LRU connection pool

Files: pool.rs, Cargo.toml

  • Add lru = "0.12" dependency
  • PartitionPool { base_dir, cache: Mutex<LruCache<PartitionKey, PoolEntry>>, max_open, idle_secs }
  • with_conn(key, f): evict idle, lookup or open, call f on &mut Connection
  • evict_idle(): remove entries past idle timeout
  • Unit tests: LRU eviction, idle close
    Dependencies: Step 1

Step 3: PartitionedLogStore — write path

Files: store.rs

  • insert(entry) and insert_batch(entries) grouped by partition
  • Entries stored without context_name column (encoded in path)
  • Unit tests: file-system verification of partition files
    Dependencies: Step 2

Step 4: PartitionedLogStore — read path

Files: store.rs

  • resolve_partitions(base_dir, context_filter, epoch_from, epoch_to) — walks dir tree, filters by time range
  • query(filter) — per-partition filtered SQL + in-memory merge sort + pagination
  • count(filter), list_sources(context_filter)
  • src prefix via range query; reconstruct context_name from PartitionKey
  • Unit tests: multi-day, prefix search, context isolation
    Dependencies: Step 3

Step 5: PartitionedLogStore — delete + export/import

Files: store.rs

  • delete_by_src(pattern) — across all partitions
  • delete_older_than(epoch) — removes entire .sqlite/-wal/-shm files for expired days; row-level delete for boundary day
  • export_logs(filter), import_logs(data) — reuse flate2 compression
  • Unit tests: delete across multiple files; file removal verification
    Dependencies: Step 4

Step 6: Rewrite LoggingApi in factory.rs

Files: factory.rs, mod.rs

  • LoggingApi { store: Arc<PartitionedLogStore> }
  • HeroProcDb::new derives logs_base_dir = db_path.parent().join("logs")
  • Drop init_schema call for logs on main DB
  • with_defaults() uses $HOME/hero/var/logs
  • Update all unit tests to use TempDir
    Dependencies: Step 5

Step 7: Update server main and RPC handler

Files: main.rs, rpc/log.rs

  • Update HeroProcDb::new call signature
  • Verify background cleanup (delete_older_than) compiles
  • Pass time-range params from RPC to store query
    Dependencies: Step 6

Step 8: Update existing tests

Files: integration_tests.rs, logs/mod.rs tests

  • Update make_db() to supply logs_base_dir temp path
  • Replace open_log_db(":memory:") with PartitionedLogStore::new(tmp_dir, 8, 60)
    Dependencies: Step 6

Step 9: New integration & performance tests

Files: store.rs tests or tests/ module

  • test_partitioning_creates_correct_dirs
  • test_multi_day_query (7 days, epoch range filter)
  • test_src_prefix_search (range query, not LIKE %)
  • test_idle_close (LRU eviction)
  • test_context_isolation
  • #[ignore] perf test: 1M entries, 7-day range < 500 ms
    Dependencies: Step 5

Step 10: UI time-range filter

Files: index.html, dashboard.js

  • Add <select> + custom date inputs in #tab-logs toolbar
  • Default: Last 24 h; toggle custom inputs on "Custom…"
  • Wire epoch_from/epoch_to into loadLogs() filter
    Dependencies: Step 7

Acceptance Criteria

  • All existing cargo test -p hero_proc_lib tests pass
  • All existing cargo test -p hero_proc_server tests pass
  • Logs appear under <base>/logs/<context>/<year>/<day>/logs.sqlite
  • Multi-day query returns sorted results from all matching partitions
  • src prefix uses range scan (>=/<) not LIKE %
  • Max 32 open DB handles enforced (LRU eviction)
  • Idle connections closed after 60 s
  • delete_older_than removes entire SQLite files for expired days
  • Background cleanup continues working
  • UI time-range selector defaults to "Last 24 h"; populates epoch_from/epoch_to
  • Performance test: 1M entries, 7-day range query < 500 ms
  • export_logs/import_logs round-trips correctly
  • logs.sources returns distinct sources across all partitions

Notes

  1. context_name column removed from per-partition schema (encoded in path); reconstructed from PartitionKey on read-back. name_fix normalisation is lossy — canonical form is context_safe.
  2. logid is unique only within a partition. Combined (context_name, epoch, logid) is globally unique. No RPC handler does bare logid point-lookup — safe.
  3. Concurrency: PartitionPool uses std::sync::Mutex (library remains async-free). Server calls via spawn_blocking if needed.
  4. chrono already a workspace dep — use DateTime<Utc>::from_timestamp(epoch, 0).ordinal() for day-of-year.
  5. lru = "0.12" must be added to workspace + crate deps.
  6. On file deletion in delete_older_than, also remove -wal and -shm companion files.
  7. Main hero_proc.db retains all non-log tables; only the logs table init is removed from factory.rs.
## Implementation Spec for Issue #15 — Daily & Context-Scoped Log Storage in SQLite ### Objective Replace the current single-SQLite log storage with a **partitioned SQLite layout** where each partition is identified by `(context, year, day-of-year)`. External API — RPC methods, `LogEntry`, `LogFilter`, `LoggingApi`, `HeroProcDb` — remains structurally stable. The server's background cleanup task is adapted. The UI gains a time-range selector. --- ### Requirements - Storage layout: `/logs/<context>/<year>/<day>/logs.sqlite` (`context` normalised, `year` 4 digits, `day` 3-digit day-of-year 001–366) - Default context: `"default"` on disk - Write path: derive partition from `(context_name, epoch)` → open/create DB lazily → insert - Read path: resolve DBs from time range, query each partition, merge & sort in memory - Connection pool: LRU cache, max 32 open handles, idle close after 60 s - Indexes on `epoch`, `loglevel`, `error`, `src`; prefix search via range queries (`WHERE src >= ? AND src < ?`) - `list_sources`, `delete_by_src`, `delete_older_than` adapted to iterate partitions - UI: time-range selector (Today / Last 24 h / Last 7 d / Custom) - Integration & performance tests (1M+ rows, < 500 ms query) --- ### Files to Modify/Create **New files:** - `crates/hero_proc_lib/src/db/logs/partition.rs` — Partition key derivation, path resolution, schema init - `crates/hero_proc_lib/src/db/logs/pool.rs` — LRU connection pool (`PartitionPool`) - `crates/hero_proc_lib/src/db/logs/store.rs` — `PartitionedLogStore`: insert, query, count, list_sources, delete, export/import **Modified files:** - `crates/hero_proc_lib/src/db/logs/mod.rs` — Delegate to `PartitionedLogStore` - `crates/hero_proc_lib/src/db/logs/model.rs` — Update schema init (drop `context_name` col, add indexes) - `crates/hero_proc_lib/src/db/factory.rs` — `LoggingApi` holds `Arc<PartitionedLogStore>`; `HeroProcDb::new` accepts `logs_base_dir` - `crates/hero_proc_server/src/main.rs` — Update `HeroProcDb::new` call; verify cleanup task - `crates/hero_proc_server/src/rpc/log.rs` — Verify compile; add optional context param to `list_sources` - `crates/hero_proc_ui/templates/index.html` — Time-range toolbar in `#tab-logs` - `crates/hero_proc_ui/static/js/dashboard.js` — Wire time-range selector to `loadLogs()` - `Cargo.toml` — Add `lru = "0.12"` --- ### Implementation Plan #### Step 1: Partition key primitives and per-partition DB init Files: `partition.rs`, `model.rs` - `PartitionKey { context_safe, year, day }` (Hash, Eq, Clone) - `partition_key(context, epoch)` — uses `chrono` to derive year + day-of-year; normalises context with `name_fix` - `partition_path(base_dir, key)` → `<base>/<ctx>/<year>/<day_padded>/logs.sqlite` - `open_partition(path)` — WAL mode + `init_partition_schema` - Schema: `logs` table without `context_name` col; indexes on epoch, loglevel, error, src - Unit tests: known epoch → correct path/key Dependencies: none #### Step 2: LRU connection pool Files: `pool.rs`, `Cargo.toml` - Add `lru = "0.12"` dependency - `PartitionPool { base_dir, cache: Mutex<LruCache<PartitionKey, PoolEntry>>, max_open, idle_secs }` - `with_conn(key, f)`: evict idle, lookup or open, call `f` on `&mut Connection` - `evict_idle()`: remove entries past idle timeout - Unit tests: LRU eviction, idle close Dependencies: Step 1 #### Step 3: `PartitionedLogStore` — write path Files: `store.rs` - `insert(entry)` and `insert_batch(entries)` grouped by partition - Entries stored without `context_name` column (encoded in path) - Unit tests: file-system verification of partition files Dependencies: Step 2 #### Step 4: `PartitionedLogStore` — read path Files: `store.rs` - `resolve_partitions(base_dir, context_filter, epoch_from, epoch_to)` — walks dir tree, filters by time range - `query(filter)` — per-partition filtered SQL + in-memory merge sort + pagination - `count(filter)`, `list_sources(context_filter)` - `src` prefix via range query; reconstruct `context_name` from `PartitionKey` - Unit tests: multi-day, prefix search, context isolation Dependencies: Step 3 #### Step 5: `PartitionedLogStore` — delete + export/import Files: `store.rs` - `delete_by_src(pattern)` — across all partitions - `delete_older_than(epoch)` — removes entire `.sqlite`/`-wal`/`-shm` files for expired days; row-level delete for boundary day - `export_logs(filter)`, `import_logs(data)` — reuse flate2 compression - Unit tests: delete across multiple files; file removal verification Dependencies: Step 4 #### Step 6: Rewrite `LoggingApi` in `factory.rs` Files: `factory.rs`, `mod.rs` - `LoggingApi { store: Arc<PartitionedLogStore> }` - `HeroProcDb::new` derives `logs_base_dir = db_path.parent().join("logs")` - Drop `init_schema` call for logs on main DB - `with_defaults()` uses `$HOME/hero/var/logs` - Update all unit tests to use `TempDir` Dependencies: Step 5 #### Step 7: Update server main and RPC handler Files: `main.rs`, `rpc/log.rs` - Update `HeroProcDb::new` call signature - Verify background cleanup (`delete_older_than`) compiles - Pass time-range params from RPC to store query Dependencies: Step 6 #### Step 8: Update existing tests Files: `integration_tests.rs`, `logs/mod.rs` tests - Update `make_db()` to supply `logs_base_dir` temp path - Replace `open_log_db(":memory:")` with `PartitionedLogStore::new(tmp_dir, 8, 60)` Dependencies: Step 6 #### Step 9: New integration & performance tests Files: `store.rs` tests or `tests/` module - `test_partitioning_creates_correct_dirs` - `test_multi_day_query` (7 days, epoch range filter) - `test_src_prefix_search` (range query, not LIKE %) - `test_idle_close` (LRU eviction) - `test_context_isolation` - `#[ignore]` perf test: 1M entries, 7-day range < 500 ms Dependencies: Step 5 #### Step 10: UI time-range filter Files: `index.html`, `dashboard.js` - Add `<select>` + custom date inputs in `#tab-logs` toolbar - Default: Last 24 h; toggle custom inputs on "Custom…" - Wire `epoch_from`/`epoch_to` into `loadLogs()` filter Dependencies: Step 7 --- ### Acceptance Criteria - [ ] All existing `cargo test -p hero_proc_lib` tests pass - [ ] All existing `cargo test -p hero_proc_server` tests pass - [ ] Logs appear under `<base>/logs/<context>/<year>/<day>/logs.sqlite` - [ ] Multi-day query returns sorted results from all matching partitions - [ ] `src` prefix uses range scan (`>=`/`<`) not `LIKE %` - [ ] Max 32 open DB handles enforced (LRU eviction) - [ ] Idle connections closed after 60 s - [ ] `delete_older_than` removes entire SQLite files for expired days - [ ] Background cleanup continues working - [ ] UI time-range selector defaults to "Last 24 h"; populates `epoch_from`/`epoch_to` - [ ] Performance test: 1M entries, 7-day range query < 500 ms - [ ] `export_logs`/`import_logs` round-trips correctly - [ ] `logs.sources` returns distinct sources across all partitions --- ### Notes 1. `context_name` column removed from per-partition schema (encoded in path); reconstructed from `PartitionKey` on read-back. `name_fix` normalisation is lossy — canonical form is `context_safe`. 2. `logid` is unique only within a partition. Combined `(context_name, epoch, logid)` is globally unique. No RPC handler does bare `logid` point-lookup — safe. 3. Concurrency: `PartitionPool` uses `std::sync::Mutex` (library remains async-free). Server calls via `spawn_blocking` if needed. 4. `chrono` already a workspace dep — use `DateTime<Utc>::from_timestamp(epoch, 0).ordinal()` for day-of-year. 5. `lru = "0.12"` must be added to workspace + crate deps. 6. On file deletion in `delete_older_than`, also remove `-wal` and `-shm` companion files. 7. Main `hero_proc.db` retains all non-log tables; only the `logs` table init is removed from `factory.rs`.
despiegk commented 2026-03-20 05:48:17 +00:00
Author
Owner
Copy link

Test Results

hero_proc_lib

  • Total: 158
  • Passed: 155
  • Failed: 0
  • Ignored: 3

hero_proc_server

  • Total: 55
  • Passed: 55
  • Failed: 0
  • Ignored: 0

Status: ✅ All tests passing

Failure details (if any)

No failures.

## Test Results ### hero_proc_lib - Total: 158 - Passed: 155 - Failed: 0 - Ignored: 3 ### hero_proc_server - Total: 55 - Passed: 55 - Failed: 0 - Ignored: 0 ### Status: ✅ All tests passing <details> <summary>Failure details (if any)</summary> No failures. </details>
despiegk commented 2026-03-20 05:50:45 +00:00
Author
Owner
Copy link

Implementation committed: 7a1b59c

Browse: 7a1b59c

Implementation committed: `7a1b59c` Browse: https://forge.ourworld.tf/lhumina_code/hero_proc/commit/7a1b59c
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
development
development_sameh_hp02
main
latest
lab-latest
v0.5.0-rc2
v0.5.0-rc1
v0.4.4
v0.4.1
v0.1.1
Labels
Clear labels   
prio_critical

   
prio_low

   
type_bug

   
type_contact

   
type_issue

   
type_lead

   
type_question

   
type_story

   
type_task
No labels
prio_critical
prio_low
type_bug
type_contact
type_issue
type_lead
type_question
type_story
type_task
Milestone
Clear milestone
No items
No milestone
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
lhumina_code/hero_proc#15
No description provided.