-- ============================================================================ -- 05 CMS: pages, SEO, images (with required alt text), themes, menus, home sections -- ============================================================================ CREATE TABLE pages ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, slug varchar(120) NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9]+(?:[-/][a-z0-9]+)*$'), template varchar(40) NOT NULL DEFAULT 'default', -- file in the active theme is_system boolean NOT NULL DEFAULT false, -- home, shop, search: cannot be deleted status varchar(10) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','enabled','disabled')), -- SEO fields editable by the Admin title_tag varchar(160), meta_description varchar(320), meta_keywords varchar(300), canonical_url varchar(300), robots varchar(40) NOT NULL DEFAULT 'index,follow', og_image_path varchar(255), h1_text varchar(160), h2_text varchar(160), -- hero section hero_headline varchar(160), hero_subline varchar(300), hero_cta_primary_label varchar(40), hero_cta_primary_url varchar(255), hero_cta_secondary_label varchar(40), hero_cta_secondary_url varchar(255), hero_image_path varchar(255), hero_image_alt varchar(200), content_html text, -- sanitised on save created_by_staff_id bigint REFERENCES staff_users(id) ON DELETE SET NULL, updated_by_staff_id bigint REFERENCES staff_users(id) ON DELETE SET NULL, deleted_at timestamptz, deleted_by_staff_id bigint REFERENCES staff_users(id) ON DELETE SET NULL, purge_after timestamptz, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX pages_status_idx ON pages (status) WHERE deleted_at IS NULL; -- Every image on a page must carry alt text: the NOT NULL + length check makes -- "forgot the alt attribute" impossible at the database level. CREATE TABLE page_images ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, page_id bigint REFERENCES pages(id) ON DELETE CASCADE, path varchar(255) NOT NULL, alt_text varchar(200) NOT NULL CHECK (length(btrim(alt_text)) >= 3), title_text varchar(160), width integer, height integer, created_by_staff_id bigint REFERENCES staff_users(id) ON DELETE SET NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX page_images_page_idx ON page_images (page_id); -- ---------------------------------------------------------------------------- -- Themes (WordPress-style): folders under /themes, each with theme.json and a thumbnail -- ---------------------------------------------------------------------------- CREATE TABLE themes ( slug varchar(60) PRIMARY KEY CHECK (slug ~ '^[a-z0-9-]+$'), -- = folder name name varchar(100) NOT NULL, version varchar(20) NOT NULL, author varchar(100), description varchar(300), thumbnail_path varchar(255) NOT NULL, -- relative to the theme folder; required manifest jsonb NOT NULL DEFAULT '{}'::jsonb, is_active boolean NOT NULL DEFAULT false, is_installed boolean NOT NULL DEFAULT true, last_scanned_at timestamptz NOT NULL DEFAULT now(), activated_at timestamptz, activated_by_staff_id bigint REFERENCES staff_users(id) ON DELETE SET NULL, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() ); -- Exactly one theme can be active at a time CREATE UNIQUE INDEX themes_one_active ON themes (is_active) WHERE is_active = true; CREATE TABLE theme_settings ( theme_slug varchar(60) NOT NULL REFERENCES themes(slug) ON DELETE CASCADE, key varchar(80) NOT NULL, value jsonb NOT NULL, PRIMARY KEY (theme_slug, key) ); -- ---------------------------------------------------------------------------- -- Navigation menus -- ---------------------------------------------------------------------------- CREATE TABLE menus ( id smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, location varchar(30) NOT NULL UNIQUE, -- header, footer, footer_legal, mobile name varchar(60) NOT NULL ); CREATE TABLE menu_items ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, menu_id smallint NOT NULL REFERENCES menus(id) ON DELETE CASCADE, parent_id integer REFERENCES menu_items(id) ON DELETE CASCADE, label varchar(60) NOT NULL, url varchar(255) NOT NULL, page_id bigint REFERENCES pages(id) ON DELETE SET NULL, opens_new_tab boolean NOT NULL DEFAULT false, requires_feature varchar(60), -- e.g. shop_enabled: hidden while that switch is off sort_order smallint NOT NULL DEFAULT 0, is_active boolean NOT NULL DEFAULT true ); CREATE INDEX menu_items_menu_idx ON menu_items (menu_id, sort_order); -- ---------------------------------------------------------------------------- -- Home page sections: "Trending Absurdities in Nigeria", "Most Popular ...". -- Rows x columns are Admin-configurable (default: 2 rows of 3 columns). -- ---------------------------------------------------------------------------- CREATE TABLE home_sections ( id smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, section_key varchar(30) NOT NULL UNIQUE CHECK (section_key IN ('trending','popular','recent','resolved')), heading_h2 varchar(120) NOT NULL, intro_text varchar(300), columns_count smallint NOT NULL DEFAULT 3 CHECK (columns_count BETWEEN 1 AND 4), rows_count smallint NOT NULL DEFAULT 2 CHECK (rows_count BETWEEN 1 AND 6), source_rule varchar(20) NOT NULL DEFAULT 'auto' CHECK (source_rule IN ('auto','manual_mix')), time_window_days smallint NOT NULL DEFAULT 7, sort_order smallint NOT NULL DEFAULT 0, is_enabled boolean NOT NULL DEFAULT true, updated_by_staff_id bigint REFERENCES staff_users(id) ON DELETE SET NULL, updated_at timestamptz NOT NULL DEFAULT now() ); -- Optional editorial pins that always show first within a section CREATE TABLE home_section_pins ( section_id smallint NOT NULL REFERENCES home_sections(id) ON DELETE CASCADE, post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE, sort_order smallint NOT NULL DEFAULT 0, expires_at timestamptz, PRIMARY KEY (section_id, post_id) ); -- Short captivating descriptions (editorial blurbs) per post, used on cards CREATE TABLE post_card_blurbs ( post_id bigint PRIMARY KEY REFERENCES posts(id) ON DELETE CASCADE, blurb varchar(220) NOT NULL, generated_by varchar(10) NOT NULL DEFAULT 'ai' CHECK (generated_by IN ('ai','staff','author')), updated_at timestamptz NOT NULL DEFAULT now() ); CREATE TRIGGER pages_updated BEFORE UPDATE ON pages FOR EACH ROW EXECUTE FUNCTION set_updated_at(); CREATE TRIGGER themes_updated BEFORE UPDATE ON themes FOR EACH ROW EXECUTE FUNCTION set_updated_at();