Database Schema Reference

Core Tables

users

The central table storing all user account information and authentication data.

Primary Key: id (UUID)

Columns

Column	Type	Nullable	Default	Description
id	UUID	NOT NULL	-	Unique user identifier
username	VARCHAR(128)	NOT NULL	-	Unique username for the account
display_name	VARCHAR(255)	NULL	-	User's display name shown in UI
avatar_url	VARCHAR(512)	NULL	-	URL to user's profile avatar image
lang_tag	VARCHAR(18)	NOT NULL	'en'	Language preference (ISO 639-1 code)
location	VARCHAR(255)	NULL	-	User's geographic location
timezone	VARCHAR(255)	NULL	-	User's timezone (e.g., "America/New_York")
metadata	JSONB	NOT NULL	'{}'	Custom metadata for user profile
wallet	JSONB	NOT NULL	'{}'	Virtual wallet/currency data
email	VARCHAR(255)	NULL	-	User's email address (unique if set)
password	BYTEA	NULL	-	Hashed password for email authentication
facebook_id	VARCHAR(128)	NULL	-	Facebook OAuth identifier
google_id	VARCHAR(128)	NULL	-	Google OAuth identifier
gamecenter_id	VARCHAR(128)	NULL	-	Apple Game Center identifier
steam_id	VARCHAR(128)	NULL	-	Steam OAuth identifier
custom_id	VARCHAR(128)	NULL	-	Custom device/anonymous identifier
apple_id	VARCHAR(128)	NULL	-	Apple Sign In identifier
facebook_instant_game_id	VARCHAR(128)	NULL	-	Facebook Instant Games identifier
edge_count	INTEGER	NOT NULL	0	Number of social connections (friends/followers)
create_time	TIMESTAMPTZ	NOT NULL	now()	Account creation timestamp
update_time	TIMESTAMPTZ	NOT NULL	now()	Last account update timestamp
verify_time	TIMESTAMPTZ	NOT NULL	'1970-01-01'	Email verification timestamp
disable_time	TIMESTAMPTZ	NOT NULL	'1970-01-01'	Account disable/ban timestamp

Indexes

• Primary Key: users_pkey on (id)
• Unique Constraints: All OAuth IDs, email, username, and custom_id have unique constraints
• Ensures one account per authentication method

Constraints

• users_edge_count_check: Edge count must be ≥ 0
• users_password_check: Password length must be < 32,000 bytes

Referenced By

This table is referenced by:

• storage - User-owned storage objects
• user_device - Device associations
• user_edge - Social connections
• message - Chat messages
• notification - Push notifications
• wallet_ledger - Transaction history
• purchase & subscription - In-app purchases

---

storage

Stores user-generated data in a flexible key-value format with access control and versioning.

Primary Key: (collection, key, user_id)

Columns

Column	Type	Nullable	Default	Description
collection	VARCHAR(128)	NOT NULL	-	Logical grouping of related data (e.g., "workshops", "sessions")
key	VARCHAR(128)	NOT NULL	-	Unique identifier within the collection
user_id	UUID	NOT NULL	-	Owner of this storage object
value	JSONB	NOT NULL	'{}'	Stored data in JSON format
version	VARCHAR(32)	NOT NULL	-	Version string for optimistic concurrency control
read	SMALLINT	NOT NULL	1	Read permission level (0=private, 1=owner, 2=public)
write	SMALLINT	NOT NULL	1	Write permission level (0=private, 1=owner, 2=public)
create_time	TIMESTAMPTZ	NOT NULL	now()	Creation timestamp
update_time	TIMESTAMPTZ	NOT NULL	now()	Last modification timestamp

Indexes

• Primary Key: storage_pkey on (collection, key, user_id)
• collection_read_key_user_id_idx - Optimized for filtering by collection and read permission
• collection_read_user_id_key_idx - Optimized for user-specific queries
• collection_user_id_read_key_idx - Optimized for user's readable objects
• storage_auto_index_fk_user_id_ref_users - Foreign key index

Constraints

• storage_read_check: Read permission must be ≥ 0
• storage_write_check: Write permission must be ≥ 0
• Foreign Key: user_id references users(id) with CASCADE delete

Permission Levels

Value	Level	Description
0	Private	Only accessible by server-side code
1	Owner	Accessible by the owner user
2	Public	Accessible by any authenticated user

Usage in humuus

The storage table is used for:

• Workshop configurations (collection: "workshops")
• Session state (collection: "sessions")
• User progress (collection: "progress")
• Participant responses (collection: "responses")

---

user_tombstone

Tracks deleted user accounts for compliance and prevents ID reuse.

Primary Key: (create_time, user_id)

Columns

Column	Type	Nullable	Default	Description
user_id	UUID	NOT NULL	-	ID of the deleted user account
create_time	TIMESTAMPTZ	NOT NULL	now()	Timestamp when user was deleted

Indexes

• Primary Key: user_tombstone_pkey on (create_time, user_id)
• Unique Constraint: user_tombstone_user_id_key on (user_id) - Prevents duplicate tombstone entries

Purpose

This table serves several important functions:

1. Audit Trail: Maintains a record of deleted accounts for compliance (GDPR, etc.)
2. ID Protection: Prevents reuse of user IDs that could cause security or data integrity issues
3. Cascade Cleanup: When a user is deleted, related data is removed via foreign key cascades, but the tombstone remains
4. Retention Policy: Can be used to implement data retention policies (e.g., purge tombstones after X years)

Lifecycle

When a user account is deleted:

1. A tombstone record is created with the user's ID
2. The user record is deleted from the users table
3. Foreign key cascades automatically delete related data:
   • Storage objects
   • Messages
   • Notifications
   • Device associations
   • Social connections

warning

Tombstone records should be retained for compliance purposes. Implement a separate purge process if tombstones need to be removed after a retention period.

---

Relationships