Database

Artist Alley’s persistent state lives in Postgres behind sqlc — the schema in app/schema.sql is the Go-side declaration the query compiler validates against. 87 tables total, grouped below by what they own.

Table relationships

Foreign-key edges between every table in the catalogue. Click into a table for the column-level detail.

erDiagram
  activities {
  }
  ai_provider_call {
  }
  api_tokens {
  }
  asset_alternates {
  }
  asset_companions {
  }
  asset_embedding_d768 {
  }
  asset_field_value {
  }
  asset_field_value_history {
  }
  asset_subtitle_tracks {
  }
  asset_tag {
  }
  asset_type_acls {
  }
  asset_types {
  }
  asset_visual_embedding {
  }
  assets {
  }
  audit_events {
  }
  audit_retention_policy {
  }
  brush_pack_stamps {
  }
  brush_packs {
  }
  capabilities {
  }
  collection_acls {
  }
  collection_field_value {
  }
  collection_field_value_history {
  }
  collection_posts {
  }
  collection_resources {
  }
  collections {
  }
  comments {
  }
  creative_lineage {
  }
  digest_queue {
  }
  direct_messages {
  }
  email_verification_token {
  }
  extraction_failure {
  }
  featured_items {
  }
  federation_directories {
  }
  federation_directory_entries {
  }
  federation_dispatch_state {
  }
  federation_inbox {
  }
  federation_outbox {
  }
  federation_peer_suggestions {
  }
  federation_peers {
  }
  federation_remote_actors {
  }
  federation_shares {
  }
  federation_user_keys {
  }
  field_definition {
  }
  goose_db_version {
  }
  jobs {
  }
  likes {
  }
  mcp_server_registration {
  }
  mcp_server_tool_grant {
  }
  metadata_backfill_run {
  }
  notifications {
  }
  post_acls {
  }
  post_assets {
  }
  post_tags {
  }
  posts {
  }
  resource_request {
  }
  role_capabilities {
  }
  roles {
  }
  saved_search {
  }
  scheduled_actions {
  }
  search_feedback {
  }
  search_reindex_run {
  }
  search_visual_backfill_run {
  }
  sessions {
  }
  storage_objects {
  }
  storage_pins {
  }
  storage_sweep_findings {
  }
  storage_sweep_runs {
  }
  storage_variants {
  }
  system_config {
  }
  team_closure {
  }
  team_memberships {
  }
  team_parents {
  }
  teams {
  }
  user {
  }
  user_blocks {
  }
  user_capability_grants {
  }
  user_capability_revokes {
  }
  user_follows {
  }
  user_password_history {
  }
  user_preferences {
  }
  user_profiles {
  }
  user_roles {
  }
  user_totp {
  }
  user_totp_recovery_code {
  }
  workflow_audit {
  }
  workflow_states {
  }
  workflow_transitions {
  }
  ai_provider_call }o--|| jobs : job_id
  asset_alternates }|--|| assets : asset_id
  asset_alternates }|--|| storage_objects : object_hash
  asset_companions }|--|| assets : asset_id
  asset_companions }|--|| storage_objects : object_hash
  asset_embedding_d768 }|--|| assets : asset_id
  asset_field_value }|--|| assets : asset_id
  asset_field_value }|--|| field_definition : field_id
  asset_subtitle_tracks }|--|| assets : asset_id
  asset_tag }|--|| assets : asset_id
  asset_type_acls }|--|| asset_types : asset_type_ref
  asset_visual_embedding }|--|| assets : asset_id
  assets }|--|| asset_types : asset_type
  assets }o--|| storage_objects : file_hash
  assets }o--|| workflow_states : state_id
  assets }o--|| teams : team_id
  brush_pack_stamps }|--|| brush_packs : pack_id
  brush_packs }|--|| user : owner_user_ref
  collection_acls }|--|| collections : collection_id
  collection_field_value }|--|| collections : collection_id
  collection_field_value }|--|| field_definition : field_id
  collection_field_value_history }|--|| collections : collection_id
  collection_field_value_history }|--|| field_definition : field_id
  collection_posts }|--|| collections : collection_id
  collection_posts }|--|| posts : post_id
  collection_resources }|--|| collections : collection_id
  collection_resources }|--|| assets : asset_id
  comments }o--|| federation_peers : peer_id
  creative_lineage }|--|| assets : derivative_asset_id
  creative_lineage }|--|| assets : source_asset_id
  digest_queue }|--|| user : user_ref
  digest_queue }|--|| notifications : notification_id
  email_verification_token }|--|| user : user_ref
  extraction_failure }|--|| assets : asset_id
  featured_items }o--|| teams : team_id
  federation_directory_entries }|--|| federation_directories : directory_id
  federation_inbox }|--|| federation_peers : peer_id
  federation_inbox }o--|| activities : correlation_activity_id
  federation_outbox }|--|| activities : activity_id
  federation_outbox }|--|| federation_peers : peer_id
  federation_peer_suggestions }|--|| federation_peers : source_peer_id
  federation_remote_actors }|--|| federation_peers : peer_id
  federation_shares }|--|| federation_peers : peer_id
  federation_shares }|--|| activities : granted_activity_id
  federation_shares }o--|| activities : revoked_activity_id
  federation_user_keys }|--|| user : user_ref
  federation_user_keys }o--|| user : rotated_by_user_ref
  likes }o--|| federation_peers : peer_id
  mcp_server_tool_grant }|--|| mcp_server_registration : server_id
  metadata_backfill_run }o--|| user : started_by_user_ref
  post_acls }|--|| posts : post_id
  post_assets }|--|| posts : post_id
  post_assets }|--|| assets : asset_id
  post_tags }|--|| posts : post_id
  posts }o--|| assets : cover_asset_id
  posts }o--|| workflow_states : state_id
  posts }o--|| teams : team_id
  posts }o--|| assets : cover_thumbnail_asset_id
  resource_request }|--|| user : requester_user_ref
  resource_request }|--|| capabilities : requested_capability
  resource_request }o--|| user : decided_by_user_ref
  role_capabilities }|--|| roles : role_id
  role_capabilities }|--|| capabilities : capability_code
  saved_search }|--|| user : owner_user_ref
  search_feedback }|--|| assets : hit_asset_id
  search_feedback }|--|| user : user_ref
  search_reindex_run }o--|| user : started_by_user_ref
  search_visual_backfill_run }o--|| user : started_by_user_ref
  sessions }o--|| user : impersonated_by_user_ref
  storage_pins }|--|| storage_objects : object_hash
  storage_sweep_findings }|--|| storage_sweep_runs : run_id
  storage_variants }|--|| storage_objects : object_hash
  team_closure }|--|| teams : ancestor_id
  team_closure }|--|| teams : descendant_id
  team_memberships }|--|| teams : team_id
  team_parents }|--|| teams : child_id
  team_parents }|--|| teams : parent_id
  user_capability_grants }|--|| capabilities : capability_code
  user_capability_grants }o--|| teams : team_id
  user_capability_grants }o--|| resource_request : request_ref
  user_capability_revokes }|--|| capabilities : capability_code
  user_capability_revokes }o--|| teams : team_id
  user_roles }|--|| roles : role_id
  user_roles }o--|| teams : team_id
  user_totp }|--|| user : user_ref
  user_totp_recovery_code }|--|| user : user_ref
  workflow_audit }o--|| workflow_states : from_state_id
  workflow_audit }|--|| workflow_states : to_state_id
  workflow_transitions }o--|| workflow_states : from_state_id
  workflow_transitions }|--|| workflow_states : to_state_id
  workflow_transitions }o--|| capabilities : required_capability

Tables by domain

Identity & access

Users, sessions, capabilities, roles, API tokens, and the per-user grant / revoke overlay.

Table	Summary
`user`	No description.
`user_profiles`	No description.
`sessions`	No description.
`api_tokens`	No description.
`capabilities`	No description.
`roles`	No description.
`role_capabilities`	No description.
`user_roles`	No description.
`user_capability_grants`	No description.
`user_capability_revokes`	No description.

Teams

Team hierarchy stored as both adjacency parents and a closure table for fast reachability queries.

Table	Summary
`teams`	No description.
`team_parents`	No description.
`team_closure`	No description.
`team_memberships`	No description.

Assets

The core asset row, asset-type lookup, tags, and the companion / alternate file relations.

Table	Summary
`asset_types`	No description.
`assets`	No description.
`asset_tag`	No description.
`asset_companions`	No description.
`asset_alternates`	No description.

Metadata

Admin-extensible field definitions, per-asset typed values, and the append-only history table.

Table	Summary
`field_definition`	No description.
`asset_field_value`	No description.
`asset_field_value_history`	No description.

Collections

Collections wrap assets and posts; per-collection ACLs cover team / role / user grants.

Table	Summary
`collections`	No description.
`collection_resources`	No description.
`collection_posts`	No description.
`collection_acls`	No description.

Posts

Posts wrap one or many assets with shared metadata, tags, and per-post ACLs.

Table	Summary
`posts`	No description.
`post_assets`	No description.
`post_tags`	No description.
`post_acls`	No description.

Workflow

Per-asset / per-post state machine: states, allowed transitions, and the audit trail of state changes.

Table	Summary
`workflow_states`	No description.
`workflow_transitions`	No description.
`workflow_audit`	No description.

Storage

Content-addressed storage objects, derived variants (thumbs / previews / waveforms), and reference-count pins.

Table	Summary
`storage_objects`	No description.
`storage_variants`	No description.
`storage_pins`	No description.

Per-post comments thread and likes.

Table	Summary
`comments`	No description.
`likes`	No description.

Audit

Project-wide event ledger consumed by the audit log surface.

Table	Summary
`audit_events`	No description.

Jobs

Async work queue for previews, ingest, federation, and scheduled actions.

Table	Summary
`jobs`	No description.

Annotations

Brush packs and stamp library powering the whiteboard / review annotation tools.

Table	Summary
`brush_packs`	No description.
`brush_pack_stamps`	No description.

System

Server-wide configuration.

Table	Summary
`system_config`	No description.

Other

Tables not yet sorted into a domain group.

Table	Summary
`activities`	No description.
`ai_provider_call`	No description.
`asset_embedding_d768`	No description.
`asset_subtitle_tracks`	No description.
`asset_type_acls`	No description.
`asset_visual_embedding`	No description.
`audit_retention_policy`	No description.
`collection_field_value`	No description.
`collection_field_value_history`	No description.
`creative_lineage`	No description.
`digest_queue`	No description.
`direct_messages`	No description.
`email_verification_token`	No description.
`extraction_failure`	No description.
`featured_items`	No description.
`federation_directories`	No description.
`federation_directory_entries`	No description.
`federation_dispatch_state`	No description.
`federation_inbox`	No description.
`federation_outbox`	No description.
`federation_peer_suggestions`	No description.
`federation_peers`	No description.
`federation_remote_actors`	No description.
`federation_shares`	No description.
`federation_user_keys`	No description.
`goose_db_version`	No description.
`mcp_server_registration`	No description.
`mcp_server_tool_grant`	No description.
`metadata_backfill_run`	No description.
`notifications`	No description.
`resource_request`	No description.
`saved_search`	No description.
`scheduled_actions`	No description.
`search_feedback`	No description.
`search_reindex_run`	No description.
`search_visual_backfill_run`	No description.
`storage_sweep_findings`	No description.
`storage_sweep_runs`	No description.
`user_blocks`	No description.
`user_follows`	No description.
`user_password_history`	No description.
`user_preferences`	No description.
`user_totp`	No description.
`user_totp_recovery_code`	No description.

Reading the per-table pages

Each table page lists every column with its type, nullability, and notes — including any foreign-key target and the column’s default value. A Connected to panel summarises which other tables this one points at; a Referenced by panel lists every table that points back. The full DDL is at the bottom so you can read the exact constraints.

Schema changes land as goose migrations under app/internal/db/migrations/; app/schema.sql is updated to mirror them so the next site build picks up the change.