Module partition 

Raw table daily partitioning and retention automation (SCALE-09, #188).

The embedded analytics store’s raw table grows unbounded under high sustained throughput between retention prunes. This module rotates the raw table daily into partition tables (raw_YYYYMMDD) within the same SQLite database, making retention enforcement O(1) (DROP TABLE) instead of O(n) (DELETE rows + incremental vacuum).

Design

• The raw table is always the CURRENT day’s partition. The writer inserts into raw unchanged.
• At daily boundaries, maybe_rotate renames raw to raw_YYYYMMDD (yesterday’s date) and creates a new empty raw table with the full schema.
• A raw_all view UNION ALLs the raw table and all raw_YYYYMMDD partition tables, so the rollup and query layers read across all partitions without per-query changes.
• drop_expired_partitions drops raw_YYYYMMDD tables older than the raw retention period (O(1) DROP TABLE).
• The raw_partitions meta table tracks partition names and their date boundaries for introspection.

Why same-file partitions, not separate files

The issue proposed separate SQLite files. Same-file partition tables achieve the same O(1) retention and bounded raw table size while avoiding the complexity of ATTACH/DETACH, cross-file transactions, and connection management. SQLite’s DROP TABLE is O(1) (the pages are simply freed to the file’s free list), and incremental vacuum reclaims them. The raw_all view provides cross-partition querying without dynamic SQL.

Constants

SCHEMA_V11

The raw_partitions meta table (schema v11, SCALE-09 #188): tracks the partition table name and the date it covers. One row per rotated partition. The current day’s raw table is NOT listed here (it is the active partition; it will be listed when it is rotated).

Functions

drop_expired_partitions

Drop partition tables older than the raw retention period. Returns the number of partitions dropped. Each drop is O(1) (SQLite frees the table’s pages to the file’s free list; incremental vacuum reclaims them).

list_partitions

List all active partition names (including the current raw table), ordered by name. Useful for introspection and diagnostics.

maybe_rotate

Check if the current raw table has rows from a previous day. If so, rename it to raw_YYYYMMDD (the previous day’s date), create a fresh raw table, record the partition, and refresh the raw_all view. Returns the partition name if a rotation happened, None otherwise.

refresh_raw_all_view

Recreate the raw_all view as the UNION ALL of the raw table and all raw_YYYYMMDD partition tables. Called after each rotation and after dropping expired partitions.