Sentra Membership — Database Design¶

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 1. Table Members
CREATE TABLE members (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    member_number VARCHAR(30) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    biometric_hash TEXT, -- One-way face feature vector hash
    status VARCHAR(20) CHECK (status IN ('ACTIVE', 'FREEZE', 'INACTIVE')) DEFAULT 'ACTIVE' NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_members_number ON members(member_number);

-- 2. Table Packages & Subscriptions
CREATE TABLE packages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(50) NOT NULL,
    price NUMERIC(12,2) NOT NULL CHECK (price >= 0.00),
    validity_days INTEGER NOT NULL CHECK (validity_days > 0),
    total_sessions INTEGER DEFAULT 0 NOT NULL, -- 0 means unlimited
    is_active BOOLEAN DEFAULT TRUE NOT NULL
);

CREATE TABLE subscriptions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    member_id UUID NOT NULL REFERENCES members(id) ON DELETE RESTRICT,
    package_id UUID NOT NULL REFERENCES packages(id) ON DELETE RESTRICT,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status VARCHAR(20) CHECK (status IN ('PENDING', 'ACTIVE', 'FROZEN', 'EXPIRED', 'CANCELLED')) DEFAULT 'PENDING' NOT NULL,
    auto_renew BOOLEAN DEFAULT FALSE NOT NULL,
    subscription_gateway_id VARCHAR(100) UNIQUE, -- Midtrans/Xendit recurring ID
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_dates CHECK (end_date >= start_date)
);

CREATE INDEX idx_subs_member ON subscriptions(member_id);

-- 3. Table Trainers & Classes
CREATE TABLE trainers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    full_name VARCHAR(100) NOT NULL,
    specialization VARCHAR(50) NOT NULL,
    base_session_rate NUMERIC(12,2) NOT NULL CHECK (base_session_rate >= 0.00),
    is_active BOOLEAN DEFAULT TRUE NOT NULL
);

CREATE TABLE classes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    trainer_id UUID NOT NULL REFERENCES trainers(id) ON DELETE RESTRICT,
    name VARCHAR(100) NOT NULL,
    branch_id UUID NOT NULL,
    studio_name VARCHAR(50) NOT NULL,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    max_capacity INTEGER NOT NULL CHECK (max_capacity > 0),
    remaining_slots INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_class_times CHECK (end_time > start_time)
);

CREATE INDEX idx_classes_start ON classes(start_time);

-- 4. Table Bookings (High Concurrent Transactions)
CREATE TABLE bookings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    class_id UUID NOT NULL REFERENCES classes(id) ON DELETE RESTRICT,
    member_id UUID NOT NULL REFERENCES members(id) ON DELETE RESTRICT,
    booking_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    status VARCHAR(20) CHECK (status IN ('CONFIRMED', 'WAITING', 'ATTENDED', 'CANCELLED', 'NOSHOW')) DEFAULT 'CONFIRMED' NOT NULL,
    queue_position INTEGER DEFAULT 0 NOT NULL,
    cancelled_at TIMESTAMP WITH TIME ZONE,
    UNIQUE (class_id, member_id)
);

CREATE INDEX idx_bookings_class ON bookings(class_id);

-- 5. Table Wallets & Signed Transactions (Anti-Tampering)
CREATE TABLE wallets (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    member_id UUID UNIQUE NOT NULL REFERENCES members(id) ON DELETE RESTRICT,
    balance NUMERIC(12,2) DEFAULT 0.00 NOT NULL CHECK (balance >= 0.00),
    wallet_signature_hash TEXT NOT NULL, -- SHA256(member_id + balance + secret_salt)
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE wallet_transactions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    wallet_id UUID NOT NULL REFERENCES wallets(id) ON DELETE RESTRICT,
    transaction_type VARCHAR(20) CHECK (transaction_type IN ('TOPUP', 'DEBIT_BOOKING', 'REFUND', 'BONUS')) NOT NULL,
    amount NUMERIC(12,2) NOT NULL CHECK (amount > 0.00),
    transaction_signature VARCHAR(64) NOT NULL, -- Unique hash of transaction details
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- 6. Table Gate Access Logs (Anti-Passback Verification)
CREATE TABLE gate_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    member_id UUID NOT NULL REFERENCES members(id) ON DELETE RESTRICT,
    branch_id UUID NOT NULL,
    access_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    access_type VARCHAR(10) CHECK (access_type IN ('IN', 'OUT')) NOT NULL,
    verification_method VARCHAR(20) CHECK (verification_method IN ('QR', 'RFID', 'FACE')) NOT NULL,
    is_success BOOLEAN DEFAULT TRUE NOT NULL,
    failure_reason VARCHAR(100)
);

CREATE INDEX idx_gatelogs_member ON gate_logs(member_id, access_time DESC);