Refactor the database schema to better model the data relationships:
Schema changes:
- Removed cards.card_type (redundant, identical to card_number)
- Removed transactions.card_id (unnecessary indirection)
- Added transactions.card_number (stores card number for all transactions)
- Made cards.customer_id NOT NULL (every card must belong to a customer)
- Made transactions.customer_id nullable (NULL for anonymized transactions)
Import logic changes:
- Only create cards for known customers (transactions with customer_number)
- Store card_number for ALL transactions (including anonymized)
- Skip cards/customer creation for anonymized transactions
Additional changes:
- Add 'db reset' command to drop and recreate database
- Update migration file with new schema
This simplifies queries and better reflects the data model:
- Cards table: authoritative mapping of card_number -> customer_id
- Transactions table: stores all raw data including anonymized cards
- Customer relationship via JOIN on card_number for known customers
Introduces separate databases and config files for dev, test, and prod
environments. The application now defaults to production, with --env flag
to specify alternative environments.
Changes:
- Update config.rs to support env-based loading (config.toml -> config.<env>.toml -> config.example.toml)
- Add Env enum (Prod, Dev, Test) with database name mapping
- Add --env flag to CLI commands (defaults to prod)
- Add 'db setup' command to create database and schema
- Split migrations into env-specific database creation and shared schema
- Update .gitignore to track config.example.toml but ignore config.toml and config.<env>.toml files
- Update config.example.toml as a template with placeholder values
- Delete 001_initial_schema.sql, replaced by 002_schema.sql + env-specific files
Config loading order:
1. config.toml (local override)
2. config.<env>.toml (environment-specific)
3. config.example.toml (fallback)
Database names:
- prod: rusty_petroleum
- dev: rusty_petroleum_dev
- test: rusty_petroleum_test
Usage:
cargo run -- db setup --env dev # Setup dev database
cargo run -- import data.csv --env dev # Import to dev
cargo run -- db setup # Setup prod (default)
cargo run -- import data.csv # Import to prod (default)
Introduces a new database layer to persist CSV transaction data in MariaDB,
enabling both invoicing and sales reporting queries. This replaces the
previous file-to-file-only processing.
Changes:
- Add sqlx, tokio, toml, anyhow, bigdecimal dependencies to Cargo.toml
- Create config module for TOML-based configuration (database credentials)
- Create db module with connection pool, models, and repository
- Create commands module with 'import' subcommand for CSV ingestion
- Refactor main.rs to use subcommand architecture (import/generate)
- Add migration SQL file for manual database schema creation
Schema (3 tables):
- customers: customer_number, card_report_group (1=fleet, 3/4=retail)
- cards: card_number, card_type, customer_id (nullable for anonymous)
- transactions: full transaction data with FK to cards/customers
Usage:
cargo run -- import <csv-file> # Import to database
cargo run -- generate <csv> <dir> # Generate HTML invoices (unchanged)
Configuration:
cp config.example.toml config.toml # Edit with database credentials
mysql < migrations/001_initial_schema.sql # Create database first
