Routines

Removes the global ban from a user, clearing the ban reason and expiration timestamp.

Returns the number of non-deleted chat messages in a given stream.

Updates stream metadata including title, description, and thumbnail. NULL values are not updated.

Rotates a refresh token, generates a new raw token and returns it for client use (optional implementation).

Retrieves the latest statistic entry for a specific stream and statistic type.

Deletes a role by name if no permissions are assigned to it. Returns TRUE on success, FALSE if permissions are assigned.

Validates that a token hash is unique before inserting (optional - unique index already exists)

Retrieves the token history for a user with optional limit, sorted by most recent first.

Retrieves a permission by its name.

Returns the number of streamers a user is subscribed to.

Returns the average value of a statistic for the given stream and type. If the stream ended, averages only values until the ended_at.

Associates a file path (e.g., media storage path) with a stream.

Retrieves all permissions assigned to a role by role ID.

Calculates the sum of statistic values for a specific stream and type. If the stream has ended, the sum includes only values up to ended_at.

Returns the edit history for a given chat message, sorted by most recent first.

Sets the live status of a stream.

Updates the stream token for a user to the specified value.

Creates a new user account if username and email do not already exist. Returns the newly created or existing user.

Assigns a role to a user within a specific context (e.g., streamer moderator). Returns TRUE if assignment was successful.

Retrieves all streamers that a user is subscribed to with subscription timestamps.

Assigns a role (by name) to a user globally. Returns TRUE if successful, FALSE if role not found.

Retrieves a stream statistic type by its exact name.

Retrieves all roles from the system.

Retrieves the top streamers ranked by subscriber count, useful for discovery features.

Returns all chat messages in a stream, including deleted ones, paginated.

Returns the total number of statistic entries for a specific stream.

Updates the last_used_at timestamp of a session to track user activity.

Retrieves statistic entries for a specific stream within a date range, including type name and description, ordered by timepoint descending. Supports pagination with limit and offset parameters.

Revokes a role from a user globally. Returns TRUE on success, FALSE if role assignment not found.

Checks if a user has a specific permission (by name) within a given context through their assigned roles.

Retrieves a permission by its ID.

Updates the profile banner image URL for a user.

Cleanup function that marks expired sessions and tokens as inactive/revoked for database maintenance.

Retrieves all permissions assigned to a role by role name.

Checks if a subscription relationship already exists between a user and a streamer.

Marks a refresh token as used by updating its used_at timestamp for audit purposes.

Deletes statistic entries for a specific stream older than the specified date. Returns the number of deleted records.

Checks if a user has a specific permission (by ID) within a given context through their assigned roles.

Deletes all statistic entries for a specific statistic type across all streams. Returns the number of deleted entries.

Creates a new live stream for a streamer. Raises exception if user is not a streamer or already has active stream.

Checks if a chat message with the given ID exists.

Retrieves subscribers for a streamer with pagination, sorted by subscription date.

Retrieves the most recent subscriptions for a user with optional limit.

Returns the edit history for a chat message, paginated with optional LIMIT and OFFSET.

Updates an existing stream statistic type by ID. Returns the updated statistic type. Raises an exception if the type does not exist.

Updates the avatar image URL for a user.

Returns the total number of stream statistic types.

Checks if a user has a specific permission (by ID) through their assigned roles.

Checks if a user with the specified user ID exists in the database.

Updates the username of a user.

Returns the top streams ranked by total statistic value for a specific type, optionally within a date range.

Updates the email address of a user.

Checks if the specified user is the author of the given chat message.

Deletes all statistic entries for a specific stream. Returns the number of deleted entries.

Atomically rotates a refresh token by marking old token as replaced and creating a new one. Ensures race condition safety.

Retrieves all roles assigned to a user within a specific context.

Checks if a user currently has an active live stream.

Deletes all statistic entries older than the specified date. Returns the number of deleted records.

Deletes a stream statistic type by ID. This will also cascade delete all related stream statistics. Returns TRUE if the deletion was successful, FALSE otherwise.

Retrieves a specific stream statistic type by ID.

Bans a user from a streamer chat. Ban can be permanent or temporary with optional expiration date.

Checks if a user currently has an active public stream.

Returns deleted chat messages in a stream, paginated with optional LIMIT and OFFSET.

Revokes all sessions and tokens for a specific user, effectively logging them out everywhere.

Permanently deletes a user and all associated data from the system.

Extends the expiration time of an existing session.

Creates a new user session with device information and expiration time. Returns the created session record.

Returns a summary of all statistics in the database including counts, date ranges, and streams with statistics.

Sets the lock status of a stream (locks prevent new interactions)

Retrieves a role by its name.

Returns all deleted chat messages in a given stream.

Restores a previously deleted chat message.

Restores all deleted chat messages in a given stream and returns the count of restored messages.

Deletes a permission by name. Returns TRUE on success, FALSE if permission not found.

Ends a live stream by marking it as not live and setting the end timestamp.

Checks if a stream statistic type with the given name exists. Returns TRUE if exists, FALSE otherwise.

Updates the password hash for a user.

Removes a subscription between a user and streamer. Returns TRUE if subscription was deleted, FALSE if it did not exist.

Globally bans a user with a specified reason. The user is marked as banned in the system.

Retrieves all permissions from the system.

Retrieves statistic entries for a specific stream and statistic type, including type name and description, ordered by timepoint descending. Supports pagination with limit and offset.

Permanently deletes revoked tokens older than the specified number of days (database pruning/maintenance).

Returns hourly aggregated statistics (avg, min, max, count) for a stream and type within a date range.

Generates a random access token of specified length using hexadecimal encoding. Minimum length is 8 characters.

Ends all active streams in the system.

Ends all active streams for a specific user/streamer.

Returns all non-deleted chat messages from a specific user in a stream, paginated.

Retrieves a role by its ID.

Retrieves subscriptions for a user with pagination, sorted by subscription date.

Retrieves all refresh tokens associated with a specific session.

Deletes a permission by ID. Returns TRUE on success, FALSE if permission not found.

Checks if a user is a streamer by verifying they have a non-empty stream token.

Retrieves all global roles assigned to a user (without context).

Generates a SHA256 hash of a raw token with optional pepper for secure token storage.

Retrieves all users subscribed to a streamer with subscription timestamps.

Updates an existing stream statistic entry by ID. Returns the updated statistic entry. Raises an exception if the entry does not exist.

Retrieves all currently active and public streams.

Retrieves all active and non-expired sessions for a user.

Returns the maximum value for the requested statistic type. If the stream ended, it only considers values up to ended_at.

Retrieves all streams (active and inactive) for a specific user/streamer.

Retrieves a refresh token record by its hash.

Removes all subscribers from a streamer and returns count of deleted subscriptions.

Retrieves a stream by its ID.

Revokes all refresh tokens associated with a specific session.

Retrieves a user by their email address.

Revokes a role (by name) from a user within a specific context. Returns TRUE on success, FALSE if role not found or assignment does not exist.

Issues a new refresh token for a session by hashing the raw token and storing the hash in the database.

Retrieves all global permissions granted to a user through their assigned roles.

Creates a new chat message in a stream and returns the created message.

Checks if a user has a specific permission within a given context.

Returns the number of subscribers for a streamer.

Creates a new role if it does not already exist. Returns the existing or newly created role.

Retrieves a user by their user ID.

Retrieves detailed subscription information including both usernames for a specific user-streamer relationship.

Assigns a permission to a role. Returns TRUE if assignment was successful or already exists.

Returns paginated chat messages for a given stream, excluding deleted messages.

Adds a new stream statistic entry for the specified stream and statistic type. Returns the newly created statistic entry.

Revokes a role from a user within a specific context. Returns TRUE on success, FALSE if role assignment not found.

Adds a subscription from a user to a streamer. Returns existing subscription if already subscribed, otherwise creates new subscription.

Retrieves all statistic entries for a specific stream, including type name and description, ordered by timepoint descending. Supports pagination with limit and offset parameters.

Checks if a stream statistic type with the given ID exists. Returns TRUE if exists, FALSE otherwise.

Returns daily aggregated statistics (avg, min, max, count) for a stream and type within a date range.

Checks if a user has a specific global permission through their assigned roles.

Retrieves all sessions with their last token issuance timestamp, useful for session management UI.

Retrieves all stream statistic types ordered by name.

Revokes a session and all its associated refresh tokens.

Gets the minimum statistic value for a specific stream and type. If the stream has ended, the result includes only values up to ended_at.

Revokes a permission from a role. Returns TRUE on success, FALSE if permission assignment not found.

Updates replaced_by_id field of an old token to link it to a new token during token rotation.

Validates a refresh token by checking if it is active, not revoked, not expired, not replaced, and its session is valid.

Permanently deletes a chat message from the database and returns TRUE if a row was deleted, FALSE otherwise.

Revokes a role (by name) from a user globally. Returns TRUE on success, FALSE if role not found or assignment does not exist.

Deletes a specific stream statistic entry by ID. Returns TRUE if the deletion was successful, FALSE otherwise.

Removes all subscriptions from a user and returns count of deleted subscriptions.

Returns the most recent non-deleted chat message in a given stream.

Updates the description/bio of a user.

Creates a new permission if it does not already exist. Returns the existing or newly created permission.

Updates the content of a chat message and records the old content in the edit history.

Generates and assigns a new random stream token to a user.

Assigns a role to a user globally (without context). Returns TRUE if successful.

Deletes a role by ID if no permissions are assigned to it. Returns TRUE on success, FALSE if permissions are assigned.

Marks a chat message as deleted (soft delete).

Creates a new stream statistic type with the given name and optional description. Returns the newly created statistic type. If a type with the same name already exists, returns the existing type.

Revokes a refresh token and optionally revokes its associated session.

Returns the number of edits made to a given chat message.