Architecting the Authoritative HR Identity Database Engine

-- Core Employee Table
CREATE TABLE IF NOT EXISTS employees (
    employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    user_principal_name TEXT UNIQUE NOT NULL,
    department TEXT NOT NULL,
    job_title TEXT NOT NULL,
    status TEXT CHECK(status IN ('Active', 'Transferred', 'Terminated')) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Execution Log Table for Audit & HTML Reporting
CREATE TABLE IF NOT EXISTS jml_sync_log (
    log_id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    action_type TEXT NOT NULL,
    status TEXT NOT NULL,
    details TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(employee_id) REFERENCES employees(employee_id)
);

# Import module & set path
Import-Module PSSQLite
$dbPath = "C:\IAM_Automation\hr_system.db"

# Query all active employees to drive Joiner logic
$activeUsers = Invoke-SqliteQuery -Database $dbPath -Query "SELECT * FROM employees WHERE status = 'Active'"

# Insert execution audit entry
$logQuery = "INSERT INTO jml_sync_log (employee_id, action_type, status, details) VALUES (1, 'JOINER', 'SUCCESS', 'Account provisioned')"
Invoke-SqliteQuery -Database $dbPath -Query $logQuery