47 lines
1.6 KiB
PL/PgSQL
47 lines
1.6 KiB
PL/PgSQL
-- Chattyness Server Schema Tables
|
|
-- PostgreSQL 18 with PostGIS
|
|
--
|
|
-- Server-wide configuration, global resources, and settings
|
|
|
|
\set ON_ERROR_STOP on
|
|
|
|
BEGIN;
|
|
|
|
-- =============================================================================
|
|
-- Server Configuration (Singleton)
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE server.config (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
name public.nonempty_text NOT NULL,
|
|
description TEXT,
|
|
welcome_message TEXT,
|
|
|
|
default_scene_bounds public.scene_bounds NOT NULL
|
|
DEFAULT ST_MakeEnvelope(0, 0, 800, 600, 0),
|
|
max_users_per_channel INTEGER NOT NULL DEFAULT 50
|
|
CHECK (max_users_per_channel > 0 AND max_users_per_channel <= 1000),
|
|
|
|
message_rate_limit INTEGER NOT NULL DEFAULT 10 CHECK (message_rate_limit > 0),
|
|
message_rate_window_seconds INTEGER NOT NULL DEFAULT 60 CHECK (message_rate_window_seconds > 0),
|
|
|
|
allow_guest_access BOOLEAN NOT NULL DEFAULT true,
|
|
allow_user_uploads BOOLEAN NOT NULL DEFAULT true,
|
|
require_email_verification BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT chk_server_config_singleton CHECK (id = '00000000-0000-0000-0000-000000000001'::UUID)
|
|
);
|
|
|
|
COMMENT ON TABLE server.config IS 'Server-wide configuration settings (singleton table)';
|
|
|
|
INSERT INTO server.config (id, name, description) VALUES (
|
|
'00000000-0000-0000-0000-000000000001'::UUID,
|
|
'Chattyness Server',
|
|
'A 2D virtual chat world'
|
|
);
|
|
|
|
COMMIT;
|