-- Paga Staff Multipurpose Cooperative Society — schema -- Target: MySQL / MariaDB (shared cPanel hosting) -- Import via phpMyAdmin, or: mysql -u USER -p DBNAME < schema.sql SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- --------------------------------------------------------------------- -- members: every person in the cooperative (staff or not). Excos are -- members with is_exco = TRUE — matches the demo, where the 3 Excos -- vote as named individuals, not a separate identity system. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS members ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, member_number VARCHAR(20) NOT NULL UNIQUE, name VARCHAR(150) NOT NULL, phone VARCHAR(30) NULL, email VARCHAR(150) NULL, next_of_kin_name VARCHAR(150) NULL, next_of_kin_phone VARCHAR(30) NULL, balance DECIMAL(14,2) NOT NULL DEFAULT 0, monthly_contribution DECIMAL(14,2) NOT NULL DEFAULT 0, is_exco TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- users: login identity. One user per member. Two ways in: -- GOOGLE -> google_id set, password_hash NULL -- PASSWORD -> password_hash set, google_id NULL (invited members) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, member_id INT UNSIGNED NOT NULL UNIQUE, email VARCHAR(150) NOT NULL UNIQUE, google_id VARCHAR(100) NULL UNIQUE, password_hash VARCHAR(255) NULL, -- NULL until first successful login: a newly-approved member's user -- row doesn't know yet whether they'll set a password or sign in -- with Google, so this is set at activation time, not at creation. auth_provider ENUM('GOOGLE','PASSWORD') NULL, status ENUM('INVITED','ACTIVE') NOT NULL DEFAULT 'INVITED', invite_token_hash VARCHAR(255) NULL, invite_expires_at TIMESTAMP NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_users_member FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- requests: withdrawal / loan / bnpl. BNPL-specific fields are NULL -- for the other two types rather than a separate table — matches the -- demo's single-shape request object and keeps status transitions in -- one place. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS requests ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, member_id INT UNSIGNED NOT NULL, type ENUM('WITHDRAWAL','LOAN','BNPL') NOT NULL, amount DECIMAL(14,2) NOT NULL, term_months INT UNSIGNED NULL, reason TEXT NULL, status ENUM( 'PENDING_GUARANTORS', 'PENDING_EXCO_APPROVAL', 'APPROVED', 'DISBURSED', 'REJECTED', 'DECLINED_BY_GUARANTOR' ) NOT NULL, bnpl_item_description VARCHAR(200) NULL, bnpl_disbursement_type ENUM('VENDOR_PAYMENT','CASH_TO_MEMBER') NULL, bnpl_vendor_name VARCHAR(150) NULL, bnpl_installment_months INT UNSIGNED NULL, bnpl_interest_rate DECIMAL(5,4) NULL, bnpl_total_repayable DECIMAL(14,2) NULL, submitted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_requests_member FOREIGN KEY (member_id) REFERENCES members(id), INDEX idx_requests_member (member_id), INDEX idx_requests_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- guarantors: loan-only. Combined `amount` across a request's rows -- must equal 50% of the loan (enforced in application code). -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS guarantors ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, request_id INT UNSIGNED NOT NULL, member_id INT UNSIGNED NOT NULL, amount DECIMAL(14,2) NOT NULL, status ENUM('PENDING','APPROVED','DECLINED') NOT NULL DEFAULT 'PENDING', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_guarantors_request FOREIGN KEY (request_id) REFERENCES requests(id) ON DELETE CASCADE, CONSTRAINT fk_guarantors_member FOREIGN KEY (member_id) REFERENCES members(id), INDEX idx_guarantors_member (member_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- exco_votes -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS exco_votes ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, request_id INT UNSIGNED NOT NULL, exco_member_id INT UNSIGNED NOT NULL, vote ENUM('APPROVE','REJECT') NOT NULL, voted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_votes_request FOREIGN KEY (request_id) REFERENCES requests(id) ON DELETE CASCADE, CONSTRAINT fk_votes_exco FOREIGN KEY (exco_member_id) REFERENCES members(id), UNIQUE KEY uniq_vote_per_exco (request_id, exco_member_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- contribution_requests: standing monthly-contribution change requests -- (only created when policy requires Exco approval; self-service -- changes write straight to members.monthly_contribution) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS contribution_requests ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, member_id INT UNSIGNED NOT NULL, amount DECIMAL(14,2) NOT NULL, status ENUM('PENDING','APPROVED','REJECTED') NOT NULL DEFAULT 'PENDING', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, resolved_at TIMESTAMP NULL, CONSTRAINT fk_contribreq_member FOREIGN KEY (member_id) REFERENCES members(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- registration_requests: membership applications (open beyond Paga -- staff — this is the table an invite gets generated from on approval) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS registration_requests ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, phone VARCHAR(30) NOT NULL, email VARCHAR(150) NULL, next_of_kin_name VARCHAR(150) NULL, next_of_kin_phone VARCHAR(30) NULL, proposed_monthly_contribution DECIMAL(14,2) NOT NULL, status ENUM('PENDING','APPROVED','REJECTED') NOT NULL DEFAULT 'PENDING', submitted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, resolved_at TIMESTAMP NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- statements -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS statements ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, member_id INT UNSIGNED NOT NULL, date_from DATE NOT NULL, date_to DATE NOT NULL, purpose VARCHAR(250) NULL, status ENUM('PENDING','ISSUED') NOT NULL DEFAULT 'PENDING', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, issued_at TIMESTAMP NULL, CONSTRAINT fk_statements_member FOREIGN KEY (member_id) REFERENCES members(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- notifications: broadcast log -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS notifications ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, message TEXT NOT NULL, audience ENUM('ALL_MEMBERS') NOT NULL DEFAULT 'ALL_MEMBERS', channels JSON NOT NULL, sent_by_member_id INT UNSIGNED NULL, sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_notifications_sender FOREIGN KEY (sent_by_member_id) REFERENCES members(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- policy_settings: single row, id always 1 -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS policy_settings ( id TINYINT UNSIGNED PRIMARY KEY DEFAULT 1, interest_type ENUM('FLAT','REDUCING') NOT NULL DEFAULT 'REDUCING', loan_savings_linkage ENUM('AUTO','MANUAL') NOT NULL DEFAULT 'AUTO', statement_issuance ENUM('AUTO','SIGNOFF') NOT NULL DEFAULT 'SIGNOFF', exco_tie_break ENUM('AUTO_REJECT','ESCALATE') NOT NULL DEFAULT 'AUTO_REJECT', standing_contribution_approval ENUM('SELF_SERVICE','EXCO_APPROVAL') NOT NULL DEFAULT 'SELF_SERVICE', notification_channels JSON NOT NULL, CONSTRAINT chk_policy_single_row CHECK (id = 1) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO policy_settings (id, notification_channels) VALUES (1, JSON_ARRAY('IN_APP')) ON DUPLICATE KEY UPDATE id = id; -- --------------------------------------------------------------------- -- audit_log: not in the demo, added because status changes here touch -- real savings/loan decisions and "who approved what, when" needs to -- survive beyond the votes/guarantors rows themselves. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS audit_log ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, actor_member_id INT UNSIGNED NULL, action VARCHAR(100) NOT NULL, entity_type VARCHAR(50) NOT NULL, entity_id INT UNSIGNED NULL, details JSON NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_audit_actor FOREIGN KEY (actor_member_id) REFERENCES members(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; SET FOREIGN_KEY_CHECKS = 1;