schema.sql

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL -- plaintext for demo; hash in production
    created_at TIMESTAMP
    updated_at TIMESTAMP
    );

-- Preload demo user
INSERT INTO users (username, password) VALUES
    ('alice', 'password123')
    ON CONFLICT DO NOTHING;

-- Timecards table
CREATE TABLE IF NOT EXISTS timecards (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),
    clock_in TIMESTAMP,
    clock_out TIMESTAMP,
    created_at TIMESTAMP
    updated_at TIMESTAMP
    CONSTRAINT uq_user_active_timecard UNIQUE (user_id, clock_out)
    -- ensures only one active (open) timecard per user
    );

-- Breaks table
CREATE TABLE IF NOT EXISTS breaks (
    id SERIAL PRIMARY KEY,
    timecard_id INT NOT NULL REFERENCES timecards(id),
    break_in TIMESTAMP,
    break_out TIMESTAMP
    created_at TIMESTAMP
    updated_at TIMESTAMP
    );

-- Indexes for performance on row-level locking queries
CREATE INDEX IF NOT EXISTS idx_timecards_user_active ON timecards(user_id, clock_out);
CREATE INDEX IF NOT EXISTS idx_breaks_timecard ON breaks(timecard_id);