306 lines
12 KiB
PL/PgSQL
306 lines
12 KiB
PL/PgSQL
-- Chattyness Scene Schema Tables
|
|
-- PostgreSQL 18 with PostGIS
|
|
--
|
|
-- Scene-level runtime state: instances, members, spots, loose props, decorations
|
|
-- Load via: psql -f schema/tables/045_scene.sql
|
|
|
|
\set ON_ERROR_STOP on
|
|
|
|
BEGIN;
|
|
|
|
-- =============================================================================
|
|
-- Instances (renamed from realm.channels)
|
|
-- =============================================================================
|
|
-- Instances are ephemeral scene rooms where users can interact.
|
|
-- Each scene can have multiple instances (public default + private rooms).
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE scene.instances (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
scene_id UUID NOT NULL REFERENCES realm.scenes(id) ON DELETE CASCADE,
|
|
|
|
instance_type scene.instance_type NOT NULL DEFAULT 'public',
|
|
name public.display_name,
|
|
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
|
|
max_users INTEGER NOT NULL DEFAULT 50 CHECK (max_users > 0 AND max_users <= 1000),
|
|
current_user_count INTEGER NOT NULL DEFAULT 0 CHECK (current_user_count >= 0),
|
|
|
|
expires_at TIMESTAMPTZ,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
COMMENT ON TABLE scene.instances IS 'Ephemeral scene rooms where users interact';
|
|
|
|
CREATE INDEX idx_scene_instances_scene ON scene.instances (scene_id);
|
|
CREATE INDEX idx_scene_instances_type ON scene.instances (scene_id, instance_type);
|
|
CREATE INDEX idx_scene_instances_expires ON scene.instances (expires_at) WHERE expires_at IS NOT NULL;
|
|
|
|
-- =============================================================================
|
|
-- Add FK from auth.guest_sessions to scene.instances
|
|
-- =============================================================================
|
|
-- guest_sessions.current_instance_id was added without FK in 030_realm.sql
|
|
-- Now we can add the constraint since scene.instances exists
|
|
-- =============================================================================
|
|
|
|
ALTER TABLE auth.guest_sessions
|
|
ADD CONSTRAINT fk_auth_guest_sessions_instance
|
|
FOREIGN KEY (current_instance_id) REFERENCES scene.instances(id) ON DELETE SET NULL;
|
|
|
|
-- =============================================================================
|
|
-- Instance Members (renamed from realm.channel_members)
|
|
-- =============================================================================
|
|
-- Users currently present in an instance with their positions.
|
|
-- Note: instance_id is actually scene_id in this system (scenes are used directly as instances).
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE scene.instance_members (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
instance_id UUID NOT NULL REFERENCES realm.scenes(id) ON DELETE CASCADE,
|
|
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
guest_session_id UUID REFERENCES auth.guest_sessions(id) ON DELETE CASCADE,
|
|
|
|
position public.virtual_point NOT NULL DEFAULT ST_SetSRID(ST_MakePoint(400, 300), 0),
|
|
|
|
facing_direction SMALLINT NOT NULL DEFAULT 0 CHECK (facing_direction >= 0 AND facing_direction < 360),
|
|
is_moving BOOLEAN NOT NULL DEFAULT false,
|
|
is_afk BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
last_moved_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT chk_scene_instance_members_user_or_guest CHECK (
|
|
(user_id IS NOT NULL AND guest_session_id IS NULL) OR
|
|
(user_id IS NULL AND guest_session_id IS NOT NULL)
|
|
),
|
|
CONSTRAINT uq_scene_instance_members_user UNIQUE (instance_id, user_id),
|
|
CONSTRAINT uq_scene_instance_members_guest UNIQUE (instance_id, guest_session_id)
|
|
);
|
|
|
|
COMMENT ON TABLE scene.instance_members IS 'Users in an instance with positions';
|
|
|
|
CREATE INDEX idx_scene_instance_members_instance ON scene.instance_members (instance_id);
|
|
CREATE INDEX idx_scene_instance_members_user ON scene.instance_members (user_id) WHERE user_id IS NOT NULL;
|
|
CREATE INDEX idx_scene_instance_members_guest ON scene.instance_members (guest_session_id) WHERE guest_session_id IS NOT NULL;
|
|
CREATE INDEX idx_scene_instance_members_position ON scene.instance_members USING GIST (position);
|
|
|
|
-- =============================================================================
|
|
-- Instance Invites (renamed from realm.channel_invites)
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE scene.instance_invites (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
instance_id UUID NOT NULL REFERENCES scene.instances(id) ON DELETE CASCADE,
|
|
invited_by UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
invited_user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
accepted_at TIMESTAMPTZ,
|
|
declined_at TIMESTAMPTZ,
|
|
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_scene_instance_invites UNIQUE (instance_id, invited_user_id),
|
|
CONSTRAINT chk_scene_instance_invites_not_self CHECK (invited_by != invited_user_id)
|
|
);
|
|
|
|
COMMENT ON TABLE scene.instance_invites IS 'Private instance invitations';
|
|
|
|
CREATE INDEX idx_scene_instance_invites_instance ON scene.instance_invites (instance_id);
|
|
CREATE INDEX idx_scene_instance_invites_user ON scene.instance_invites (invited_user_id);
|
|
CREATE INDEX idx_scene_instance_invites_pending ON scene.instance_invites (invited_user_id, expires_at)
|
|
WHERE accepted_at IS NULL AND declined_at IS NULL;
|
|
|
|
-- =============================================================================
|
|
-- Spots (moved from realm.spots)
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE scene.spots (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
scene_id UUID NOT NULL REFERENCES realm.scenes(id) ON DELETE CASCADE,
|
|
|
|
name TEXT,
|
|
slug public.slug,
|
|
|
|
region GEOMETRY(GEOMETRY, 0) NOT NULL,
|
|
|
|
spot_type scene.spot_type NOT NULL DEFAULT 'normal',
|
|
|
|
destination_scene_id UUID REFERENCES realm.scenes(id) ON DELETE SET NULL,
|
|
destination_position GEOMETRY(POINT, 0),
|
|
|
|
current_state SMALLINT NOT NULL DEFAULT 0,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
|
|
is_visible BOOLEAN NOT NULL DEFAULT true,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_scene_spots_slug UNIQUE (scene_id, slug)
|
|
);
|
|
|
|
COMMENT ON TABLE scene.spots IS 'Interactive regions in scenes';
|
|
|
|
CREATE INDEX idx_scene_spots_scene ON scene.spots (scene_id);
|
|
CREATE INDEX idx_scene_spots_region ON scene.spots USING GIST (region);
|
|
CREATE INDEX idx_scene_spots_active ON scene.spots (scene_id, is_active) WHERE is_active = true;
|
|
|
|
-- =============================================================================
|
|
-- Spot States (moved from realm.spot_states)
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE scene.spot_states (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
spot_id UUID NOT NULL REFERENCES scene.spots(id) ON DELETE CASCADE,
|
|
|
|
state_number SMALLINT NOT NULL,
|
|
|
|
asset_path public.asset_path,
|
|
offset_x REAL NOT NULL DEFAULT 0,
|
|
offset_y REAL NOT NULL DEFAULT 0,
|
|
|
|
audio_id UUID REFERENCES server.audio(id) ON DELETE SET NULL,
|
|
|
|
CONSTRAINT uq_scene_spot_states UNIQUE (spot_id, state_number)
|
|
);
|
|
|
|
COMMENT ON TABLE scene.spot_states IS 'Visual configurations for spot states';
|
|
|
|
CREATE INDEX idx_scene_spot_states_spot ON scene.spot_states (spot_id);
|
|
|
|
-- =============================================================================
|
|
-- Scene Scripts (moved from realm.scene_scripts)
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE scene.scripts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
scene_id UUID NOT NULL REFERENCES realm.scenes(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_enter BOOLEAN NOT NULL DEFAULT false,
|
|
handle_private_instances 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 scene.scripts IS 'Rhai scripts for scenes';
|
|
|
|
CREATE INDEX idx_scene_scripts_scene ON scene.scripts (scene_id);
|
|
CREATE INDEX idx_scene_scripts_enabled ON scene.scripts (scene_id, is_enabled) WHERE is_enabled = true;
|
|
|
|
-- =============================================================================
|
|
-- Loose Props (moved from props.loose_props)
|
|
-- =============================================================================
|
|
-- Props that exist at a position in an instance, not worn by anyone.
|
|
-- Can be picked up by users.
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE scene.loose_props (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
instance_id UUID NOT NULL REFERENCES scene.instances(id) ON DELETE CASCADE,
|
|
|
|
-- Source of the prop (either server or realm library)
|
|
server_prop_id UUID REFERENCES server.props(id) ON DELETE CASCADE,
|
|
realm_prop_id UUID REFERENCES realm.props(id) ON DELETE CASCADE,
|
|
|
|
-- Position in scene (PostGIS point, SRID 0)
|
|
position public.virtual_point NOT NULL,
|
|
|
|
-- Who dropped it (NULL = spawned by system/script)
|
|
dropped_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
|
|
-- Auto-decay
|
|
expires_at TIMESTAMPTZ, -- NULL = permanent
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
-- Must reference exactly one source
|
|
CONSTRAINT chk_scene_loose_props_source CHECK (
|
|
(server_prop_id IS NOT NULL AND realm_prop_id IS NULL) OR
|
|
(server_prop_id IS NULL AND realm_prop_id IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
COMMENT ON TABLE scene.loose_props IS 'Props dropped in instances that can be picked up';
|
|
COMMENT ON COLUMN scene.loose_props.position IS 'Location in scene as PostGIS point (SRID 0)';
|
|
COMMENT ON COLUMN scene.loose_props.expires_at IS 'When prop auto-decays (NULL = permanent)';
|
|
|
|
CREATE INDEX idx_scene_loose_props_instance ON scene.loose_props (instance_id);
|
|
CREATE INDEX idx_scene_loose_props_expires ON scene.loose_props (expires_at)
|
|
WHERE expires_at IS NOT NULL;
|
|
|
|
-- Spatial index for finding props near a position
|
|
CREATE INDEX idx_scene_loose_props_position ON scene.loose_props
|
|
USING GIST (position);
|
|
|
|
-- =============================================================================
|
|
-- Scene Decorations (moved from props.scene_decorations)
|
|
-- =============================================================================
|
|
-- Props placed in scenes by builders/owners as permanent decoration.
|
|
-- Can optionally be copied by users into their inventory.
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE scene.decorations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
scene_id UUID NOT NULL REFERENCES realm.scenes(id) ON DELETE CASCADE,
|
|
|
|
-- Source of the prop
|
|
server_prop_id UUID REFERENCES server.props(id) ON DELETE CASCADE,
|
|
realm_prop_id UUID REFERENCES realm.props(id) ON DELETE CASCADE,
|
|
|
|
-- Position and display
|
|
position public.virtual_point NOT NULL,
|
|
z_index INTEGER NOT NULL DEFAULT 0,
|
|
scale REAL NOT NULL DEFAULT 1.0 CHECK (scale > 0 AND scale <= 10),
|
|
rotation SMALLINT NOT NULL DEFAULT 0 CHECK (rotation >= 0 AND rotation < 360),
|
|
opacity public.percentage NOT NULL DEFAULT 1.0,
|
|
|
|
-- Interaction
|
|
is_copyable BOOLEAN NOT NULL DEFAULT false, -- Users can copy to inventory
|
|
click_action JSONB, -- Optional click behavior
|
|
|
|
-- Management
|
|
placed_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
-- Must reference exactly one source
|
|
CONSTRAINT chk_scene_decorations_source CHECK (
|
|
(server_prop_id IS NOT NULL AND realm_prop_id IS NULL) OR
|
|
(server_prop_id IS NULL AND realm_prop_id IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
COMMENT ON TABLE scene.decorations IS 'Permanent prop decorations placed in scenes';
|
|
COMMENT ON COLUMN scene.decorations.is_copyable IS 'If true, users can copy this prop to their inventory';
|
|
COMMENT ON COLUMN scene.decorations.click_action IS 'Optional JSON action config for click behavior';
|
|
|
|
CREATE INDEX idx_scene_decorations_scene ON scene.decorations (scene_id);
|
|
|
|
-- Spatial index for rendering props in view
|
|
CREATE INDEX idx_scene_decorations_position ON scene.decorations
|
|
USING GIST (position);
|
|
|
|
COMMIT;
|