schema.sql

-- schema.sql

-- Stores the final validated payroll data
CREATE TABLE IF NOT EXISTS payroll_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id TEXT NOT NULL,
    pay_period_start DATE NOT NULL,
    pay_period_end DATE NOT NULL,
    work_date DATE NOT NULL,
    pay_code TEXT NOT NULL CHECK (pay_code IN ('REG', 'OT', 'VAC', 'SICK', 'STAT')),
    hours DECIMAL(4, 2) NOT NULL CHECK (hours > 0 AND hours <= 24),
    cost_center TEXT,
    rate_override DECIMAL(10, 2) CHECK (rate_override IS NULL OR rate_override > 0),
    notes TEXT,
    source_file TEXT, -- To track which file this came from
    import_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Stores mappings between source column names and target fields
-- This allows the system to "remember" mappings for recurrent file formats
CREATE TABLE IF NOT EXISTS mapping_templates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source_format_key TEXT NOT NULL, -- A hash or name identifying the source format (e.g., column header string)
    source_column TEXT NOT NULL,
    target_field TEXT NOT NULL,
    is_user_overridden BOOLEAN DEFAULT FALSE,
    last_used DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(source_format_key, source_column)
);

-- Indexing for performance
CREATE INDEX IF NOT EXISTS idx_payroll_employee ON payroll_records(employee_id);
CREATE INDEX IF NOT EXISTS idx_payroll_work_date ON payroll_records(work_date);