Strategic Hub Specification

CREATE TABLE users (
    id                  UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    email               VARCHAR(320) NOT NULL UNIQUE,
    password_hash       VARCHAR(512) NOT NULL,
    full_name           VARCHAR(255) NOT NULL,
    role                VARCHAR(16)  NOT NULL DEFAULT 'staff'
                                     CHECK (role IN ('chief', 'director', 'officer', 'staff')),
    active              BOOLEAN      NOT NULL DEFAULT TRUE,
    storage_quota_bytes BIGINT,
    avatar_data         TEXT,
    department          VARCHAR(255),
    supervisor_id       UUID         REFERENCES users(id),
    notification_prefs  JSONB        DEFAULT '{}',
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE files (
    id             UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    parent_id      UUID         REFERENCES files (id) ON DELETE CASCADE,
    owner_id       UUID         NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    name           VARCHAR(512) NOT NULL,
    is_folder      BOOLEAN      NOT NULL DEFAULT FALSE,
    size_bytes     BIGINT       NOT NULL DEFAULT 0,
    mime_type      VARCHAR(255),
    classification VARCHAR(16)  NOT NULL DEFAULT 'TERBUKA'
                                CHECK (classification IN ('RAHSIA', 'SULIT', 'TERHAD', 'TERBUKA')),
    created_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    deleted_at     TIMESTAMPTZ,
    locked_by      UUID,
    locked_at      TIMESTAMPTZ,
    lock_reason    TEXT,
    search_vector  tsvector
);

CREATE TABLE file_shares (
    id          UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    file_id     UUID         NOT NULL REFERENCES files (id) ON DELETE CASCADE,
    user_id     UUID         NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    role        VARCHAR(16)  NOT NULL DEFAULT 'viewer'
                             CHECK (role IN ('owner', 'editor', 'viewer')),
    shared_by   UUID         NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    UNIQUE (file_id, user_id)
);

CREATE TABLE governance_requests (
    id              UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    type            VARCHAR(32)  NOT NULL CHECK (type IN (
                                     'FILE_LOCK', 'FILE_UNLOCK',
                                     'CLASSIFICATION_UPGRADE', 'CLASSIFICATION_DOWNGRADE'
                                 )),
    title           VARCHAR(255) NOT NULL,
    description     TEXT,
    status          VARCHAR(16)  NOT NULL DEFAULT 'PENDING'
                                 CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED')),
    requested_by    UUID         NOT NULL REFERENCES users (id),
    reviewed_by     UUID         REFERENCES users (id),
    target_file_id  UUID         REFERENCES files (id) ON DELETE SET NULL,
    metadata        JSONB,
    review_note     TEXT,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE permissions (
    id          UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    key         VARCHAR(64)  NOT NULL UNIQUE,
    description TEXT         NOT NULL
);

CREATE TABLE role_groups (
    id          UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    name        VARCHAR(128) NOT NULL,
    description TEXT         NOT NULL DEFAULT '',
    created_by  VARCHAR(128) NOT NULL,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE role_group_permissions (
    role_group_id UUID NOT NULL REFERENCES role_groups (id) ON DELETE CASCADE,
    permission_id UUID NOT NULL REFERENCES permissions (id) ON DELETE CASCADE,
    PRIMARY KEY (role_group_id, permission_id)
);

CREATE TABLE user_role_groups (
    user_id       UUID NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    role_group_id UUID NOT NULL REFERENCES role_groups (id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, role_group_id)
);

CREATE TABLE user_permissions (
    user_id       UUID NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    permission_id UUID NOT NULL REFERENCES permissions (id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, permission_id)
);

CREATE TABLE custom_roles (
    role_key    VARCHAR(32)  PRIMARY KEY,
    label       VARCHAR(64)  NOT NULL,
    level       SMALLINT     NOT NULL DEFAULT 1
);

CREATE TABLE role_implicit_permissions (
    role_key      VARCHAR(32) NOT NULL REFERENCES custom_roles (role_key) ON DELETE CASCADE,
    permission_id UUID        NOT NULL REFERENCES permissions (id) ON DELETE CASCADE,
    PRIMARY KEY (role_key, permission_id)
);

CREATE TABLE webauthn_credentials (
    id              UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id         UUID         NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    credential_id   TEXT         NOT NULL UNIQUE,
    public_key      TEXT         NOT NULL,
    sign_count      BIGINT       NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE user_sessions (
    id         UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id    UUID        NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    token      TEXT        NOT NULL UNIQUE,
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE password_resets (
    id          UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id     UUID         NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    token       VARCHAR(128) NOT NULL UNIQUE,
    expires_at  TIMESTAMPTZ  NOT NULL,
    used        BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE magic_links (
    id          UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id     UUID         NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    token       VARCHAR(128) NOT NULL UNIQUE,
    expires_at  TIMESTAMPTZ  NOT NULL,
    used        BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE system_config (
    key         VARCHAR(128) PRIMARY KEY,
    value       TEXT NOT NULL,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE audit_logs (
    id              UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id         UUID        REFERENCES users (id) ON DELETE SET NULL,
    action          VARCHAR(64) NOT NULL,
    target_resource VARCHAR(512),
    ip_address      VARCHAR(45),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE favorites (
    user_id    UUID        NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    file_id    UUID        NOT NULL REFERENCES files (id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (user_id, file_id)
);

CREATE TABLE file_versions (
    id             UUID          PRIMARY KEY DEFAULT uuid_generate_v4(),
    file_id        UUID          NOT NULL REFERENCES files (id) ON DELETE CASCADE,
    version_number INTEGER       NOT NULL,
    size_bytes     BIGINT        NOT NULL DEFAULT 0,
    storage_path   VARCHAR(1024) NOT NULL,
    uploaded_by    UUID          REFERENCES users (id) ON DELETE SET NULL,
    created_at     TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_audit_logs_user ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_created ON audit_logs(created_at DESC);