411 lines
16 KiB
PL/PgSQL
411 lines
16 KiB
PL/PgSQL
-- Chattyness Realm Schema Tables
|
|
-- PostgreSQL 18 with PostGIS
|
|
--
|
|
-- Realms, scenes, memberships, realm props, and realm-level moderation
|
|
|
|
\set ON_ERROR_STOP on
|
|
|
|
BEGIN;
|
|
|
|
-- =============================================================================
|
|
-- Realms
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE realm.realms (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
name public.nonempty_text NOT NULL,
|
|
slug public.slug NOT NULL,
|
|
description TEXT,
|
|
tagline TEXT,
|
|
|
|
owner_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE RESTRICT,
|
|
|
|
privacy realm.realm_privacy NOT NULL DEFAULT 'public',
|
|
is_nsfw BOOLEAN NOT NULL DEFAULT false,
|
|
min_reputation_tier server.reputation_tier NOT NULL DEFAULT 'guest',
|
|
|
|
theme_color public.hex_color,
|
|
banner_image_path public.asset_path,
|
|
thumbnail_path public.asset_path,
|
|
|
|
max_users INTEGER NOT NULL DEFAULT 100 CHECK (max_users > 0 AND max_users <= 10000),
|
|
allow_guest_access BOOLEAN NOT NULL DEFAULT true,
|
|
allow_user_teleport BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
default_scene_id UUID,
|
|
|
|
-- Default avatars for this realm (optional, override server defaults)
|
|
-- FK constraints added in 035_realm_avatars.sql after realm.avatars exists
|
|
default_avatar_neutral_child UUID,
|
|
default_avatar_neutral_adult UUID,
|
|
default_avatar_male_child UUID,
|
|
default_avatar_male_adult UUID,
|
|
default_avatar_female_child UUID,
|
|
default_avatar_female_adult UUID,
|
|
|
|
member_count INTEGER NOT NULL DEFAULT 0 CHECK (member_count >= 0),
|
|
current_user_count INTEGER NOT NULL DEFAULT 0 CHECK (current_user_count >= 0),
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_realm_realms_slug UNIQUE (slug)
|
|
);
|
|
|
|
COMMENT ON TABLE realm.realms IS 'Themed virtual spaces';
|
|
|
|
CREATE INDEX idx_realm_realms_owner ON realm.realms (owner_id);
|
|
CREATE INDEX idx_realm_realms_privacy ON realm.realms (privacy);
|
|
CREATE INDEX idx_realm_realms_public ON realm.realms (privacy, is_nsfw) WHERE privacy = 'public';
|
|
|
|
-- =============================================================================
|
|
-- Scenes
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE realm.scenes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,
|
|
|
|
name public.nonempty_text NOT NULL,
|
|
slug public.slug NOT NULL,
|
|
description TEXT,
|
|
|
|
background_image_path public.asset_path,
|
|
background_image_url public.url,
|
|
background_color public.hex_color DEFAULT '#1a1a2e',
|
|
bounds public.scene_bounds NOT NULL DEFAULT ST_MakeEnvelope(0, 0, 800, 600, 0),
|
|
dimension_mode realm.dimension_mode NOT NULL DEFAULT 'fixed',
|
|
|
|
ambient_audio_id UUID REFERENCES server.audio(id) ON DELETE SET NULL,
|
|
ambient_volume public.percentage DEFAULT 0.5,
|
|
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
|
|
is_entry_point BOOLEAN NOT NULL DEFAULT false,
|
|
is_hidden BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_realm_scenes_slug UNIQUE (realm_id, slug)
|
|
);
|
|
|
|
COMMENT ON TABLE realm.scenes IS 'Rooms within a realm';
|
|
|
|
CREATE INDEX idx_realm_scenes_realm ON realm.scenes (realm_id);
|
|
CREATE INDEX idx_realm_scenes_realm_order ON realm.scenes (realm_id, sort_order);
|
|
|
|
-- Add FK for default_scene_id
|
|
ALTER TABLE realm.realms
|
|
ADD CONSTRAINT fk_realm_realms_default_scene
|
|
FOREIGN KEY (default_scene_id) REFERENCES realm.scenes(id) ON DELETE SET NULL;
|
|
|
|
-- =============================================================================
|
|
-- Realm Memberships
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE realm.memberships (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
nickname public.display_name,
|
|
|
|
role realm.realm_role NOT NULL DEFAULT 'member',
|
|
role_granted_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
role_granted_at TIMESTAMPTZ,
|
|
|
|
exp_score BIGINT NOT NULL DEFAULT 0 CHECK (exp_score >= 0),
|
|
|
|
last_scene_id UUID REFERENCES realm.scenes(id) ON DELETE SET NULL,
|
|
last_position public.virtual_point,
|
|
|
|
last_visited_at TIMESTAMPTZ,
|
|
total_time_seconds BIGINT NOT NULL DEFAULT 0 CHECK (total_time_seconds >= 0),
|
|
|
|
script_state JSONB NOT NULL DEFAULT '{}',
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_realm_memberships UNIQUE (realm_id, user_id)
|
|
);
|
|
|
|
COMMENT ON TABLE realm.memberships IS 'User membership within a realm';
|
|
|
|
CREATE UNIQUE INDEX uq_realm_memberships_nickname ON realm.memberships (realm_id, lower(nickname)) WHERE nickname IS NOT NULL;
|
|
CREATE INDEX idx_realm_memberships_realm ON realm.memberships (realm_id);
|
|
CREATE INDEX idx_realm_memberships_user ON realm.memberships (user_id);
|
|
CREATE INDEX idx_realm_memberships_role ON realm.memberships (realm_id, role) WHERE role IN ('owner', 'moderator', 'builder');
|
|
|
|
-- =============================================================================
|
|
-- Realm Scripts
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE realm.realm_scripts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,
|
|
|
|
name public.nonempty_text NOT NULL,
|
|
description TEXT,
|
|
|
|
source TEXT NOT NULL,
|
|
config JSONB NOT NULL DEFAULT '{}',
|
|
state JSONB NOT NULL DEFAULT '{}',
|
|
|
|
is_enabled BOOLEAN NOT NULL DEFAULT true,
|
|
run_on_realm_enter BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
COMMENT ON TABLE realm.realm_scripts IS 'Rhai scripts for realms';
|
|
|
|
CREATE INDEX idx_realm_realm_scripts_realm ON realm.realm_scripts (realm_id);
|
|
CREATE INDEX idx_realm_realm_scripts_enabled ON realm.realm_scripts (realm_id, is_enabled) WHERE is_enabled = true;
|
|
|
|
-- =============================================================================
|
|
-- Realm Prop Library (moved from props.realm_props)
|
|
-- =============================================================================
|
|
-- Custom props specific to a realm (can only be used in that realm)
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE realm.props (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,
|
|
|
|
name public.nonempty_text NOT NULL,
|
|
slug public.slug NOT NULL,
|
|
description TEXT,
|
|
tags TEXT[] NOT NULL DEFAULT '{}',
|
|
|
|
asset_path public.asset_path NOT NULL,
|
|
thumbnail_path public.asset_path,
|
|
|
|
-- Default avatar positioning
|
|
default_layer server.avatar_layer,
|
|
default_emotion server.emotion_state,
|
|
default_position SMALLINT CHECK (default_position IS NULL OR default_position BETWEEN 0 AND 8),
|
|
|
|
-- Default scale factor for dropped props (10% - 1000%)
|
|
default_scale REAL NOT NULL DEFAULT 1.0 CHECK (default_scale >= 0.1 AND default_scale <= 10.0),
|
|
|
|
is_unique BOOLEAN NOT NULL DEFAULT false,
|
|
is_transferable BOOLEAN NOT NULL DEFAULT true,
|
|
is_droppable BOOLEAN NOT NULL DEFAULT true,
|
|
is_public BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
available_from TIMESTAMPTZ,
|
|
available_until TIMESTAMPTZ,
|
|
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_realm_props_slug UNIQUE (realm_id, slug),
|
|
CONSTRAINT chk_realm_props_availability CHECK (
|
|
available_from IS NULL OR available_until IS NULL OR available_from < available_until
|
|
),
|
|
-- Props can be: non-avatar (all NULL), content layer, OR emotion layer
|
|
CONSTRAINT chk_realm_props_positioning CHECK (
|
|
(default_layer IS NULL AND default_emotion IS NULL AND default_position IS NULL) OR
|
|
(default_layer IS NOT NULL AND default_emotion IS NULL AND default_position IS NOT NULL) OR
|
|
(default_layer IS NULL AND default_emotion IS NOT NULL AND default_position IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
COMMENT ON TABLE realm.props IS 'Realm-specific prop library';
|
|
|
|
CREATE INDEX idx_realm_props_realm ON realm.props (realm_id);
|
|
CREATE INDEX idx_realm_props_tags ON realm.props USING GIN (tags);
|
|
CREATE INDEX idx_realm_props_active ON realm.props (realm_id, is_active) WHERE is_active = true;
|
|
CREATE INDEX idx_realm_props_public ON realm.props (realm_id, is_public) WHERE is_public = true;
|
|
|
|
COMMENT ON COLUMN realm.props.is_public IS
|
|
'When true, prop appears in the public Realm inventory tab. Uses filtered index idx_realm_props_public.';
|
|
|
|
-- =============================================================================
|
|
-- Add Foreign Keys to auth tables (now that realm.realms and realm.props exist)
|
|
-- =============================================================================
|
|
|
|
-- Add FK for auth.inventory.realm_prop_id
|
|
ALTER TABLE auth.inventory
|
|
ADD CONSTRAINT fk_auth_inventory_realm_prop
|
|
FOREIGN KEY (realm_prop_id) REFERENCES realm.props(id) ON DELETE SET NULL;
|
|
|
|
-- Add FK for auth.active_avatars.realm_id
|
|
ALTER TABLE auth.active_avatars
|
|
ADD CONSTRAINT fk_auth_active_avatars_realm
|
|
FOREIGN KEY (realm_id) REFERENCES realm.realms(id) ON DELETE CASCADE;
|
|
|
|
-- =============================================================================
|
|
-- Realm-Level Moderation: Reports
|
|
-- =============================================================================
|
|
-- Reports are always realm-scoped (but server admins can resolve them)
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE realm.reports (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,
|
|
|
|
reporter_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
reported_user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
category TEXT NOT NULL,
|
|
description TEXT NOT NULL,
|
|
evidence JSONB NOT NULL DEFAULT '[]',
|
|
|
|
-- Context
|
|
scene_id UUID REFERENCES realm.scenes(id) ON DELETE SET NULL,
|
|
message_id UUID, -- FK added when chat schema loads
|
|
|
|
status server.report_status NOT NULL DEFAULT 'pending',
|
|
resolved_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
resolved_at TIMESTAMPTZ,
|
|
resolution_notes TEXT,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT chk_realm_reports_not_self CHECK (reporter_id != reported_user_id)
|
|
);
|
|
|
|
COMMENT ON TABLE realm.reports IS 'User reports within a realm';
|
|
|
|
CREATE INDEX idx_realm_reports_realm ON realm.reports (realm_id);
|
|
CREATE INDEX idx_realm_reports_reporter ON realm.reports (reporter_id);
|
|
CREATE INDEX idx_realm_reports_reported ON realm.reports (reported_user_id);
|
|
CREATE INDEX idx_realm_reports_status ON realm.reports (realm_id, status) WHERE status = 'pending';
|
|
|
|
-- =============================================================================
|
|
-- Realm-Level Moderation: Realm Bans
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE realm.bans (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
reason TEXT NOT NULL,
|
|
evidence JSONB NOT NULL DEFAULT '[]',
|
|
|
|
banned_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
expires_at TIMESTAMPTZ, -- NULL = permanent
|
|
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
unbanned_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
unbanned_at TIMESTAMPTZ,
|
|
unban_reason TEXT,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_realm_bans_active UNIQUE (realm_id, user_id) -- Only one active ban per user per realm
|
|
);
|
|
|
|
COMMENT ON TABLE realm.bans IS 'Realm-specific user bans';
|
|
|
|
CREATE INDEX idx_realm_bans_realm ON realm.bans (realm_id);
|
|
CREATE INDEX idx_realm_bans_user ON realm.bans (user_id);
|
|
CREATE INDEX idx_realm_bans_active ON realm.bans (realm_id, user_id, is_active) WHERE is_active = true;
|
|
CREATE INDEX idx_realm_bans_expires ON realm.bans (expires_at) WHERE expires_at IS NOT NULL AND is_active = true;
|
|
|
|
-- =============================================================================
|
|
-- Realm-Level Moderation: Realm Mutes
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE realm.mutes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
reason TEXT NOT NULL,
|
|
|
|
muted_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
expires_at TIMESTAMPTZ, -- NULL = permanent
|
|
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
unmuted_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
unmuted_at TIMESTAMPTZ,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
COMMENT ON TABLE realm.mutes IS 'Realm-specific user mutes (cannot send messages in this realm)';
|
|
|
|
CREATE INDEX idx_realm_mutes_realm ON realm.mutes (realm_id);
|
|
CREATE INDEX idx_realm_mutes_user ON realm.mutes (user_id);
|
|
CREATE INDEX idx_realm_mutes_active ON realm.mutes (realm_id, user_id, is_active) WHERE is_active = true;
|
|
|
|
-- =============================================================================
|
|
-- Realm-Level Moderation: Content Filters
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE realm.content_filters (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,
|
|
|
|
pattern TEXT NOT NULL,
|
|
is_regex BOOLEAN NOT NULL DEFAULT false,
|
|
is_case_sensitive BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
action server.filter_action NOT NULL DEFAULT 'block',
|
|
replacement TEXT,
|
|
|
|
reason TEXT,
|
|
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
COMMENT ON TABLE realm.content_filters IS 'Realm-specific content filtering rules';
|
|
|
|
CREATE INDEX idx_realm_content_filters_realm ON realm.content_filters (realm_id);
|
|
CREATE INDEX idx_realm_content_filters_active ON realm.content_filters (realm_id, is_active) WHERE is_active = true;
|
|
|
|
-- =============================================================================
|
|
-- Realm-Level Moderation: Action Log
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE realm.moderation_actions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
realm_id UUID NOT NULL REFERENCES realm.realms(id) ON DELETE CASCADE,
|
|
|
|
action_type server.action_type NOT NULL,
|
|
|
|
target_user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
moderator_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
|
|
reason TEXT NOT NULL,
|
|
evidence JSONB NOT NULL DEFAULT '[]',
|
|
metadata JSONB NOT NULL DEFAULT '{}',
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
COMMENT ON TABLE realm.moderation_actions IS 'Log of realm-level moderation actions';
|
|
|
|
CREATE INDEX idx_realm_moderation_actions_realm ON realm.moderation_actions (realm_id);
|
|
CREATE INDEX idx_realm_moderation_actions_target ON realm.moderation_actions (target_user_id);
|
|
CREATE INDEX idx_realm_moderation_actions_moderator ON realm.moderation_actions (moderator_id);
|
|
CREATE INDEX idx_realm_moderation_actions_type ON realm.moderation_actions (realm_id, action_type);
|
|
CREATE INDEX idx_realm_moderation_actions_created ON realm.moderation_actions (realm_id, created_at DESC);
|
|
|
|
COMMIT;
|