Database Schema

The BOSA Authentication system creates and maintains several database tables to support its functionality. Understanding this schema is important for developers who need to perform database operations, migrations, or integrations with other systems.

Database Versions Table

The bosa_auth_database_versions table tracks migration versions for the authentication database:

Column	Type	Description
id	Integer	Primary key
version	Integer	Migration version number
description	String	Description of the migration
applied_at	DateTime	When the migration was applied

Clients Table

Store clients registered

Column	Type	Description
id	UUID	Primary key
name	String	Client name
secret	String	Hashed client secret (API key)
is_active	Boolean	Whether the client is active (default: true)
created_at	DateTime	Creation timestamp

Users Table

The users table stores user accounts associated with clients:

Column	Type	Description
id	UUID	Primary key
client_id	UUID	Foreign key to clients table
identifier	String	User identifier (typically email)
secret	String	Hashed user secret (password) using Argon2
is_active	Boolean	Whether the user is active (default: true)
created_at	DateTime	Creation timestamp

Tokens Table

The tokens table keeps track of issued authentication tokens:

Column	Type	Description
id	UUID	Primary key
user_id	UUID	Foreign key to users table
client_id	UUID	Foreign key to clients table
is_revoked	Boolean	Whether the token has been revoked

Third-Party Integrations Table

The third_party_integrations table stores connections to external services:

Column	Type	Description
id	UUID	Primary key
client_id	UUID	Foreign key to clients table
user_id	UUID	Foreign key to users table
connector	String	Name of the integration (e.g., "github")
auth_string	String	Authentication credentials for the service
user_identifier	String	User identifier in the third-party service
auth_scopes	List[String]	Scopes/permissions granted to the integration