Structure (Migration)

These SQL migration files define the core database structure for Vircadia World. They are executed in numeric order to set up the PostgreSQL database schema.

Environment Variables

• VRCA_CLI_SERVICE_POSTGRES_MIGRATION_DIR: Overrides the default migration files (as seen below) with a custom set of files

1. Base

1_BASE.sql

-- ============================================================================
-- 1. CORE SECURITY AND ROLE MANAGEMENT
-- ============================================================================

-- Create Agent Proxy Role with hard-coded password
DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'vircadia_agent_proxy') THEN
        EXECUTE 'CREATE ROLE vircadia_agent_proxy LOGIN PASSWORD ''CHANGE_ME!'' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION';
    END IF;
END
$$;

-- Then revoke everything
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- Grant usage on schema
GRANT USAGE ON SCHEMA public TO vircadia_agent_proxy;

-- Only grant specific permissions needed
GRANT EXECUTE ON FUNCTION uuid_generate_v4() TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION digest(bytea, text) TO vircadia_agent_proxy;

2. Auth

2_AUTH.sql

-- ============================================================================
-- 1. SCHEMA CREATION
-- ============================================================================
CREATE SCHEMA IF NOT EXISTS auth;

-- ============================================================================
-- 2. CORE AUTHENTICATION FUNCTIONS
-- ============================================================================
-- Super Admin Check Function
CREATE OR REPLACE FUNCTION auth.is_system_agent()
RETURNS boolean AS $$ 
BEGIN
    RETURN session_user = 'vircadia';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Proxy Agent Check Function
CREATE OR REPLACE FUNCTION auth.is_proxy_agent()
RETURNS boolean AS $$
BEGIN
    RETURN session_user = 'vircadia_agent_proxy';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- System Agent ID Function
CREATE OR REPLACE FUNCTION auth.get_system_agent_id() 
RETURNS UUID AS $$
BEGIN
    RETURN '00000000-0000-0000-0000-000000000000'::UUID;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Current Agent ID Function
CREATE OR REPLACE FUNCTION auth.current_agent_id() 
RETURNS UUID AS $$
BEGIN
    -- First check if user is super admin
    IF auth.is_system_agent() THEN
        RETURN auth.get_system_agent_id();
    END IF;

    -- Check if setting exists and is not empty/null
    IF current_setting('app.current_agent_id', true) IS NULL OR 
       TRIM(current_setting('app.current_agent_id', true)) = '' OR
       TRIM(current_setting('app.current_agent_id', true)) = 'NULL' THEN
        RAISE EXCEPTION 'No agent ID set in context';
    END IF;

    -- Validate UUID length
    IF LENGTH(TRIM(current_setting('app.current_agent_id', true))) != 36 THEN
        RAISE EXCEPTION 'Invalid UUID format: incorrect length';
    END IF;

    -- Try to cast to UUID, raise exception if invalid
    BEGIN
        RETURN TRIM(current_setting('app.current_agent_id', true))::UUID;
    EXCEPTION WHEN OTHERS THEN
        RAISE EXCEPTION 'Invalid UUID format: %', current_setting('app.current_agent_id', true);
    END;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================================
-- 3. BASE TEMPLATES
-- ============================================================================
-- Audit Template Table
CREATE TABLE auth._template (
    general__created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    general__created_by UUID DEFAULT auth.current_agent_id(),
    general__updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    general__updated_by UUID DEFAULT auth.current_agent_id()
);

-- ============================================================================
-- 4. BASE TABLES
-- ============================================================================
-- Agent Profiles Table
CREATE TABLE auth.agent_profiles (
    general__agent_profile_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    profile__username TEXT UNIQUE,
    auth__email TEXT UNIQUE,
    auth__is_admin BOOLEAN NOT NULL DEFAULT FALSE,
    auth__is_anon BOOLEAN NOT NULL DEFAULT FALSE,
    profile__last_seen_at TIMESTAMPTZ
) INHERITS (auth._template);
ALTER TABLE auth.agent_profiles ENABLE ROW LEVEL SECURITY;

-- Sessions Table
CREATE TABLE auth.agent_sessions (
    general__session_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    auth__agent_id UUID NOT NULL REFERENCES auth.agent_profiles(general__agent_profile_id) ON DELETE CASCADE,
    auth__provider_name TEXT NOT NULL,
    session__started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    session__last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    session__expires_at TIMESTAMPTZ NOT NULL,
    session__jwt TEXT,
    session__is_active BOOLEAN NOT NULL DEFAULT TRUE
) INHERITS (auth._template);
ALTER TABLE auth.agent_sessions ENABLE ROW LEVEL SECURITY;

-- Auth Provider Configurations Table
CREATE TABLE auth.auth_providers (
    provider__name TEXT PRIMARY KEY,                -- Provider identifier (e.g., 'google', 'github')
    provider__display_name TEXT NOT NULL,           -- Human-readable name
    provider__enabled BOOLEAN NOT NULL DEFAULT false,
    provider__client_id TEXT,                       -- OAuth client ID
    provider__client_secret TEXT,                   -- OAuth client secret
    provider__auth_url TEXT,                        -- OAuth authorization endpoint
    provider__token_url TEXT,                       -- OAuth token endpoint
    provider__userinfo_url TEXT,                    -- OAuth userinfo endpoint
    provider__scope TEXT[],                         -- Required OAuth scopes
    provider__metadata JSONB,                       -- Additional provider-specific configuration
    provider__icon_url TEXT,                        -- URL to provider's icon
    provider__jwt_secret TEXT NOT NULL,             -- JWT signing secret for this provider
    provider__session_max_per_agent INTEGER NOT NULL DEFAULT 1,
    provider__session_duration_jwt_string TEXT NOT NULL DEFAULT '24h',
    provider__session_duration_ms BIGINT NOT NULL DEFAULT 86400000,
    provider__session_max_age_ms BIGINT NOT NULL DEFAULT 86400000,
    provider__session_inactive_expiry_ms BIGINT NOT NULL DEFAULT 3600000
) INHERITS (auth._template);
ALTER TABLE auth.auth_providers ENABLE ROW LEVEL SECURITY;

-- Auth Providers Association Table
CREATE TABLE auth.agent_auth_providers (
    -- Core fields
    auth__agent_id UUID NOT NULL REFERENCES auth.agent_profiles(general__agent_profile_id) ON DELETE CASCADE,
    auth__provider_name TEXT NOT NULL REFERENCES auth.auth_providers(provider__name) ON DELETE RESTRICT,
    
    -- Provider-specific identifiers
    auth__provider_uid TEXT NOT NULL,              -- Provider's unique ID for the user
    auth__provider_email TEXT,                     -- Email from the provider
    
    -- OAuth tokens
    auth__access_token TEXT,                       -- Current access token
    auth__refresh_token TEXT,                      -- Refresh token (if available)
    auth__token_expires_at TIMESTAMPTZ,            -- When the access token expires
    
    -- Account status
    auth__is_verified BOOLEAN NOT NULL DEFAULT FALSE, -- Has email been verified
    auth__last_login_at TIMESTAMPTZ,               -- Track last successful login
    
    -- Additional data
    auth__metadata JSONB,                          -- Provider-specific data/claims

    -- Constraints
    PRIMARY KEY (auth__agent_id, auth__provider_name),
    UNIQUE (auth__provider_name, auth__provider_uid)
) INHERITS (auth._template);
ALTER TABLE auth.agent_auth_providers ENABLE ROW LEVEL SECURITY;
ALTER TABLE auth.agent_sessions ADD CONSTRAINT agent_sessions_auth__provider_name_fkey
    FOREIGN KEY (auth__provider_name) REFERENCES auth.auth_providers(provider__name) ON DELETE CASCADE;

-- Sync Groups Table
CREATE TABLE auth.sync_groups (
    general__sync_group TEXT PRIMARY KEY,
    general__description TEXT,
    
    server__tick__rate_ms INTEGER NOT NULL,
    server__tick__max_tick_count_buffer INTEGER NOT NULL,
    
    client__render_delay_ms INTEGER NOT NULL,
    client__max_prediction_time_ms INTEGER NOT NULL,
    client__poll__rate_ms INTEGER NOT NULL,
    
    network__packet_timing_variance_ms INTEGER NOT NULL
) INHERITS (auth._template);
ALTER TABLE auth.sync_groups ENABLE ROW LEVEL SECURITY;

-- Sync Group Roles Table
CREATE TABLE auth.agent_sync_group_roles (
    auth__agent_id UUID NOT NULL REFERENCES auth.agent_profiles(general__agent_profile_id) ON DELETE CASCADE,
    group__sync TEXT NOT NULL REFERENCES auth.sync_groups(general__sync_group) ON DELETE CASCADE,
    permissions__can_read BOOLEAN NOT NULL DEFAULT true,
    permissions__can_insert BOOLEAN NOT NULL DEFAULT false,
    permissions__can_update BOOLEAN NOT NULL DEFAULT false,
    permissions__can_delete BOOLEAN NOT NULL DEFAULT false,
    PRIMARY KEY (auth__agent_id, group__sync)
) INHERITS (auth._template);
ALTER TABLE auth.agent_sync_group_roles ENABLE ROW LEVEL SECURITY;

-- ============================================================================
-- 5. UTILITY AND TRIGGER FUNCTIONS
-- ============================================================================
-- Audit Column Update Function
CREATE OR REPLACE FUNCTION auth.update_audit_columns()
RETURNS TRIGGER AS $$
BEGIN
    NEW.general__updated_at = CURRENT_TIMESTAMP;
    NEW.general__updated_by = auth.current_agent_id();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Session Cleanup Functions
CREATE OR REPLACE FUNCTION auth.cleanup_old_sessions()
RETURNS trigger AS $$ 
BEGIN
    -- Delete expired sessions based on provider settings
    DELETE FROM auth.agent_sessions AS s
    USING auth.auth_providers AS p
    WHERE s.auth__provider_name = p.provider__name
    AND (
        -- Manual invalidation checks
        NOT s.session__is_active 
        OR s.session__expires_at < NOW()
        -- Provider-based timeout checks
        OR s.session__started_at < (NOW() - (p.provider__session_max_age_ms || ' milliseconds')::INTERVAL)
        OR s.session__last_seen_at < (NOW() - (p.provider__session_inactive_expiry_ms || ' milliseconds')::INTERVAL)
    );
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION auth.enforce_session_limit()
RETURNS TRIGGER AS $$
DECLARE
  v_current_sessions INTEGER;
  v_provider_record auth.auth_providers%ROWTYPE;
  v_oldest_session RECORD;
BEGIN
  -- Check that the provider exists and is enabled (already partly enforced by FK)
  SELECT * INTO v_provider_record
  FROM auth.auth_providers
  WHERE provider__name = NEW.auth__provider_name;

  -- Count active sessions
  SELECT COUNT(*) INTO v_current_sessions
  FROM auth.agent_sessions
  WHERE auth__agent_id = NEW.auth__agent_id
    AND auth__provider_name = NEW.auth__provider_name
    AND session__is_active = true
    AND session__expires_at > NOW();
  
  IF v_current_sessions > v_provider_record.provider__session_max_per_agent THEN
      -- Deactivate oldest session if limit reached
      SELECT general__session_id
      INTO v_oldest_session
      FROM auth.agent_sessions
      WHERE auth__agent_id = NEW.auth__agent_id
        AND auth__provider_name = NEW.auth__provider_name
        AND session__is_active = true
      ORDER BY session__started_at ASC
      LIMIT 1;
      
      IF FOUND THEN
          UPDATE auth.agent_sessions
          SET session__is_active = false,
              session__expires_at = NOW()
          WHERE general__session_id = v_oldest_session.general__session_id;
      END IF;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- View Refresh Functions
CREATE OR REPLACE FUNCTION auth.refresh_active_sessions_view_trigger()
RETURNS trigger AS $$ 
BEGIN
    REFRESH MATERIALIZED VIEW auth.active_sync_group_sessions;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Trigger to update profile's last seen time based on session activity
CREATE OR REPLACE FUNCTION auth.update_profile_last_seen()
RETURNS TRIGGER AS $$
BEGIN
    -- Update the agent's profile last seen timestamp if the session timestamp is newer
    UPDATE auth.agent_profiles
    SET profile__last_seen_at = NEW.session__last_seen_at
    WHERE general__agent_profile_id = NEW.auth__agent_id
      AND (profile__last_seen_at IS NULL 
           OR profile__last_seen_at < NEW.session__last_seen_at);
      
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================================
-- 6. AUTHENTICATION FUNCTIONS
-- ============================================================================
-- Non-system agent Status Functions
CREATE OR REPLACE FUNCTION auth.is_anon_agent()
RETURNS boolean AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1
        FROM auth.agent_profiles AS ap
        WHERE ap.general__agent_profile_id = auth.current_agent_id()
          AND ap.auth__is_anon = true
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION auth.is_admin_agent()
RETURNS boolean AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1
        FROM auth.agent_profiles AS ap
        WHERE ap.general__agent_profile_id = auth.current_agent_id()
          AND ap.auth__is_admin = true
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================================
-- 7. SESSION MANAGEMENT FUNCTIONS
-- ============================================================================
CREATE OR REPLACE FUNCTION auth.validate_session_id(
    p_session_id UUID,
    p_session_token TEXT DEFAULT NULL
) RETURNS UUID AS $$ 
DECLARE
    v_session RECORD;
BEGIN
    SELECT *
      INTO v_session
    FROM auth.agent_sessions
    WHERE general__session_id = p_session_id;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Session not found for id: %', p_session_id;
    END IF;
    
    IF NOT v_session.session__is_active THEN
        RAISE EXCEPTION 'Session % is inactive', p_session_id;
    END IF;
    
    IF v_session.session__expires_at < NOW() THEN
        RAISE EXCEPTION 'Session % has expired on %', p_session_id, v_session.session__expires_at;
    END IF;
    
    IF p_session_token IS NOT NULL AND v_session.session__jwt != p_session_token THEN
        RAISE EXCEPTION 'Session token mismatch for session id: %', p_session_id;
    END IF;
    
    RETURN v_session.auth__agent_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION auth.set_agent_context_from_agent_id(p_agent_id UUID)
RETURNS void AS $$
BEGIN
    -- Only allow the vircadia_agent_proxy role to set the agent context
    IF NOT auth.is_proxy_agent() THEN
        RAISE EXCEPTION 'Only the proxy agent can set the agent context';
    END IF;

    -- If the agent ID does not exist, raise an exception
    IF NOT EXISTS (
        SELECT 1
        FROM auth.agent_profiles
        WHERE general__agent_profile_id = p_agent_id
    ) THEN
        RAISE EXCEPTION 'Agent ID % does not exist', p_agent_id;
    END IF;

    -- Prevent changing the context if it has already been set
    IF current_setting('app.current_agent_id', true) IS NOT NULL
       AND TRIM(current_setting('app.current_agent_id', true)) <> ''
       AND TRIM(current_setting('app.current_agent_id', true)) <> 'NULL' THEN
        RAISE EXCEPTION 'Agent context already set, a new transaction must be created';
    END IF;

    -- Set the validated agent ID for the session (transaction-local)
    PERFORM set_config('app.current_agent_id', p_agent_id::text, true);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Session Heartbeat Function
CREATE OR REPLACE FUNCTION auth.update_session_heartbeat_from_session_id(
    p_session_id UUID
)
RETURNS void AS $$
DECLARE
    v_agent_id UUID;
BEGIN
    -- Check if session exists and get agent ID
    SELECT auth__agent_id INTO v_agent_id
    FROM auth.agent_sessions
    WHERE general__session_id = p_session_id
      AND session__is_active = true
      AND session__expires_at > NOW();
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Session not found for id: %', p_session_id;
    END IF;
    
    -- Check permissions (user's own session, admin, or system)
    IF v_agent_id != auth.current_agent_id() 
       AND NOT auth.is_admin_agent() 
       AND NOT auth.is_system_agent() THEN
        RAISE EXCEPTION 'Insufficient permissions to update session: %', p_session_id;
    END IF;
    
    -- Update the last seen timestamp
    UPDATE auth.agent_sessions
    SET session__last_seen_at = NOW()
    WHERE general__session_id = p_session_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to invalidate a session
CREATE OR REPLACE FUNCTION auth.invalidate_session_from_session_id(
    p_session_id UUID
)
RETURNS void AS $$
DECLARE
    v_agent_id UUID;
BEGIN
    -- Check if session exists and get agent ID
    SELECT auth__agent_id INTO v_agent_id
    FROM auth.agent_sessions
    WHERE general__session_id = p_session_id
      AND session__is_active = true
      AND session__expires_at > NOW();
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Session not found for id: %', p_session_id;
    END IF;
    
    -- Check permissions (user's own session, admin, or system)
    IF v_agent_id != auth.current_agent_id() 
       AND NOT auth.is_admin_agent() 
       AND NOT auth.is_system_agent() THEN
        RAISE EXCEPTION 'Insufficient permissions to invalidate session: %', p_session_id;
    END IF;
    
    -- Update the session to be inactive
    UPDATE auth.agent_sessions
    SET session__is_active = false,
        session__expires_at = NOW()
    WHERE general__session_id = p_session_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;


-- ============================================================================
-- 8. MATERIALIZED VIEWS AND RELATED FUNCTIONS
-- ============================================================================
-- Active Sessions View
CREATE MATERIALIZED VIEW IF NOT EXISTS auth.active_sync_group_sessions AS
SELECT DISTINCT
    s.general__session_id,
    s.auth__agent_id,
    s.session__started_at,
    s.session__last_seen_at,
    s.session__expires_at,
    s.session__is_active,
    r.group__sync,
    r.permissions__can_read,
    r.permissions__can_insert,
    r.permissions__can_update,
    r.permissions__can_delete,
    ap.auth__is_admin,
    ap.auth__is_anon
FROM auth.agent_sessions s
JOIN auth.agent_profiles ap ON s.auth__agent_id = ap.general__agent_profile_id
LEFT JOIN auth.agent_sync_group_roles r ON s.auth__agent_id = r.auth__agent_id
WHERE 
    s.session__is_active = true 
    AND s.session__expires_at > NOW()
WITH DATA;

-- Create index for better performance
CREATE UNIQUE INDEX active_sync_group_sessions_session_group 
ON auth.active_sync_group_sessions (general__session_id, group__sync);

-- Additional indexes for materialized view
CREATE INDEX idx_active_sync_group_sessions_lookup 
ON auth.active_sync_group_sessions (group__sync);

-- ============================================================================
-- 9. INDEXES
-- ============================================================================
-- Agent Profile Indexes
CREATE INDEX idx_agent_profiles_email ON auth.agent_profiles(auth__email);

-- Agent Session Indexes
CREATE INDEX idx_agent_sessions_auth__agent_id ON auth.agent_sessions(auth__agent_id);
CREATE INDEX idx_agent_sessions_auth__provider_name ON auth.agent_sessions(auth__provider_name);
CREATE INDEX idx_agent_sessions_active_lookup ON auth.agent_sessions 
    (session__is_active, session__expires_at) 
    WHERE session__is_active = true;
CREATE INDEX idx_agent_sessions_validation ON auth.agent_sessions 
    (general__session_id, session__is_active, session__expires_at) 
    WHERE session__is_active = true;
CREATE INDEX idx_agent_sessions_last_seen ON auth.agent_sessions(session__last_seen_at) 
    WHERE session__is_active = true;

-- ============================================================================
-- 10. TRIGGERS
-- ============================================================================
-- Session Management Triggers
CREATE TRIGGER trigger_cleanup
    AFTER INSERT OR UPDATE ON auth.agent_sessions
    FOR EACH STATEMENT
    EXECUTE FUNCTION auth.cleanup_old_sessions();

CREATE TRIGGER trigger_enforce_max_sessions
    AFTER INSERT ON auth.agent_sessions
    FOR EACH ROW
    EXECUTE FUNCTION auth.enforce_session_limit();

CREATE TRIGGER refresh_active_sessions_view_on_session_change
    AFTER INSERT OR UPDATE OR DELETE ON auth.agent_sessions
    FOR EACH STATEMENT
    EXECUTE FUNCTION auth.refresh_active_sessions_view_trigger();

CREATE TRIGGER refresh_active_sessions_view_on_role_change
    AFTER INSERT OR UPDATE OR DELETE ON auth.agent_sync_group_roles
    FOR EACH STATEMENT
    EXECUTE FUNCTION auth.refresh_active_sessions_view_trigger();

-- Audit Trail Triggers
CREATE TRIGGER update_agent_profile_timestamps
    BEFORE UPDATE ON auth.agent_profiles
    FOR EACH ROW
    EXECUTE FUNCTION auth.update_audit_columns();

CREATE TRIGGER update_agent_auth_providers_updated_at
    BEFORE UPDATE ON auth.agent_auth_providers
    FOR EACH ROW
    EXECUTE FUNCTION auth.update_audit_columns();

CREATE TRIGGER update_agent_sessions_updated_at
    BEFORE UPDATE ON auth.agent_sessions
    FOR EACH ROW
    EXECUTE FUNCTION auth.update_audit_columns();

CREATE TRIGGER update_sync_groups_updated_at
    BEFORE UPDATE ON auth.sync_groups
    FOR EACH ROW
    EXECUTE FUNCTION auth.update_audit_columns();

CREATE TRIGGER update_agent_sync_group_roles_updated_at
    BEFORE UPDATE ON auth.agent_sync_group_roles
    FOR EACH ROW
    EXECUTE FUNCTION auth.update_audit_columns();

CREATE TRIGGER update_profile_last_seen_on_session_activity
    AFTER UPDATE OF session__last_seen_at ON auth.agent_sessions
    FOR EACH ROW
    EXECUTE FUNCTION auth.update_profile_last_seen();

CREATE TRIGGER update_profile_last_seen_on_session_creation
    AFTER INSERT ON auth.agent_sessions
    FOR EACH ROW
    EXECUTE FUNCTION auth.update_profile_last_seen();

-- ============================================================================
-- 11. INITIAL DATA
-- ============================================================================
-- System Agent Profile
INSERT INTO auth.agent_profiles 
    (general__agent_profile_id, profile__username, auth__email) 
VALUES 
    (auth.get_system_agent_id(), 'admin', 'system@internal')
ON CONFLICT (general__agent_profile_id) DO NOTHING;

-- Default Sync Groups
INSERT INTO auth.sync_groups (
    general__sync_group,
    general__description,
    server__tick__rate_ms,
    server__tick__max_tick_count_buffer,
    client__render_delay_ms,
    client__max_prediction_time_ms,
    client__poll__rate_ms,
    network__packet_timing_variance_ms
) VALUES
    -- Public zone
    ('public.REALTIME', 'Public realtime entities', 100, 50, 50, 100, 100, 25),
    ('public.NORMAL', 'Public normal-priority entities', 200, 20, 100, 150, 200, 50),
    ('public.BACKGROUND', 'Public background entities', 1000, 10, 200, 300, 1000, 100),
    ('public.STATIC', 'Public static entities', 5000, 5, 500, 1000, 5000, 250);

-- Add system provider to auth_providers table if not exists
INSERT INTO auth.auth_providers (
    provider__name,
    provider__display_name,
    provider__enabled,
    provider__jwt_secret,
    provider__session_max_per_agent,
    provider__session_duration_jwt_string,
    provider__session_duration_ms,
    provider__session_max_age_ms,
    provider__session_inactive_expiry_ms
) VALUES (
    'system',
    'System Authentication',
    true,
    'CHANGE_ME!',
    100,
    '24h',
    86400000,
    86400000,
    3600000
) ON CONFLICT (provider__name) DO NOTHING;

-- Add anonymous provider to auth_providers table if not exists
INSERT INTO auth.auth_providers (
    provider__name,
    provider__display_name,
    provider__enabled,
    provider__jwt_secret,
    provider__session_max_per_agent,
    provider__session_duration_jwt_string,
    provider__session_duration_ms,
    provider__session_max_age_ms,
    provider__session_inactive_expiry_ms
) VALUES (
    'anon',
    'Anonymous Authentication',
    true,
    'CHANGE_ME!',
    1,
    '24h',
    86400000,
    86400000,
    3600000
) ON CONFLICT (provider__name) DO NOTHING;

-- ============================================================================
-- 12. PERMISSIONS
-- ============================================================================
CREATE POLICY agent_view_own_profile ON auth.agent_profiles
    FOR SELECT
    TO PUBLIC
    USING (
        general__agent_profile_id = auth.current_agent_id()  -- Agents can view their own profile
        OR auth.is_admin_agent()                            -- Admins can view all profiles
        OR auth.is_system_agent()                           -- System agent can view all profiles
    );

CREATE POLICY agent_update_own_profile ON auth.agent_profiles
    FOR UPDATE
    TO PUBLIC
    USING (
        general__agent_profile_id = auth.current_agent_id()  -- Agents can update their own profile
        OR auth.is_admin_agent()                            -- Admins can update all profiles
        OR auth.is_system_agent()                           -- System agent can update all profiles
    );

-- Sync Group Policies
CREATE POLICY "Allow viewing sync groups" ON auth.sync_groups
    FOR SELECT
    TO PUBLIC
    USING (true);

CREATE POLICY "Allow admin sync group modifications" ON auth.sync_groups
    FOR ALL
    TO PUBLIC
    USING (
        auth.is_admin_agent() 
        OR auth.is_system_agent() 
    );

-- Sync Group Role Policies
CREATE POLICY "Allow viewing sync group roles" ON auth.agent_sync_group_roles
    FOR SELECT
    TO PUBLIC
    USING (true);

CREATE POLICY "Allow admin sync group role modifications" ON auth.agent_sync_group_roles
    FOR ALL
    TO PUBLIC
    USING (
        auth.is_admin_agent() 
        OR auth.is_system_agent() 
    );

-- Agent Auth Providers Policies
CREATE POLICY "Users can view their own provider connections" ON auth.agent_auth_providers
    FOR SELECT
    TO PUBLIC
    USING (
        auth__agent_id = auth.current_agent_id()
        OR auth.is_admin_agent()
        OR auth.is_system_agent()
    );

CREATE POLICY "Only admins can manage provider connections" ON auth.agent_auth_providers
    FOR ALL
    TO PUBLIC
    USING (
        auth.is_admin_agent()
        OR auth.is_system_agent()
    );

CREATE POLICY "Only admins can manage auth providers" ON auth.auth_providers
    FOR ALL
    TO PUBLIC
    USING (
        auth.is_admin_agent()
        OR auth.is_system_agent()
    );

-- SELECT policy: Regular users can view their own sessions, admins/system can view all
CREATE POLICY "Sessions SELECT permissions" ON auth.agent_sessions
    FOR SELECT
    TO PUBLIC
    USING (
        auth__agent_id = auth.current_agent_id()
        OR auth.is_admin_agent() 
        OR auth.is_system_agent()
    );

-- INSERT policy: Regular users can only create their own sessions, admins/system can create any
CREATE POLICY "Sessions INSERT permissions" ON auth.agent_sessions
    FOR INSERT
    TO PUBLIC
    WITH CHECK (
        auth.is_admin_agent() 
        OR auth.is_system_agent()
    );

-- UPDATE policy: Regular users can only update their own sessions, admins/system can update any
CREATE POLICY "Sessions UPDATE permissions" ON auth.agent_sessions
    FOR UPDATE
    TO PUBLIC
    USING (
        auth.is_admin_agent() 
        OR auth.is_system_agent()
    );

-- DELETE policy: Regular users can only delete their own sessions, admins/system can delete any
CREATE POLICY "Sessions DELETE permissions" ON auth.agent_sessions
    FOR DELETE
    TO PUBLIC
    USING (
        auth__agent_id = auth.current_agent_id()
        OR auth.is_admin_agent() 
        OR auth.is_system_agent()
    );

-- Revoke all permissions first
REVOKE ALL ON ALL TABLES IN SCHEMA auth FROM PUBLIC;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA auth FROM PUBLIC;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA auth FROM PUBLIC;
REVOKE ALL ON SCHEMA auth FROM PUBLIC;

-- Grant usage on schema
GRANT USAGE ON SCHEMA auth TO vircadia_agent_proxy;

-- Grant table permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON auth.agent_profiles TO vircadia_agent_proxy;
GRANT SELECT, INSERT, UPDATE, DELETE ON auth.auth_providers TO vircadia_agent_proxy;
GRANT SELECT, INSERT, UPDATE, DELETE ON auth.agent_auth_providers TO vircadia_agent_proxy;
GRANT SELECT, INSERT, UPDATE, DELETE ON auth.sync_groups TO vircadia_agent_proxy;
GRANT SELECT, INSERT, UPDATE, DELETE ON auth.agent_sync_group_roles TO vircadia_agent_proxy;
GRANT SELECT, INSERT, UPDATE, DELETE ON auth.agent_sessions TO vircadia_agent_proxy;

-- Grant view permissions
GRANT SELECT ON auth.active_sync_group_sessions TO vircadia_agent_proxy;

-- Grant function permissions with explicit parameter types
GRANT EXECUTE ON FUNCTION auth.is_anon_agent() TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION auth.is_admin_agent() TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION auth.is_system_agent() TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION auth.is_proxy_agent() TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION auth.current_agent_id() TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION auth.get_system_agent_id() TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION auth.validate_session_id(UUID, TEXT) TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION auth.set_agent_context_from_agent_id(UUID) TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION auth.refresh_active_sessions_view_trigger() TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION auth.update_audit_columns() TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION auth.cleanup_old_sessions() TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION auth.enforce_session_limit() TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION auth.update_session_heartbeat_from_session_id(UUID) TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION auth.update_profile_last_seen() TO vircadia_agent_proxy;
GRANT EXECUTE ON FUNCTION auth.invalidate_session_from_session_id(UUID) TO vircadia_agent_proxy;

3. Configuration

3_CONFIG.sql

-- ============================================================================
-- 1. SCHEMA CREATION AND INITIAL PERMISSIONS
-- ============================================================================
CREATE SCHEMA IF NOT EXISTS config;

-- ============================================================================
-- 2. TYPES
-- ============================================================================
CREATE TYPE config.operation_enum AS ENUM ('INSERT', 'UPDATE', 'DELETE');


-- ============================================================================
-- 3. CONFIGURATION TABLES
-- ============================================================================
-- Entity Configuration
CREATE TABLE config.entity_config (
    entity_config__script_compilation_timeout_ms INTEGER NOT NULL
);

-- Network Configuration
CREATE TABLE config.network_config (
    network_config__max_latency_ms INTEGER NOT NULL,
    network_config__warning_latency_ms INTEGER NOT NULL,
    network_config__consecutive_warnings_before_kick INTEGER NOT NULL,
    network_config__measurement_window_ticks INTEGER NOT NULL,
    network_config__packet_loss_threshold_percent INTEGER NOT NULL
);

-- Authentication Configuration
CREATE TABLE config.auth_config (
    auth_config__session_cleanup_interval BIGINT NOT NULL,
    auth_config__heartbeat_interval_ms INTEGER NOT NULL
);

-- Database Version Configuration
CREATE TABLE config.database_config (
    database_config__major_version INTEGER NOT NULL,
    database_config__minor_version INTEGER NOT NULL,
    database_config__patch_version INTEGER NOT NULL,
    database_config__setup_timestamp TIMESTAMP NOT NULL
);


-- ============================================================================
-- 4. SEED TRACKING
-- ============================================================================
CREATE TABLE config.seeds (
    general__hash TEXT PRIMARY KEY,  -- MD5 hash of the seed content
    general__name TEXT NOT NULL,     -- Seed filename for reference
    general__executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- ============================================================================
-- 5. INITIAL DATA
-- ============================================================================
-- Network Configuration
INSERT INTO config.network_config (
    network_config__max_latency_ms,
    network_config__warning_latency_ms,
    network_config__consecutive_warnings_before_kick,
    network_config__measurement_window_ticks,
    network_config__packet_loss_threshold_percent
) VALUES (500, 200, 50, 100, 5);

-- Authentication Configuration
INSERT INTO config.auth_config (
    auth_config__session_cleanup_interval,
    auth_config__heartbeat_interval_ms
) VALUES (
    3600000,
    3000
);

-- Database Version Configuration
INSERT INTO config.database_config (
    database_config__major_version,
    database_config__minor_version,
    database_config__patch_version,
    database_config__setup_timestamp
) VALUES (1, 0, 0, CURRENT_TIMESTAMP);


-- ============================================================================
-- 6. CONFIG SCHEMA PERMISSIONS
-- ============================================================================
-- Revoke All Permissions
REVOKE ALL ON ALL TABLES IN SCHEMA config FROM PUBLIC;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA config FROM PUBLIC;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA config FROM PUBLIC;
REVOKE ALL ON SCHEMA config FROM PUBLIC;

-- Grant Usage on Schema
GRANT USAGE ON SCHEMA config TO vircadia_agent_proxy;

-- Grant Specific Permissions
GRANT SELECT ON config.entity_config TO vircadia_agent_proxy;
GRANT SELECT ON config.network_config TO vircadia_agent_proxy;
GRANT SELECT ON config.auth_config TO vircadia_agent_proxy;
GRANT SELECT ON config.database_config TO vircadia_agent_proxy;

4. Entity

4_ENTITY.sql

-- ============================================================================
-- 1. SCHEMA CREATION AND INITIAL PERMISSIONS
-- ============================================================================
CREATE SCHEMA IF NOT EXISTS entity;

REVOKE ALL ON SCHEMA entity FROM vircadia_agent_proxy;
GRANT USAGE ON SCHEMA entity TO vircadia_agent_proxy;

-- ============================================================================
-- 3. BASE TEMPLATES
-- ============================================================================
-- Audit Template Table
CREATE TABLE entity._template (
    general__created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    general__created_by UUID NOT NULL DEFAULT auth.current_agent_id(),
    general__updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    general__updated_by UUID NOT NULL DEFAULT auth.current_agent_id()
);


-- ============================================================================
-- 4. CORE TABLES
-- ============================================================================

-- 4.2 ENTITY ASSETS TABLE
-- ============================================================================
CREATE TABLE entity.entity_assets (
    general__asset_file_name TEXT PRIMARY KEY,
    group__sync TEXT NOT NULL REFERENCES auth.sync_groups(general__sync_group) DEFAULT 'public.NORMAL',
    CONSTRAINT fk_entity_assets_sync_group FOREIGN KEY (group__sync) REFERENCES auth.sync_groups(general__sync_group),

    asset__data__bytea BYTEA,  -- Store asset binaries (GLBs, textures, etc.) as bytea
    asset__mime_type TEXT DEFAULT NULL,

    asset__data__bytea_updated_at timestamptz DEFAULT now()
) INHERITS (entity._template);

ALTER TABLE entity.entity_assets ENABLE ROW LEVEL SECURITY;


-- 4.3 ENTITIES TABLE
-- ============================================================================
CREATE TABLE entity.entities (
    general__entity_name TEXT PRIMARY KEY,
    general__semantic_version TEXT NOT NULL DEFAULT '1.0.0',
    general__initialized_at TIMESTAMPTZ DEFAULT NULL,
    general__initialized_by UUID DEFAULT NULL,
    meta__data JSONB NOT NULL DEFAULT '{}'::jsonb,
    group__sync TEXT NOT NULL REFERENCES auth.sync_groups(general__sync_group) DEFAULT 'public.NORMAL',
    group__load_priority INTEGER NOT NULL DEFAULT 1,

    CONSTRAINT fk_entities_sync_group FOREIGN KEY (group__sync) REFERENCES auth.sync_groups(general__sync_group),

    meta_data_updated_at timestamptz NOT NULL DEFAULT now()
) INHERITS (entity._template);

CREATE INDEX idx_entities_load_priority ON entity.entities(group__load_priority) WHERE group__load_priority IS NOT NULL;
CREATE INDEX idx_entities_created_at ON entity.entities(general__created_at);
CREATE INDEX idx_entities_updated_at ON entity.entities(general__updated_at);
CREATE INDEX idx_entities_semantic_version ON entity.entities(general__semantic_version);

ALTER TABLE entity.entities ENABLE ROW LEVEL SECURITY;


-- ============================================================================
-- 5. FUNCTIONS
-- ============================================================================

-- 5.1 CORE UTILITY FUNCTIONS
-- ============================================================================

-- Audit Column Update Function
CREATE OR REPLACE FUNCTION entity.update_audit_columns()
RETURNS TRIGGER AS $$
BEGIN
    NEW.general__updated_at = CURRENT_TIMESTAMP;
    NEW.general__updated_by = auth.current_agent_id();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


-- ============================================================================
-- 6. TRIGGERS
-- ============================================================================

-- 6.2 ENTITY ASSET TRIGGERS
-- ============================================================================

-- Update audit columns trigger for entity_assets
CREATE TRIGGER update_audit_columns
    BEFORE UPDATE ON entity.entity_assets
    FOR EACH ROW
    EXECUTE FUNCTION entity.update_audit_columns();


-- 6.3 ENTITY TRIGGERS
-- ============================================================================

-- Trigger for updating audit columns
CREATE TRIGGER update_audit_columns
    BEFORE UPDATE ON entity.entities
    FOR EACH ROW
    EXECUTE FUNCTION entity.update_audit_columns();


-- ============================================================================
-- 7. POLICIES AND PERMISSIONS
-- ============================================================================

-- 7.1 INITIAL REVOCATIONS
-- ============================================================================
-- Revoke all permissions from PUBLIC and vircadia_agent_proxy (to start with a clean slate)
REVOKE ALL ON ALL TABLES IN SCHEMA entity FROM PUBLIC, vircadia_agent_proxy;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA entity FROM PUBLIC, vircadia_agent_proxy;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA entity FROM PUBLIC, vircadia_agent_proxy;
REVOKE ALL ON ALL PROCEDURES IN SCHEMA entity FROM PUBLIC, vircadia_agent_proxy;
REVOKE ALL ON ALL ROUTINES IN SCHEMA entity FROM PUBLIC, vircadia_agent_proxy;


-- 7.3 ENTITY ASSET POLICIES
-- ============================================================================
-- Grant table permissions to vircadia_agent_proxy for entity_assets
GRANT SELECT, INSERT, UPDATE, DELETE ON entity.entity_assets TO vircadia_agent_proxy;

-- Policy: allow insert only if the agent is a member of the asset's sync group with insert permission
CREATE POLICY "Group can insert entity assets" ON entity.entity_assets
    FOR INSERT
    TO PUBLIC
    WITH CHECK (
        auth.is_admin_agent()
        OR auth.is_system_agent()
        OR EXISTS (
            SELECT 1
            FROM auth.agent_sync_group_roles AS ar
            WHERE ar.auth__agent_id = auth.current_agent_id()
              AND ar.group__sync = entity.entity_assets.group__sync
              AND ar.permissions__can_insert = true
        )
    );

-- Policy: allow view only if the agent is a member of the asset's sync group with view permission
CREATE POLICY "Group can view entity assets" ON entity.entity_assets
    FOR SELECT
    TO PUBLIC
    USING (
        auth.is_admin_agent()
        OR auth.is_system_agent()
        OR EXISTS (
            SELECT 1
            FROM auth.agent_sync_group_roles AS ar
            WHERE ar.auth__agent_id = auth.current_agent_id()
              AND ar.group__sync = entity.entity_assets.group__sync
              AND ar.permissions__can_read = true
        )
    );

-- Policy: allow update only if the agent is a member of the asset's sync group with update permission
CREATE POLICY "Group can update entity assets" ON entity.entity_assets
    FOR UPDATE
    TO PUBLIC
    USING (
        auth.is_admin_agent()
        OR auth.is_system_agent()
        OR EXISTS (
            SELECT 1
            FROM auth.agent_sync_group_roles AS ar
            WHERE ar.auth__agent_id = auth.current_agent_id()
              AND ar.group__sync = entity.entity_assets.group__sync
              AND ar.permissions__can_update = true
        )
    );

-- Policy: allow delete only if the agent is a member of the asset's sync group with delete permission
CREATE POLICY "Group can delete entity assets" ON entity.entity_assets
    FOR DELETE
    TO PUBLIC
    USING (
        auth.is_admin_agent()
        OR auth.is_system_agent()
        OR EXISTS (
            SELECT 1
            FROM auth.agent_sync_group_roles AS ar
            WHERE ar.auth__agent_id = auth.current_agent_id()
              AND ar.group__sync = entity.entity_assets.group__sync
              AND ar.permissions__can_delete = true
        )
    );


-- 7.4 ENTITY POLICIES
-- ============================================================================
-- Grant table permissions to vircadia_agent_proxy for entities
GRANT SELECT, INSERT, UPDATE, DELETE ON entity.entities TO vircadia_agent_proxy;

CREATE POLICY "entities_read_policy" ON entity.entities
    FOR SELECT
    TO PUBLIC
    USING (
        auth.is_admin_agent()
        OR auth.is_system_agent()
        OR EXISTS (
            SELECT 1
            FROM auth.active_sync_group_sessions sess
            WHERE sess.auth__agent_id = auth.current_agent_id()
              AND sess.group__sync = entity.entities.group__sync
              AND sess.permissions__can_read = true
        )
    );

CREATE POLICY "entities_update_policy" ON entity.entities
    FOR UPDATE
    TO PUBLIC
    USING (
        auth.is_admin_agent()
        OR auth.is_system_agent()
        OR EXISTS (
            SELECT 1
            FROM auth.active_sync_group_sessions sess
            WHERE sess.auth__agent_id = auth.current_agent_id()
              AND sess.group__sync = entity.entities.group__sync
              AND sess.permissions__can_update = true
        )
    );

CREATE POLICY "entities_insert_policy" ON entity.entities
    FOR INSERT
    TO PUBLIC
    WITH CHECK (
        auth.is_admin_agent()
        OR auth.is_system_agent()
        OR EXISTS (
            SELECT 1
            FROM auth.active_sync_group_sessions sess
            WHERE sess.auth__agent_id = auth.current_agent_id()
              AND sess.group__sync = entity.entities.group__sync
              AND sess.permissions__can_insert = true
        )
    );

CREATE POLICY "entities_delete_policy" ON entity.entities
    FOR DELETE
    TO PUBLIC
    USING (
        auth.is_admin_agent()
        OR auth.is_system_agent()
        OR EXISTS (
            SELECT 1
            FROM auth.active_sync_group_sessions sess
            WHERE sess.auth__agent_id = auth.current_agent_id()
              AND sess.group__sync = entity.entities.group__sync
              AND sess.permissions__can_delete = true
        )
    );

-- ============================================================================
-- TRIGGERS TO UPDATE TIMESTAMPS WHEN SPECIFIC COLUMNS CHANGE
-- ============================================================================

-- 1. Trigger for entity.entities
CREATE OR REPLACE FUNCTION entity.update_entity_timestamps()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        IF NEW.meta__data IS DISTINCT FROM OLD.meta__data THEN
            NEW.meta_data_updated_at = now();
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_entity_timestamps
BEFORE UPDATE ON entity.entities
FOR EACH ROW EXECUTE FUNCTION entity.update_entity_timestamps();

-- 3. Trigger for entity.entity_assets
CREATE OR REPLACE FUNCTION entity.update_asset_timestamps()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        IF NEW.asset__data__bytea IS DISTINCT FROM OLD.asset__data__bytea THEN
            NEW.asset__data__bytea_updated_at = now();
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_asset_timestamps
BEFORE UPDATE ON entity.entity_assets
FOR EACH ROW EXECUTE FUNCTION entity.update_asset_timestamps();

-- ============================================================================
-- INDEXES FOR TIMESTAMP-BASED QUERIES
-- ============================================================================

-- 1. Index for entity changes
CREATE INDEX idx_entity_timestamp_changes ON entity.entities
    (group__sync,
     GREATEST(
        meta_data_updated_at,
        general__updated_at
     ))
    INCLUDE (general__entity_name);

-- 3. Composite index for asset changes
CREATE INDEX idx_asset_timestamp_changes ON entity.entity_assets
    (group__sync,
     GREATEST(
        general__updated_at
     ))
    INCLUDE (general__asset_file_name);

5. Tick

5_TICK.sql

-- ============================================================================
-- 1. SCHEMA CREATION AND INITIAL PERMISSIONS
-- ============================================================================
CREATE SCHEMA IF NOT EXISTS tick;

-- Initial revocations (we'll grant specific permissions at the end)
REVOKE ALL ON SCHEMA tick FROM PUBLIC, vircadia_agent_proxy;
GRANT USAGE ON SCHEMA tick TO vircadia_agent_proxy;

-- ============================================================================
-- 2. BASE TABLES
-- ============================================================================

-- 2.1 WORLD TICKS TABLE
CREATE TABLE tick.world_ticks (
    general__tick_id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    tick__number bigint NOT NULL,
    group__sync TEXT NOT NULL REFERENCES auth.sync_groups(general__sync_group),
    tick__start_time timestamptz NOT NULL,
    tick__end_time timestamptz NOT NULL,
    tick__duration_ms double precision NOT NULL,
    tick__entity_states_processed int NOT NULL,
    tick__is_delayed boolean NOT NULL,
    tick__headroom_ms double precision,
    tick__time_since_last_tick_ms double precision,

    -- DB-specific metrics
    tick__db__start_time timestamptz,
    tick__db__end_time timestamptz,
    tick__db__duration_ms double precision,
    tick__db__is_delayed boolean,

    -- Manager-specific metrics
    tick__manager__start_time timestamptz,
    tick__manager__end_time timestamptz,
    tick__manager__duration_ms double precision,
    tick__manager__is_delayed boolean,

    -- Add unique constraint for sync_group + tick number combination
    UNIQUE (group__sync, tick__number)
);

-- 2.2 ENTITY STATES TABLE
CREATE TABLE tick.entity_states (
    LIKE entity.entities INCLUDING DEFAULTS EXCLUDING CONSTRAINTS,

    -- Additional metadata for state tracking
    general__tick_id uuid NOT NULL,
    general__entity_state_id uuid DEFAULT uuid_generate_v4(),

    -- Override the primary key to allow multiple states per entity
    CONSTRAINT entity_states_pkey PRIMARY KEY (general__entity_state_id),

    -- Add foreign key constraint for sync_group
    CONSTRAINT entity_states_sync_group_fkey FOREIGN KEY (group__sync)
        REFERENCES auth.sync_groups(general__sync_group),

    -- Add foreign key constraint to world_ticks with cascade delete
    CONSTRAINT entity_states_tick_fkey FOREIGN KEY (general__tick_id)
        REFERENCES tick.world_ticks(general__tick_id) ON DELETE CASCADE
);

-- ============================================================================
-- 3. INDEXES
-- ============================================================================

-- 3.1 WORLD TICKS INDEXES
CREATE INDEX idx_world_ticks_sync_number ON tick.world_ticks (group__sync, tick__number DESC);
CREATE INDEX idx_world_ticks_sync_time ON tick.world_ticks (group__sync, tick__start_time DESC);

-- 3.2 ENTITY STATES INDEXES
CREATE INDEX entity_states_lookup_idx ON tick.entity_states (general__entity_name, general__tick_id);
CREATE INDEX entity_states_tick_idx ON tick.entity_states (general__tick_id);
CREATE INDEX entity_states_sync_group_tick_idx ON tick.entity_states (group__sync, general__tick_id DESC);
CREATE INDEX idx_entity_states_sync_tick_lookup ON tick.entity_states (group__sync, general__tick_id, general__entity_name);
CREATE INDEX idx_entity_states_sync_tick ON tick.entity_states (group__sync, general__tick_id);

-- Fast lookups of entity states by tick and entity ID
CREATE INDEX idx_entity_states_tick_entity_name ON tick.entity_states (general__tick_id, general__entity_name);

-- Optimized index for finding latest ticks by sync group with covering columns
CREATE INDEX idx_world_ticks_sync_number_covering ON tick.world_ticks
    (group__sync, tick__number DESC)
    INCLUDE (general__tick_id, tick__start_time);

-- Fast timestamp comparisons for entity changes
CREATE INDEX idx_entity_states_updated_at ON tick.entity_states
    (group__sync, general__updated_at DESC)
    INCLUDE (general__entity_name);

-- Space-efficient BRIN index for time-series data
CREATE INDEX idx_world_ticks_time_brin ON tick.world_ticks USING BRIN (tick__start_time);

-- Composite index for tick + sync group lookup patterns
CREATE INDEX idx_entity_states_sync_tick_composite ON tick.entity_states
    (group__sync, general__tick_id)
    INCLUDE (general__entity_name, meta__data);

-- ============================================================================
-- 4. FUNCTIONS
-- ============================================================================

-- 4.1 TICK CAPTURE FUNCTION - Updated to include timestamp tracking columns
CREATE OR REPLACE FUNCTION tick.capture_tick_state(
    p_sync_group text
) RETURNS TABLE (
    general__tick_id uuid,
    tick__number bigint,
    group__sync text,
    tick__start_time timestamptz,
    tick__end_time timestamptz,
    tick__duration_ms double precision,
    tick__entity_states_processed int,
    tick__is_delayed boolean,
    tick__headroom_ms double precision,
    tick__time_since_last_tick_ms double precision,
    tick__db__start_time timestamptz,
    tick__db__end_time timestamptz,
    tick__db__duration_ms double precision,
    tick__db__is_delayed boolean
) AS $$
DECLARE
    v_start_time timestamptz;
    v_last_tick_time timestamptz;
    v_tick_number bigint;
    v_entity_states_processed int;
    v_end_time timestamptz;
    v_duration_ms double precision;
    v_headroom_ms double precision;
    v_is_delayed boolean;
    v_time_since_last_tick_ms double precision;
    v_tick_id uuid;
    v_max_tick_count_buffer integer;
    v_db_start_time timestamptz;
    v_db_end_time timestamptz;
    v_db_duration_ms double precision;
    v_db_is_delayed boolean;
BEGIN
    -- Initialize timing variables (no global lock)
    v_start_time := clock_timestamp();
    v_db_start_time := v_start_time;  -- Database processing starts now

    -- Get max tick count buffer from sync group config
    SELECT server__tick__max_tick_count_buffer
    INTO v_max_tick_count_buffer
    FROM auth.sync_groups
    WHERE general__sync_group = p_sync_group;

    -- Shorter transaction for tick number acquisition
    BEGIN
        -- Get last tick information - lock only what we need
        SELECT
            wt.tick__start_time,
            wt.tick__number
        INTO
            v_last_tick_time,
            v_tick_number
        FROM tick.world_ticks wt
        WHERE wt.group__sync = p_sync_group
        ORDER BY wt.tick__number DESC
        LIMIT 1
        FOR UPDATE;

        IF v_tick_number IS NULL THEN
            v_tick_number := 1;
        ELSE
            v_tick_number := v_tick_number + 1;
        END IF;
    END;

    -- Calculate time since last tick
    IF v_last_tick_time IS NOT NULL THEN
        v_time_since_last_tick_ms := EXTRACT(EPOCH FROM (v_start_time - v_last_tick_time)) * 1000;
    END IF;

    -- Clean up in a separate transaction using a more targeted approach
    BEGIN
        DELETE FROM tick.world_ticks wt
        WHERE wt.group__sync = p_sync_group
        AND wt.general__tick_id IN (
            SELECT wt2.general__tick_id
            FROM tick.world_ticks wt2
            WHERE wt2.group__sync = p_sync_group
            AND (
                SELECT COUNT(*)
                FROM tick.world_ticks wt3
                WHERE wt3.group__sync = wt2.group__sync
                  AND wt3.tick__number > wt2.tick__number
            ) >= v_max_tick_count_buffer
        );
    END;

    -- Insert new tick record (initial)
    v_tick_id := uuid_generate_v4();
    INSERT INTO tick.world_ticks (
        general__tick_id,
        tick__number,
        group__sync,
        tick__start_time,
        tick__end_time,
        tick__duration_ms,
        tick__entity_states_processed,
        tick__is_delayed,
        tick__headroom_ms,
        tick__time_since_last_tick_ms,
        tick__db__start_time,
        tick__db__end_time,
        tick__db__duration_ms,
        tick__db__is_delayed
    ) VALUES (
        v_tick_id,
        v_tick_number,
        p_sync_group,
        v_start_time,
        clock_timestamp(),
        0,
        0,
        false,
        0,
        v_time_since_last_tick_ms,
        v_db_start_time,
        null,  -- Will update at the end
        0,
        false
    );

    -- Capture entity states (now including timestamp columns)
    WITH entity_snapshot AS (
        INSERT INTO tick.entity_states (
            general__entity_name,
            general__semantic_version,
            group__load_priority,
            general__initialized_at,
            general__initialized_by,
            meta__data,
            group__sync,
            general__created_at,
            general__created_by,
            general__updated_at,
            general__updated_by,
            general__tick_id,
            -- Include the timestamp columns from the source table if they exist
            meta_data_updated_at
        )
        SELECT
            e.general__entity_name,
            e.general__semantic_version,
            e.group__load_priority,
            e.general__initialized_at,
            e.general__initialized_by,
            e.meta__data,
            e.group__sync,
            e.general__created_at,
            e.general__created_by,
            e.general__updated_at,
            e.general__updated_by,
            v_tick_id,
            -- Copy timestamp columns if they exist in the source table
            e.meta_data_updated_at
        FROM entity.entities e
        WHERE e.group__sync = p_sync_group
        RETURNING 1
    )
    SELECT COUNT(*) INTO v_entity_states_processed FROM entity_snapshot;

    -- Calculate tick duration, delay & headroom
    v_end_time := clock_timestamp();
    v_duration_ms := EXTRACT(EPOCH FROM (v_end_time - v_start_time)) * 1000;

    -- Calculate DB-specific metrics
    v_db_end_time := v_end_time;
    v_db_duration_ms := EXTRACT(EPOCH FROM (v_db_end_time - v_db_start_time)) * 1000;

    SELECT
        v_duration_ms > sg.server__tick__rate_ms AS is_delayed,
        sg.server__tick__rate_ms - v_duration_ms AS headroom_ms,
        v_db_duration_ms > sg.server__tick__rate_ms AS db_is_delayed
    INTO v_is_delayed, v_headroom_ms, v_db_is_delayed
    FROM auth.sync_groups sg
    WHERE sg.general__sync_group = p_sync_group;

    -- Update tick record with final metrics
    UPDATE tick.world_ticks wt
    SET
        tick__end_time = v_end_time,
        tick__duration_ms = v_duration_ms,
        tick__entity_states_processed = v_entity_states_processed,
        tick__is_delayed = v_is_delayed,
        tick__headroom_ms = v_headroom_ms,
        tick__db__end_time = v_db_end_time,
        tick__db__duration_ms = v_db_duration_ms,
        tick__db__is_delayed = v_db_is_delayed
    WHERE wt.general__tick_id = v_tick_id;

    -- Send notification that a tick has been captured
    PERFORM pg_notify(
        'tick_captured',
        json_build_object(
            'syncGroup', p_sync_group,
            'tickId', v_tick_id,
            'tickNumber', v_tick_number
        )::text
    );

    -- Return the captured tick record
    RETURN QUERY
    SELECT
        v_tick_id,
        v_tick_number,
        p_sync_group,
        v_start_time,
        v_end_time,
        v_duration_ms,
        v_entity_states_processed,
        v_is_delayed,
        v_headroom_ms,
        v_time_since_last_tick_ms,
        v_db_start_time,
        v_db_end_time,
        v_db_duration_ms,
        v_db_is_delayed;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================================
-- 5. TRIGGERS
-- ============================================================================

-- 5.1 ENABLE ROW LEVEL SECURITY ON ALL TABLES
ALTER TABLE tick.world_ticks ENABLE ROW LEVEL SECURITY;
ALTER TABLE tick.entity_states ENABLE ROW LEVEL SECURITY;

-- ============================================================================
-- 6. POLICIES
-- ============================================================================

-- 6.1 WORLD TICKS POLICIES
CREATE POLICY "world_ticks_read_policy" ON tick.world_ticks
    FOR SELECT
    USING (
        auth.is_admin_agent()
        OR auth.is_system_agent()
    );

CREATE POLICY "world_ticks_update_policy" ON tick.world_ticks
    FOR UPDATE
    USING (
        auth.is_admin_agent()
        OR auth.is_system_agent()
    );

CREATE POLICY "world_ticks_insert_policy" ON tick.world_ticks
    FOR INSERT
    WITH CHECK (
        auth.is_admin_agent()
        OR auth.is_system_agent()
    );

CREATE POLICY "world_ticks_delete_policy" ON tick.world_ticks
    FOR DELETE
    USING (
        auth.is_admin_agent()
        OR auth.is_system_agent()
    );

-- 6.2 ENTITY STATES POLICIES
CREATE POLICY "entity_states_read_policy" ON tick.entity_states
    FOR SELECT
    USING (
        auth.is_admin_agent()
        OR auth.is_system_agent()
        OR EXISTS (
            SELECT 1
            FROM auth.active_sync_group_sessions sess
            WHERE sess.auth__agent_id = auth.current_agent_id()
              AND sess.group__sync = tick.entity_states.group__sync
        )
    );

CREATE POLICY "entity_states_update_policy" ON tick.entity_states
    FOR UPDATE
    USING (auth.is_admin_agent());

CREATE POLICY "entity_states_insert_policy" ON tick.entity_states
    FOR INSERT
    WITH CHECK (auth.is_admin_agent());

CREATE POLICY "entity_states_delete_policy" ON tick.entity_states
    FOR DELETE
    USING (auth.is_admin_agent());

-- ============================================================================
-- 7. PERMISSIONS
-- ============================================================================

-- Revoke all permissions first
REVOKE ALL ON SCHEMA tick FROM PUBLIC, vircadia_agent_proxy;

-- Grant schema usage
GRANT USAGE ON SCHEMA tick TO vircadia_agent_proxy;

-- Grant table permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tick TO vircadia_agent_proxy;

-- Grant function permissions
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA tick TO vircadia_agent_proxy;