-- ===================================================================== -- UPDATE — Convergence Platform : MySQL schema -- Engine: InnoDB / utf8mb4 -- -- Design notes: -- * Multi-tenant from day one. Nothing here is CSCAfrica-specific. -- * `channels` are PLATFORM-level shared lines (one WhatsApp number, one -- Telegram bot, etc.) — NOT per-org or per-event. The Routing Engine -- decides which event an inbound message belongs to. -- * `event_code` is globally unique because it is the routing token that -- contributors type across shared channels (e.g. "#NGR2027"). -- * `platform_admins` and the `events.auto_publish_*` columns are additions -- beyond the original spec — see README ("Schema additions"). -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- --------------------------------------------------------------------- -- Organizations -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS organizations ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(190) NOT NULL, slug VARCHAR(190) NOT NULL, contact_email VARCHAR(190) NOT NULL, status ENUM('active','suspended') NOT NULL DEFAULT 'active', -- Browser origins allowed to make AUTHENTICATED cross-origin calls -- (a custom admin/moderation console on the org's own domain). -- NULL/empty => no cross-origin authenticated access. JSON array of origins. allowed_origins JSON NULL DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_org_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Organization users (admins / moderators of a given org) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS org_users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id BIGINT UNSIGNED NOT NULL, name VARCHAR(190) NOT NULL, email VARCHAR(190) NOT NULL, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','moderator') NOT NULL DEFAULT 'moderator', status ENUM('active','disabled') NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_orguser_email (email), KEY idx_orguser_org (org_id), CONSTRAINT fk_orguser_org FOREIGN KEY (org_id) REFERENCES organizations (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Platform admins (US — operate shared channels, triage, oversight). -- Deliberately separate from org_users so platform staff are never -- accidentally scoped to a single organization. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS platform_admins ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(190) NOT NULL, email VARCHAR(190) NOT NULL, password_hash VARCHAR(255) NOT NULL, status ENUM('active','disabled') NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_padmin_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Events (owned by an org). event_code is the public routing token and -- is UNIQUE PLATFORM-WIDE. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS events ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id BIGINT UNSIGNED NOT NULL, title VARCHAR(190) NOT NULL, event_code VARCHAR(40) NOT NULL, -- e.g. NGR2027 (stored WITHOUT the leading '#') description TEXT NULL, status ENUM('draft','live','closed') NOT NULL DEFAULT 'draft', -- Auto-publish flag (spec: "design the flag but default it off"). -- NULL => auto-publish disabled (everything queues for moderation). -- Otherwise, submissions from a contributor at >= this trust level skip -- the queue and publish immediately. auto_publish_min_trust ENUM('medium','high') NULL DEFAULT NULL, -- Per-event channel opt-in. NULL => accept ALL channels (default/back-compat). -- Otherwise a JSON array of accepted channel types, e.g. ["whatsapp","web"]. accepted_channels JSON NULL DEFAULT NULL, starts_at DATETIME NULL, ends_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_event_code (event_code), -- fast routing lookup + platform-wide uniqueness KEY idx_event_org (org_id), KEY idx_event_status (status), CONSTRAINT fk_event_org FOREIGN KEY (org_id) REFERENCES organizations (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Channels — PLATFORM-level shared communication lines. -- One row per channel type (whatsapp/telegram/sms/email/web). -- config_json holds provider credentials + webhook verification secret. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS channels ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, channel_type ENUM('whatsapp','telegram','sms','email','web') NOT NULL, platform_identifier VARCHAR(190) NOT NULL, -- shared WA number / bot @username / SMS shortcode / inbound email addr config_json JSON NULL, -- provider creds, webhook secret, phone_number_id, etc. status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_channel_type (channel_type) -- one shared line per type ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Contributor accounts — email-keyed identity a person authenticates as. -- One account can own many channel identities (phone, telegram, …), so a -- person is recognized across channels once they've logged in with email+OTP. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS contributor_accounts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(190) NOT NULL, username VARCHAR(120) NULL, display_name VARCHAR(190) NULL, verified TINYINT(1) NOT NULL DEFAULT 0, -- email proven via OTP created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_account_email (email), UNIQUE KEY uq_account_username (username) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Contributors — a sender identity across any channel. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS contributors ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, identity_type ENUM('phone','telegram_id','email','app_user') NOT NULL, identity_value VARCHAR(190) NOT NULL, -- E.164 phone / telegram user id / email / app user id display_name VARCHAR(190) NULL, verified TINYINT(1) NOT NULL DEFAULT 0, trust_level ENUM('low','medium','high') NOT NULL DEFAULT 'low', blocked TINYINT(1) NOT NULL DEFAULT 0, -- Legacy simple-registration flag (superseded by channel login below; -- kept for display/back-compat, set on successful login). registered TINYINT(1) NOT NULL DEFAULT 0, registered_at DATETIME NULL, -- Channel login (email + OTP). A channel identity links to an account and -- holds a conversational auth state + a time-boxed posting session. account_id BIGINT UNSIGNED NULL, auth_state ENUM('none','awaiting_email','awaiting_register','awaiting_otp','active') NOT NULL DEFAULT 'none', pending_email VARCHAR(190) NULL, -- email entered, pre-verification otp_hash CHAR(64) NULL, otp_expires_at DATETIME NULL, otp_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, session_expires_at DATETIME NULL, -- 30-day posting window first_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_contrib_identity (identity_type, identity_value), KEY idx_contrib_trust (trust_level), KEY idx_contrib_account (account_id), CONSTRAINT fk_contrib_account FOREIGN KEY (account_id) REFERENCES contributor_accounts (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Updates — the normalized unit of content. Every channel funnels here. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS updates ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, event_id BIGINT UNSIGNED NOT NULL, contributor_id BIGINT UNSIGNED NOT NULL, channel_id BIGINT UNSIGNED NOT NULL, content_text TEXT NOT NULL, -- sanitized content (routing token stripped) update_type ENUM('statement','question') NOT NULL DEFAULT 'statement', media_url VARCHAR(500) NULL, media_type ENUM('none','image','video','audio','document','link','location') NOT NULL DEFAULT 'none', latitude DECIMAL(10,7) NULL, -- geotag (from WhatsApp/Telegram location) longitude DECIMAL(10,7) NULL, like_count INT UNSIGNED NOT NULL DEFAULT 0, -- denormalized for fast public feed / top-moments status ENUM('pending','approved','rejected','flagged') NOT NULL DEFAULT 'pending', provider_message_id VARCHAR(190) NULL, -- for webhook idempotency / dedupe moderated_by BIGINT UNSIGNED NULL, moderated_at DATETIME NULL, submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, raw_payload_json JSON NULL, -- original inbound payload, for audits PRIMARY KEY (id), KEY idx_updates_event_status (event_id, status, submitted_at), -- moderation queue + public feed KEY idx_updates_contributor (contributor_id), KEY idx_updates_channel (channel_id), UNIQUE KEY uq_updates_provider_msg (channel_id, provider_message_id), CONSTRAINT fk_update_event FOREIGN KEY (event_id) REFERENCES events (id) ON DELETE CASCADE, CONSTRAINT fk_update_contributor FOREIGN KEY (contributor_id) REFERENCES contributors (id) ON DELETE CASCADE, CONSTRAINT fk_update_channel FOREIGN KEY (channel_id) REFERENCES channels (id), CONSTRAINT fk_update_moderator FOREIGN KEY (moderated_by) REFERENCES org_users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- NOTE on the unique index above: MySQL treats multiple NULLs as distinct, -- so rows with a NULL provider_message_id (e.g. web-form submissions) do not -- collide. Webhook rows that DO carry a provider id are deduped per channel. -- --------------------------------------------------------------------- -- Unmatched messages — inbound the Routing Engine could not assign. -- Reviewed by PLATFORM ADMINS (not org moderators). -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS unmatched_messages ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, channel_id BIGINT UNSIGNED NOT NULL, raw_payload_json JSON NULL, sender_identity VARCHAR(190) NULL, parsed_content TEXT NULL, -- best-effort extracted body, to aid triage attempted_token VARCHAR(80) NULL, -- what we thought the token was, if any reason ENUM('no_event_code_found','invalid_event_code','ambiguous','line_tag_not_active','channel_not_accepted') NOT NULL, provider_message_id VARCHAR(190) NULL, resolved TINYINT(1) NOT NULL DEFAULT 0, received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_unmatched_resolved (resolved, received_at), KEY idx_unmatched_channel (channel_id), CONSTRAINT fk_unmatched_channel FOREIGN KEY (channel_id) REFERENCES channels (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Moderation log — audit trail of every moderation action. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS moderation_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, update_id BIGINT UNSIGNED NOT NULL, actor_id BIGINT UNSIGNED NULL, -- org_users.id (nullable: system/auto actions) action VARCHAR(40) NOT NULL, -- approve / reject / edit / flag_contributor / auto_publish ... note TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_modlog_update (update_id), CONSTRAINT fk_modlog_update FOREIGN KEY (update_id) REFERENCES updates (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- API keys — for org website widget/API consumers. -- Only the hash is stored; the plaintext is shown once at creation. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS api_keys ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id BIGINT UNSIGNED NOT NULL, label VARCHAR(120) NULL, key_prefix VARCHAR(16) NOT NULL, -- first chars, shown in UI for identification key_hash CHAR(64) NOT NULL, -- sha256(secret) scopes VARCHAR(255) NOT NULL DEFAULT 'public:read', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, revoked_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_apikey_hash (key_hash), KEY idx_apikey_org (org_id), CONSTRAINT fk_apikey_org FOREIGN KEY (org_id) REFERENCES organizations (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Rate limits — sliding-ish fixed-window counters. -- subject_type + subject_value keeps it generic (contributor id OR ip). -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS rate_limits ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, subject_type ENUM('contributor','ip') NOT NULL, subject_value VARCHAR(190) NOT NULL, event_id BIGINT UNSIGNED NULL, window_start DATETIME NOT NULL, count INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uq_ratelimit_window (subject_type, subject_value, event_id, window_start), KEY idx_ratelimit_window (window_start) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Webhook event log — structured logging of every inbound webhook and -- routing decision, for debugging channel integrations / observability. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS webhook_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, channel_type VARCHAR(20) NULL, provider_message_id VARCHAR(190) NULL, decision VARCHAR(40) NULL, -- routed / unmatched / duplicate / rejected_signature / line_tag event_id BIGINT UNSIGNED NULL, detail TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_webhooklog_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Update likes — reader engagement from org websites (via the widget). -- Deduped per (update, voter_token). voter_token is an opaque client id -- the widget stores in localStorage; no reader account required. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS update_likes ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, update_id BIGINT UNSIGNED NOT NULL, voter_token VARCHAR(80) NOT NULL, ip VARCHAR(64) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_like_voter (update_id, voter_token), KEY idx_like_update (update_id), CONSTRAINT fk_like_update FOREIGN KEY (update_id) REFERENCES updates (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Event followers — "Follow" / subscribe capture from org websites. -- Stores the subscription identity; actual notification delivery is a -- future concern (email/push), noted in the API docs. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS event_followers ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, event_id BIGINT UNSIGNED NOT NULL, identity_type ENUM('email','device') NOT NULL DEFAULT 'email', identity_value VARCHAR(190) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_follower (event_id, identity_type, identity_value), KEY idx_follower_event (event_id), CONSTRAINT fk_follower_event FOREIGN KEY (event_id) REFERENCES events (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Auth tokens — DB-backed REFRESH tokens (access tokens stay stateless JWTs). -- Enables long-lived custom dashboards without forcing re-login, plus -- server-side revocation (logout). Only the hash is stored. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS auth_tokens ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, principal_type ENUM('org_user','platform_admin') NOT NULL, principal_id BIGINT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL, -- sha256(refresh secret) user_agent VARCHAR(255) NULL, expires_at DATETIME NOT NULL, revoked_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_authtoken_hash (token_hash), KEY idx_authtoken_principal (principal_type, principal_id), KEY idx_authtoken_expiry (expires_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Platform settings — key/value overrides editable in the admin portal. -- A present value overrides the matching .env default (see src/Config.php). -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS platform_settings ( skey VARCHAR(64) NOT NULL, svalue TEXT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (skey) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET FOREIGN_KEY_CHECKS = 1; -- ===================================================================== -- Optional seed data (safe to run once). Password for both demo logins -- below is: password123 (change immediately in any real deployment) -- Hash generated with PHP password_hash(..., PASSWORD_DEFAULT). -- ===================================================================== -- INSERT INTO platform_admins (name, email, password_hash) -- VALUES ('Platform Admin', 'admin@update.local', -- '$2y$10$e0MYzXyjpJS7Pd0RVvHwHeFjHNMBpT2i3Xn8p3Zt3s8k9Qb2mLQO');