Tracking Financial Spending Across Dual Systems in SQLite vs. Time-Series Databases

Modeling Financial Data with Dual Identifier Systems and Time-Dependent Metrics

The core challenge involves designing a database to track daily financial spending across 150 funding buckets, each identified by two distinct systems (WBS and Task Numbers). These systems report conflicting values for the same bucket, requiring reconciliation. The solution must also monitor spending progress against budget thresholds and ownership assignments. While the user has experience with time-series databases (InfluxDB), relational databases like SQLite offer structured data management for cross-referencing identifiers, calculating discrepancies, and enforcing budget constraints. The critical technical considerations include schema design for dual-source reconciliation, efficient time-series aggregation, and maintaining referential integrity across budget cycles.

Data Model Mismatch Between Time-Series and Relational Paradigms

Time-series databases prioritize timestamped metric storage with tag-based indexing, which works well for IoT sensor streams but struggles with:

1. Multi-Source Entity Resolution: WBS and Task Numbers represent the same entity but require a unified reference table to avoid duplication. InfluxDB’s tag system would treat them as separate dimensions without native support for declaring equivalence relationships.
2. Discrepancy Calculations: Comparing WBS-reported spending against Task Number-reported spending demands JOIN operations across distinct data series. Time-series databases typically lack efficient JOIN support, forcing application-layer reconciliation.
3. Hierarchical Budget Enforcement: Each bucket has a total budget ($1000 for wbs-123) and ownership (Steff). Relational models enforce this via foreign keys and constraints; time-series databases would scatter these attributes across tags, risking inconsistency.
4. Historical State Tracking: Detecting when WBS and Task Number values diverge requires comparing adjacent daily entries. SQLite’s window functions simplify trend analysis, while time-series databases focus on raw metric retention without built-in delta calculations.

The user’s existing InfluxDB workflow would require duplicating ownership/budget metadata across every data point, increasing storage overhead. SQLite centralizes these attributes in reference tables, linking them to spending records via integer foreign keys. This reduces redundancy and enables centralized updates (e.g., reassigning ownership of wbs-123 from Steff to another user).

Implementing a Hybrid Relational-Time Series Schema in SQLite

Step 1: Entity Resolution Table
Create a funding_buckets table to unify WBS and Task Number identifiers, ensuring one-to-one mapping even when systems desynchronize:

CREATE TABLE funding_buckets (
    bucket_id INTEGER PRIMARY KEY,
    wbs_code TEXT NOT NULL UNIQUE,
    task_number TEXT NOT NULL UNIQUE,
    owner TEXT NOT NULL,
    total_budget REAL NOT NULL CHECK(total_budget > 0)
);

Insert records linking wbs-123 to tank_num1 and wbs-124 to tank_num2. The UNIQUE constraints prevent duplicate entries for the same WBS/Task Number.

Step 2: Daily Spending Records with Dual Source Tracking
Store daily snapshots from both systems in a spending_entries table, using CHECK constraints to enforce source validity:

CREATE TABLE spending_entries (
    entry_id INTEGER PRIMARY KEY,
    bucket_id INTEGER NOT NULL,
    source_system TEXT NOT NULL CHECK(source_system IN ('WBS', 'TASK_NUM')),
    spent_amount REAL NOT NULL CHECK(spent_amount >= 0),
    entry_date DATE NOT NULL DEFAULT (DATE('now')),
    FOREIGN KEY (bucket_id) REFERENCES funding_buckets(bucket_id)
);

For each bucket, insert two rows daily—one for each source system. This captures discrepancies directly in the data model.

Step 3: Discrepancy Detection via Materialized View
Build a view that calculates daily differences between WBS and Task Number reports:

CREATE VIEW spending_discrepancies AS
SELECT
    wbs.entry_date,
    wbs.bucket_id,
    (wbs.spent_amount - task.spent_amount) AS amount_diff,
    (wbs.spent_amount / b.total_budget) * 100 AS wbs_percent_spent,
    (task.spent_amount / b.total_budget) * 100 AS task_percent_spent
FROM
    (SELECT * FROM spending_entries WHERE source_system = 'WBS') AS wbs
    JOIN (SELECT * FROM spending_entries WHERE source_system = 'TASK_NUM') AS task
        ON wbs.bucket_id = task.bucket_id AND wbs.entry_date = task.entry_date
    JOIN funding_buckets b ON wbs.bucket_id = b.bucket_id;

This view performs a self-join on spending_entries to align WBS and Task Number data by date/bucket, computing absolute and percentage differences.

Step 4: Budget Threshold Alerts with Triggers
Automate spending alerts using a trigger that checks for overages when new entries are inserted:

CREATE TRIGGER check_budget_overage
AFTER INSERT ON spending_entries
FOR EACH ROW
BEGIN
    SELECT
        CASE
            WHEN NEW.spent_amount > (SELECT total_budget FROM funding_buckets WHERE bucket_id = NEW.bucket_id)
                THEN RAISE(ABORT, 'Spent amount exceeds total budget for bucket_id ' || NEW.bucket_id)
        END;
END;

This prevents data entry errors where spent_amount surpasses the bucket’s total_budget. For softer warnings, replace ABORT with a logging mechanism.

Step 5: Time-Series Aggregation with Window Functions
Generate weekly spending summaries using SQLite’s date functions and windowing:

SELECT
    bucket_id,
    strftime('%Y-%W', entry_date) AS week_number,
    SUM(spent_amount) OVER (
        PARTITION BY bucket_id
        ORDER BY entry_date
        RANGE BETWEEN CURRENT ROW AND 6 DAYS FOLLOWING
    ) AS weekly_spent
FROM spending_entries
WHERE source_system = 'WBS';

This computes rolling weekly totals per bucket, leveraging SQLite’s window function support—capabilities absent in many time-series databases that rely on custom query languages.

Step 6: Ownership-Based Access Control
Extend the schema to support multi-user applications by adding a users table and linking it to funding buckets:

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    role TEXT NOT NULL CHECK(role IN ('viewer', 'approver', 'admin'))
);

ALTER TABLE funding_buckets ADD COLUMN owned_by INTEGER NOT NULL REFERENCES users(user_id);

This enables role-based access to spending data, critical for audit compliance.

Migration from InfluxDB: Data Normalization
Existing InfluxDB data must be denormalized into the relational structure. For example, a time-series entry like:
finance,source=WBS,wbs=123 spent=100,budget=1000 1712659200
Becomes:

1. Lookup bucket_id from funding_buckets where wbs_code = '123'.
2. Insert into spending_entries with source_system = 'WBS', spent_amount = 100, and entry_date = 1712659200.

Performance Optimization

• Indexing Strategy: Composite indexes on (bucket_id, entry_date) accelerate time-range queries and JOINs in the spending_discrepancies view.
• Partial Indexes: For frequently filtered columns like source_system, create indexes that cover active buckets:
  CREATE INDEX idx_wbs_entries ON spending_entries(bucket_id, entry_date) WHERE source_system = 'WBS';
• Batch Inserts: Use SQLite’s transaction bundling to insert daily records for all 150 buckets in a single transaction, reducing I/O overhead.

Comparison with InfluxDB Workflow

• Data Integrity: SQLite’s foreign keys and CHECK constraints prevent orphaned records and invalid source systems, whereas InfluxDB relies on application-layer validation.
• Storage Efficiency: Storing owner and budget metadata once per bucket in SQLite vs. repeating them in every InfluxDB data point reduces disk usage by ~80% for 150 buckets with daily updates.
• Query Flexibility: Calculating weekly spending differences requires a single SQL query with JOINs and window functions in SQLite vs. multiple Flux queries with post-processing in InfluxDB.

Alternative: Hybrid Approach with SQLite and Time-Series
For high-frequency updates (sub-hourly), offload raw metric collection to InfluxDB while using SQLite for daily reconciliation and reporting. A cron job could aggregate InfluxDB data into SQLite nightly, leveraging both systems’ strengths.

Debugging Common Pitfalls

• Identifier Collisions: Use SQLite’s UNIQUE constraints to catch duplicate WBS/Task Number entries during initial data import.
• Time Zone Handling: Store entry_date as UTC with ISO8601 strings (YYYY-MM-DDTHH:MM:SSZ) to avoid local time ambiguity.
• Concurrency: Enable SQLite’s WAL mode (PRAGMA journal_mode=WAL;) if multiple processes update spending records simultaneously.

By adopting SQLite, the user gains a vertically integrated solution for financial tracking with built-in discrepancy detection, budget enforcement, and ownership tracking—capabilities requiring significant custom development in a time-series database. The relational model’s rigidity ensures data consistency, while SQLite’s portability and Python integration (via sqlite3 module) simplify deployment alongside existing spreadsheet parsing scripts.