Registers SQL Architecture

Role: Use this guide when writing code that interacts with the SQLite databases in Registers/.

1. Financial Register

Path: Registers/financial_register/financial_data.db

Core Tables

• transactions: The central ledger.
  • id (INTEGER PK)
  • date (TEXT ISO8601)
  • description (TEXT)
  • category (TEXT - see EXPENSE_CATEGORIES.md)
  • amount (REAL)
  • currency (TEXT ‘EUR’)
• assets: (Base table for polymorphism)
  • type (TEXT ‘real_estate’ | ‘stock’ | ‘license’)
  • valuation (REAL)
  • depreciation_rate (REAL)

2. Physical Assets (Tools & Equipment)

Path: Shared in financial_data.db

Tables

• physical_assets: Inherits from assets.
  • brand (TEXT)
  • asset_code (TEXT e.g., ‘DRI-MAK-001’)
  • tool_set (TEXT e.g., ‘WOO,MET’)
  • wattage (INTEGER)
  • is_brushless (BOOLEAN)
  • power_source (TEXT)
  • danger_level (INTEGER 0-5)
  • status (TEXT ‘active’|‘reserved’|‘maintenance’|‘broken’)
• asset_status_history: Tracks lifecycle.
  • asset_id (FK)
  • date (DATE)
  • status (TEXT)
  • value (TEXT)
  • notes (TEXT)

3. Candidate Register

Path: Registers/candidate_register/candidates.db

Core Tables

• candidates: People applying to join.
  • name (TEXT)
  • email (TEXT)
  • status (TEXT ‘applied’ | ‘interviewing’ | ‘accepted’)
  • skills (TEXT JSON)
• call_logs: Records of interviews.
  • candidate_id (FK)
  • notes (TEXT)
  • rating (INTEGER 1-10)

Rules for AI

1. No Schema Changes: Do not modify table structures without explicit user permission.
2. Atomic Commits: Always wrap inserts/updates in a transaction.
3. Backups: Verified daily via cron job (do not interfere).