Take control over your live stream video by running it yourself. Streaming + chat out of the box.
HTML activitypubbroadcastingchatdecentralizedfederationfediversegolanghacktoberfesthlslivelivestreamowncastrtmpself-hostedstreaming-videovideo
-- Queries added to query.sql must be compiled into Go code with sqlc. Read README.md for details.
-- Federation related queries.
-- name: GetFollowerCount :one
-- Featured-streams follows (another Owncast server following us so it can show
-- our live status in its directory) are excluded: they are a directory
-- relationship, not a fan follow, so they must not inflate the follower count.
SELECT count(*) FROM ap_followers WHERE approved_at is not null AND directory IS NOT 1;
-- name: GetLocalPostCount :one
-- Only posts (Notes) count: the outbox table also stores other
-- dereferenceable objects, such as QuoteAuthorization stamps, that are not
-- posts and must not inflate the public post count.
SELECT count(*) FROM ap_outbox WHERE type = 'Note';
-- name: GetFederationFollowersWithOffset :many
-- Excludes featured-streams (Owncast-server) follows so they don't show up in
-- the public or admin followers list; they are tracked as a directory
-- relationship, not surfaced as followers.
-- rowid breaks created_at ties (second resolution) so pagination order is
-- deterministic; without it, same-second follows shift between pages.
SELECT iri, inbox, shared_inbox, name, username, image, created_at FROM ap_followers WHERE approved_at is not null AND directory IS NOT 1 ORDER BY created_at DESC, rowid DESC LIMIT ? OFFSET ?;
-- name: GetRejectedAndBlockedFollowers :many
SELECT iri, name, username, image, created_at, disabled_at FROM ap_followers WHERE disabled_at is not null;
-- name: GetFederationFollowerApprovalRequests :many
-- Regular (fan) follow approval requests only. Featured-streams (Owncast
-- server) requests are excluded here and surfaced separately via
-- GetPendingFeaturedFollowRequests so they can be approved from the featured
-- streams admin instead of the followers admin.
SELECT iri, inbox, shared_inbox, name, username, image, created_at FROM ap_followers WHERE approved_at IS null AND disabled_at is null AND directory IS NOT 1;
-- name: GetPendingFeaturedFollowRequests :many
-- Pending requests from other Owncast servers asking to feature this server's
-- stream in their directory. These always require explicit approval.
SELECT iri, inbox, shared_inbox, name, username, image, created_at FROM ap_followers WHERE approved_at IS null AND disabled_at is null AND directory IS 1 ORDER BY created_at DESC;
-- name: ApproveFederationFollower :exec
UPDATE ap_followers SET approved_at = ?, disabled_at = null WHERE iri = ?;
-- name: RejectFederationFollower :exec
UPDATE ap_followers SET approved_at = null, disabled_at = ? WHERE iri = ?;
-- name: GetFollowerByIRI :one
SELECT iri, inbox, shared_inbox, name, username, image, request, request_object, created_at, approved_at, disabled_at, directory FROM ap_followers WHERE iri = ?;
-- name: GetOutboxWithOffset :many
-- Only posts (Notes) are listed in the public outbox collection. Other stored
-- objects, such as QuoteAuthorization stamps, stay fetchable by IRI but are
-- not part of the collection.
SELECT value FROM ap_outbox WHERE type = 'Note' LIMIT ? OFFSET ?;
-- name: GetObjectFromOutboxByIRI :one
SELECT value, live_notification, created_at FROM ap_outbox WHERE iri = ?;
-- name: GetNoteFromOutboxByIRI :one
-- Like GetObjectFromOutboxByIRI but only matches posts (Notes). Used to
-- decide whether an object may be quoted: stamps and directory pings are
-- stored in the same table and are not quotable.
SELECT value, live_notification, created_at FROM ap_outbox WHERE iri = ? AND type = 'Note';
-- name: RemoveFollowerByIRI :exec
DELETE FROM ap_followers WHERE iri = ?;
-- name: AddFollower :exec
INSERT INTO ap_followers(iri, inbox, shared_inbox, request, request_object, name, username, image, approved_at, directory) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
-- name: AddToOutbox :exec
INSERT INTO ap_outbox(iri, value, type, live_notification) values(?, ?, ?, ?);
-- name: QueueActivityPubDelivery :one
INSERT INTO ap_delivery_queue (
inbox,
payload,
actor_iri,
activity_type,
coalesce_key,
next_attempt_at
) VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(inbox, coalesce_key) WHERE coalesce_key IS NOT NULL AND failed_at IS NULL
DO UPDATE SET
payload = excluded.payload,
actor_iri = excluded.actor_iri,
activity_type = excluded.activity_type,
next_attempt_at = excluded.next_attempt_at,
attempts = 0,
last_error = NULL,
revision = ap_delivery_queue.revision + 1
RETURNING id;
-- name: ClaimActivityPubDelivery :one
UPDATE ap_delivery_queue
SET claimed_until = @claimed_until,
attempts = attempts + 1
WHERE id = (
SELECT candidate.id
FROM ap_delivery_queue AS candidate
WHERE candidate.failed_at IS NULL
AND candidate.next_attempt_at <= @now
AND (candidate.claimed_until IS NULL OR candidate.claimed_until <= @now)
ORDER BY candidate.next_attempt_at, candidate.id
LIMIT 1
)
RETURNING id, inbox, payload, actor_iri, activity_type, coalesce_key, attempts, revision;
-- name: CompleteActivityPubDelivery :execrows
DELETE FROM ap_delivery_queue WHERE id = ? AND revision = ?;
-- name: RetryActivityPubDelivery :execrows
UPDATE ap_delivery_queue
SET claimed_until = NULL,
next_attempt_at = ?,
last_error = ?
WHERE id = ? AND revision = ?;
-- name: DeferActivityPubDelivery :execrows
UPDATE ap_delivery_queue
SET claimed_until = NULL,
next_attempt_at = ?,
attempts = MAX(attempts - 1, 0)
WHERE id = ? AND revision = ?;
-- name: FailActivityPubDelivery :execrows
UPDATE ap_delivery_queue
SET claimed_until = NULL,
last_error = ?,
failed_at = ?
WHERE id = ? AND revision = ?;
-- name: ReleaseSupersededActivityPubDelivery :exec
UPDATE ap_delivery_queue SET claimed_until = NULL WHERE id = ? AND revision != ?;
-- name: AddToAcceptedActivities :exec
INSERT INTO ap_accepted_activities(iri, actor, type, timestamp) values(?, ?, ?, ?);
-- name: GetInboundActivityCount :one
SELECT count(*) FROM ap_accepted_activities;
-- name: GetInboundActivitiesWithOffset :many
SELECT iri, actor, type, timestamp FROM ap_accepted_activities ORDER BY timestamp DESC LIMIT ? OFFSET ?;
-- name: DoesInboundActivityExist :one
SELECT count(*) FROM ap_accepted_activities WHERE iri = ? AND actor = ? AND TYPE = ?;
-- name: UpdateFollowerByIRI :exec
UPDATE ap_followers SET inbox = ?, shared_inbox = ?, name = ?, username = ?, image = ? WHERE iri = ?;
-- name: GetFollowersToValidate :many
SELECT iri, inbox, shared_inbox, name, username, image, first_validation_failure_at
FROM ap_followers
WHERE approved_at IS NOT NULL AND disabled_at IS NULL
ORDER BY last_validated_at ASC NULLS FIRST
LIMIT ?;
-- name: UpdateFollowerValidationSuccess :exec
UPDATE ap_followers
SET last_validated_at = ?, first_validation_failure_at = NULL
WHERE iri = ?;
-- name: UpdateFollowerValidationFailure :exec
UPDATE ap_followers
SET last_validated_at = @last_validated_at, first_validation_failure_at = COALESCE(first_validation_failure_at, @last_validated_at)
WHERE iri = @iri;
-- name: GetUniqueDeliveryInboxes :many
SELECT COALESCE(shared_inbox, inbox) as delivery_inbox FROM ap_followers WHERE approved_at is not null GROUP BY delivery_inbox;
-- name: GetUniqueDirectoryDeliveryInboxes :many
-- Approved directory followers only. The Offer/Leave stream pings are delivered
-- here, not to fan followers, who only need the go-live Create/Note.
SELECT COALESCE(shared_inbox, inbox) as delivery_inbox FROM ap_followers WHERE approved_at is not null AND directory IS 1 GROUP BY delivery_inbox;
-- name: GetApprovedDirectoryFollowers :many
-- Approved directories that are featuring/listing this server. Shown in the
-- admin so the operator can review and remove them.
SELECT iri, inbox, shared_inbox, name, username, image, created_at FROM ap_followers WHERE approved_at IS NOT NULL AND disabled_at IS NULL AND directory IS 1 ORDER BY created_at DESC;
-- name: BanIPAddress :exec
INSERT INTO ip_bans(ip_address, notes) values(?, ?);
-- name: RemoveIPAddressBan :exec
DELETE FROM ip_bans WHERE ip_address = ?;
-- name: IsIPAddressBlocked :one
SELECT count(*) FROM ip_bans WHERE ip_address = ?;
-- name: GetIPAddressBans :many
SELECT * FROM ip_bans;
-- name: AddNotification :exec
INSERT INTO notifications (channel, destination) VALUES(?, ?);
-- name: GetNotificationDestinationsForChannel :many
SELECT destination FROM notifications WHERE channel = ?;
-- name: RemoveNotificationDestinationForChannel :exec
DELETE FROM notifications WHERE channel = ? AND destination = ?;
-- name: AddAuthForUser :exec
INSERT INTO auth(user_id, auth_key, type, provider, profile_url, handle, is_public) values(?, ?, ?, ?, ?, ?, ?);
-- name: GetUserByAuth :one
SELECT users.id, display_name, display_color, users.created_at, disabled_at, disabled_reason, previous_names, namechanged_at, authenticated_at, scopes FROM auth, users WHERE auth_key = ? AND auth.type = ? AND users.id = auth.user_id;
-- name: GetUserByPluginAuth :one
-- Plugin-auth lookup keyed on (provider, auth_key). The type literal mirrors
-- models.PluginAuth and pins the lookup to the plugin namespace, so a plugin
-- slug can never resolve a built-in identity.
SELECT users.id, display_name, display_color, users.created_at, disabled_at, disabled_reason, previous_names, namechanged_at, authenticated_at, scopes FROM auth, users WHERE auth.type = 'plugin.auth' AND auth.provider = ? AND auth.auth_key = ? AND users.id = auth.user_id;
-- name: CountUserAuthByProvider :one
SELECT count(*) FROM auth WHERE user_id = ? AND type = ? AND provider = ?;
-- name: GetAuthForUsers :many
-- External auth identities (type + provider) for the given users, so the admin
-- user list can show how each authenticated user signed in.
SELECT user_id, type, provider FROM auth WHERE user_id IN (sqlc.slice('user_ids'));
-- name: AddAccessTokenForUser :exec
INSERT INTO user_access_tokens(token, user_id) values(?, ?);
-- name: GetUserByAccessToken :one
SELECT users.id, display_name, display_color, users.created_at, disabled_at, disabled_reason, previous_names, namechanged_at, authenticated_at, scopes, users.type = 'API' AS is_bot FROM users, user_access_tokens WHERE token = ? AND users.id = user_id;
-- name: GetUserByID :one
SELECT id, display_name, display_color, created_at, disabled_at, disabled_reason, previous_names, namechanged_at, authenticated_at, scopes, type = 'API' AS is_bot FROM users WHERE id = ?;
-- name: GetUsers :many
SELECT id, display_name, display_color, created_at, disabled_at, disabled_reason, previous_names, namechanged_at, authenticated_at, scopes, type = 'API' AS is_bot FROM users ORDER BY created_at DESC;
-- name: GetUserDisplayNameByToken :one
SELECT display_name FROM users JOIN user_access_tokens ON users.id = user_access_tokens.user_id WHERE token = ? AND users.disabled_at IS NULL;
-- name: SetAccessTokenToOwner :exec
UPDATE user_access_tokens SET user_id = ? WHERE token = ?;
-- name: SetUserAsAuthenticated :exec
UPDATE users SET authenticated_at = CURRENT_TIMESTAMP WHERE id = ?;
-- name: GetMessagesFromUser :many
SELECT id, body, hidden_at, timestamp FROM messages WHERE eventType = 'CHAT' AND user_id = ? ORDER BY TIMESTAMP DESC;
-- name: IsDisplayNameAvailable :one
SELECT count(*) FROM users WHERE display_name = ? AND ( type='API' OR authenticated_at IS NOT NULL ) AND disabled_at IS NULL;
-- name: ChangeDisplayName :exec
UPDATE users SET display_name = ?, previous_names = previous_names || ?, namechanged_at = ? WHERE id = ?;
-- name: ChangeDisplayColor :exec
UPDATE users SET display_color = ? WHERE id = ?;
-- name: GetUsersPaginated :many
-- A page of users of every type (chat viewers, authenticated/plugin users, and
-- API integrations), newest first, filtered to display names containing
-- @search and an optional @status: '' or 'all' = every user; otherwise
-- 'active' (not banned) or 'bots' (API users). An empty @search matches every
-- user (LIKE '%%'). The complete banned and moderator sets come from
-- GetDisabledUsers and GetModeratorUsers instead, since those need every match
-- rather than a page. GetUsersPaginatedAsc is the oldest-first counterpart;
-- the two exist as separate queries because sqlc can't bind a sort direction
-- into ORDER BY.
SELECT id, display_name, display_color, created_at, disabled_at, disabled_reason, previous_names, namechanged_at, authenticated_at, scopes, type = 'API' AS is_bot
FROM users
WHERE display_name LIKE '%' || @search || '%'
AND (
@status = '' OR @status = 'all'
OR (@status = 'active' AND disabled_at IS NULL)
OR (@status = 'bots' AND type = 'API')
)
ORDER BY created_at DESC
LIMIT @page_limit OFFSET @page_offset;
-- name: GetUsersPaginatedAsc :many
-- Oldest-first counterpart to GetUsersPaginated. Same @search/@status filter,
-- only the created_at ordering differs.
SELECT id, display_name, display_color, created_at, disabled_at, disabled_reason, previous_names, namechanged_at, authenticated_at, scopes, type = 'API' AS is_bot
FROM users
WHERE display_name LIKE '%' || @search || '%'
AND (
@status = '' OR @status = 'all'
OR (@status = 'active' AND disabled_at IS NULL)
OR (@status = 'bots' AND type = 'API')
)
ORDER BY created_at ASC
LIMIT @page_limit OFFSET @page_offset;
-- name: CountUsers :one
-- Total number of users matching the same @search/@status filter as GetUsersPaginated.
SELECT count(*) FROM users
WHERE display_name LIKE '%' || @search || '%'
AND (
@status = '' OR @status = 'all'
OR (@status = 'active' AND disabled_at IS NULL)
OR (@status = 'bots' AND type = 'API')
);
-- name: DeleteUserAccessTokens :exec
DELETE FROM user_access_tokens WHERE user_id = ?;
-- name: DeleteUserAuth :exec
DELETE FROM auth WHERE user_id = ?;
-- name: DeleteUserMessages :exec
DELETE FROM messages WHERE user_id = ?;
-- name: DeleteUserByID :execrows
DELETE FROM users WHERE id = ?;
-- Federated servers queries
-- name: GetFederatedServers :many
SELECT id, iri, name, logo_url, is_online, stream_title, stream_description, stream_tags, thumbnail_url, last_seen_online, last_status_update, added_at, followed_at, pending, username, display_name, summary, accepted_at, rejected_at, follow_status, priority FROM federated_servers ORDER BY priority ASC, added_at ASC;
-- name: GetFederatedServer :one
SELECT id, iri, name, logo_url, is_online, stream_title, stream_description, stream_tags, thumbnail_url, last_seen_online, last_status_update, added_at, followed_at, pending, username, display_name, summary, accepted_at, rejected_at, follow_status, priority FROM federated_servers WHERE iri = ?;
-- name: AddFederatedServer :exec
INSERT INTO federated_servers(iri, name, logo_url, followed_at, pending, username, follow_status) values(?, ?, ?, ?, ?, ?, ?);
-- name: UpdateFederatedServerStatus :exec
UPDATE federated_servers SET is_online = ?, stream_title = ?, stream_description = ?, stream_tags = ?, thumbnail_url = ?, last_status_update = ? WHERE iri = ?;
-- name: UpdateFederatedServerOnlineStatus :exec
UPDATE federated_servers SET is_online = ?, last_seen_online = ?, last_status_update = ? WHERE iri = ?;
-- name: RemoveFederatedServer :exec
DELETE FROM federated_servers WHERE id = ?;
-- name: UpdateFederatedServerFollowStatus :exec
UPDATE federated_servers SET follow_status = ?, pending = ?, accepted_at = ?, rejected_at = ? WHERE iri = ?;
-- name: UpdateFederatedServerMetadata :exec
UPDATE federated_servers SET name = ?, display_name = ?, summary = ?, logo_url = ? WHERE iri = ?;
-- name: SetFederatedServerPriority :exec
UPDATE federated_servers SET priority = ? WHERE id = ?;
-- name: GetMaxAcceptedFederatedServerPriority :one
SELECT CAST(COALESCE(MAX(priority), 0) AS INTEGER) FROM federated_servers WHERE follow_status = 'accepted';
-- name: GetPendingFederatedServers :many
SELECT id, iri, name, logo_url, is_online, stream_title, stream_description, stream_tags, thumbnail_url, last_seen_online, last_status_update, added_at, followed_at, pending, username, display_name, summary, accepted_at, rejected_at, follow_status, priority FROM federated_servers WHERE pending = true ORDER BY added_at DESC;