130 lines
3.8 KiB
SQL
130 lines
3.8 KiB
SQL
-- ==========================================
|
|
-- CLUB DAGUERRE CAMERA DATABASE
|
|
-- Part 4: Authentication & Members
|
|
-- ==========================================
|
|
|
|
-- ==========================================
|
|
-- MEMBERS (Tier 1 Authentication)
|
|
-- ==========================================
|
|
CREATE TABLE members (
|
|
id SERIAL PRIMARY KEY,
|
|
member_number VARCHAR(20) UNIQUE,
|
|
email VARCHAR(200) UNIQUE NOT NULL,
|
|
name VARCHAR(200) NOT NULL,
|
|
password_hash VARCHAR(255),
|
|
|
|
-- Account status
|
|
account_active BOOLEAN DEFAULT TRUE,
|
|
member_since DATE,
|
|
membership_expires DATE,
|
|
|
|
-- Permissions
|
|
can_view_public BOOLEAN DEFAULT TRUE,
|
|
can_view_member_content BOOLEAN DEFAULT TRUE,
|
|
can_contribute BOOLEAN DEFAULT FALSE,
|
|
is_admin BOOLEAN DEFAULT FALSE,
|
|
|
|
-- Audit
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
last_login TIMESTAMP,
|
|
login_count INT DEFAULT 0
|
|
);
|
|
|
|
CREATE INDEX idx_members_email ON members(email);
|
|
CREATE INDEX idx_members_number ON members(member_number);
|
|
CREATE INDEX idx_members_active ON members(id) WHERE account_active = TRUE;
|
|
|
|
COMMENT ON TABLE members IS 'Club members - basic authentication and permissions';
|
|
|
|
-- ==========================================
|
|
-- RESEARCH ACCESS (Tier 2 Authentication)
|
|
-- ==========================================
|
|
CREATE TABLE research_access (
|
|
id SERIAL PRIMARY KEY,
|
|
member_id INT NOT NULL REFERENCES members(id) ON DELETE CASCADE,
|
|
|
|
has_research_access BOOLEAN DEFAULT FALSE,
|
|
access_granted_by VARCHAR(100),
|
|
access_granted_date DATE,
|
|
access_expires DATE,
|
|
|
|
research_purpose TEXT,
|
|
institution VARCHAR(200),
|
|
|
|
-- Usage tracking
|
|
access_count INT DEFAULT 0,
|
|
last_research_access TIMESTAMP,
|
|
|
|
notes TEXT
|
|
);
|
|
|
|
CREATE INDEX idx_research_member ON research_access(member_id);
|
|
CREATE INDEX idx_research_active ON research_access(member_id)
|
|
WHERE has_research_access = TRUE AND (access_expires IS NULL OR access_expires > CURRENT_DATE);
|
|
|
|
COMMENT ON TABLE research_access IS 'Research access grants for viewing uncertain content';
|
|
|
|
-- ==========================================
|
|
-- RESEARCH SESSIONS
|
|
-- ==========================================
|
|
CREATE TABLE research_sessions (
|
|
id SERIAL PRIMARY KEY,
|
|
member_id INT NOT NULL REFERENCES members(id) ON DELETE CASCADE,
|
|
session_token VARCHAR(64) UNIQUE NOT NULL,
|
|
|
|
ip_address INET,
|
|
user_agent TEXT,
|
|
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
expires_at TIMESTAMP NOT NULL,
|
|
last_activity TIMESTAMP,
|
|
|
|
resources_accessed TEXT[]
|
|
);
|
|
|
|
CREATE INDEX idx_sessions_member ON research_sessions(member_id);
|
|
CREATE INDEX idx_sessions_token ON research_sessions(session_token);
|
|
CREATE INDEX idx_sessions_active ON research_sessions(expires_at)
|
|
WHERE expires_at > NOW();
|
|
|
|
COMMENT ON TABLE research_sessions IS 'Active research sessions for audit trail';
|
|
|
|
-- ==========================================
|
|
-- SYSTEM CONFIG (Shared Settings)
|
|
-- ==========================================
|
|
CREATE TABLE system_config (
|
|
key VARCHAR(100) PRIMARY KEY,
|
|
value TEXT,
|
|
description TEXT,
|
|
updated_by VARCHAR(100),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE system_config IS 'System-wide configuration (including research password hash)';
|
|
|
|
-- ==========================================
|
|
-- AUDIT LOG
|
|
-- ==========================================
|
|
CREATE TABLE audit_log (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
member_id INT REFERENCES members(id),
|
|
|
|
action VARCHAR(100) NOT NULL,
|
|
resource_type VARCHAR(50),
|
|
resource_id INT,
|
|
|
|
ip_address INET,
|
|
user_agent TEXT,
|
|
|
|
details JSONB,
|
|
|
|
timestamp TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_audit_member ON audit_log(member_id);
|
|
CREATE INDEX idx_audit_action ON audit_log(action);
|
|
CREATE INDEX idx_audit_timestamp ON audit_log(timestamp DESC);
|
|
CREATE INDEX idx_audit_resource ON audit_log(resource_type, resource_id);
|
|
|
|
COMMENT ON TABLE audit_log IS 'Comprehensive audit log for all user actions';
|