Initial copy of business card prop with profile

db/schema/tables/020_auth.sql

@@ -282,6 +282,9 @@ CREATE TABLE server.props (
     -- 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),
 
+    -- Optional JSON Schema for validating state structure
+    state_schema JSONB,
+
     is_unique BOOLEAN NOT NULL DEFAULT false,
     is_transferable BOOLEAN NOT NULL DEFAULT true,
     is_portable BOOLEAN NOT NULL DEFAULT true,
@@ -312,6 +315,7 @@ CREATE TABLE server.props (
 );
 
 COMMENT ON TABLE server.props IS 'Global prop library (64x64 pixels, center-anchored)';
+COMMENT ON COLUMN server.props.state_schema IS 'Optional JSON Schema defining valid state structure for this prop';
 
 CREATE INDEX idx_server_props_tags ON server.props USING GIN (tags);
 CREATE INDEX idx_server_props_active ON server.props (is_active) WHERE is_active = true;
@@ -435,7 +439,18 @@ CREATE TABLE auth.inventory (
     is_portable BOOLEAN NOT NULL DEFAULT true,
     is_droppable BOOLEAN NOT NULL DEFAULT true,
 
+    -- Public state columns (persist through transfer)
+    server_state JSONB NOT NULL DEFAULT '{}',
+    realm_state JSONB NOT NULL DEFAULT '{}',
+    user_state JSONB NOT NULL DEFAULT '{}',
+
+    -- Private state columns (cleared on transfer)
+    server_private_state JSONB NOT NULL DEFAULT '{}',
+    realm_private_state JSONB NOT NULL DEFAULT '{}',
+    user_private_state JSONB NOT NULL DEFAULT '{}',
+
     acquired_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
 
     -- At least one source must be present
     CONSTRAINT chk_auth_inventory_has_source CHECK (
@@ -445,12 +460,23 @@ CREATE TABLE auth.inventory (
 
 COMMENT ON TABLE auth.inventory IS 'User-owned props (denormalized for performance)';
 COMMENT ON COLUMN auth.inventory.provenance IS 'Array of {from_user, timestamp, method} objects';
+COMMENT ON COLUMN auth.inventory.server_state IS 'Public state visible to everyone, persists through transfer';
+COMMENT ON COLUMN auth.inventory.realm_state IS 'Public state visible to realm members, persists through transfer';
+COMMENT ON COLUMN auth.inventory.user_state IS 'Public state visible to item inspectors, persists through transfer';
+COMMENT ON COLUMN auth.inventory.server_private_state IS 'Private owner state, cleared on transfer';
+COMMENT ON COLUMN auth.inventory.realm_private_state IS 'Private owner state, cleared on transfer';
+COMMENT ON COLUMN auth.inventory.user_private_state IS 'Private owner state, cleared on transfer';
+COMMENT ON COLUMN auth.inventory.updated_at IS 'Timestamp of last modification (auto-updated by trigger)';
 
 CREATE INDEX idx_auth_inventory_user ON auth.inventory (user_id);
 CREATE INDEX idx_auth_inventory_server_prop ON auth.inventory (server_prop_id)
     WHERE server_prop_id IS NOT NULL;
 CREATE INDEX idx_auth_inventory_realm_prop ON auth.inventory (realm_prop_id)
     WHERE realm_prop_id IS NOT NULL;
+CREATE INDEX idx_inventory_server_state ON auth.inventory USING GIN (server_state)
+    WHERE server_state != '{}';
+CREATE INDEX idx_inventory_realm_state ON auth.inventory USING GIN (realm_state)
+    WHERE realm_state != '{}';
 
 -- =============================================================================
 -- User Avatars (moved from props.avatars)

db/schema/tables/030_realm.sql

@@ -197,6 +197,9 @@ CREATE TABLE realm.props (
     -- 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),
 
+    -- Optional JSON Schema for validating state structure
+    state_schema JSONB,
+
     is_unique BOOLEAN NOT NULL DEFAULT false,
     is_transferable BOOLEAN NOT NULL DEFAULT true,
     is_droppable BOOLEAN NOT NULL DEFAULT true,
@@ -223,6 +226,7 @@ CREATE TABLE realm.props (
 );
 
 COMMENT ON TABLE realm.props IS 'Realm-specific prop library';
+COMMENT ON COLUMN realm.props.state_schema IS 'Optional JSON Schema defining valid state structure for this prop';
 
 CREATE INDEX idx_realm_props_realm ON realm.props (realm_id);
 CREATE INDEX idx_realm_props_tags ON realm.props USING GIN (tags);

db/schema/tables/045_scene.sql

@@ -219,6 +219,15 @@ CREATE TABLE scene.loose_props (
     -- Auto-decay
     expires_at TIMESTAMPTZ,  -- NULL = permanent
 
+    -- Moderator lock (prevents non-moderators from interacting)
+    is_locked BOOLEAN NOT NULL DEFAULT false,
+    locked_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
+
+    -- Public state columns (loose props only have public state)
+    server_state JSONB NOT NULL DEFAULT '{}',
+    realm_state JSONB NOT NULL DEFAULT '{}',
+    user_state JSONB NOT NULL DEFAULT '{}',
+
     created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
 
     -- Must reference exactly one source
@@ -231,6 +240,11 @@ CREATE TABLE scene.loose_props (
 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)';
+COMMENT ON COLUMN scene.loose_props.is_locked IS 'If true, only moderators can move/scale/pickup this prop';
+COMMENT ON COLUMN scene.loose_props.locked_by IS 'User ID of the moderator who locked this prop';
+COMMENT ON COLUMN scene.loose_props.server_state IS 'Public state visible to everyone';
+COMMENT ON COLUMN scene.loose_props.realm_state IS 'Public state visible to realm members';
+COMMENT ON COLUMN scene.loose_props.user_state IS 'Public state from previous owner';
 
 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)
@@ -240,6 +254,10 @@ CREATE INDEX idx_scene_loose_props_expires ON scene.loose_props (expires_at)
 CREATE INDEX idx_scene_loose_props_position ON scene.loose_props
     USING GIST (position);
 
+-- GIN index for state queries
+CREATE INDEX idx_loose_props_server_state ON scene.loose_props USING GIN (server_state)
+    WHERE server_state != '{}';
+
 -- =============================================================================
 -- Scene Decorations (moved from props.scene_decorations)
 -- =============================================================================