Database Schema
Relevant source files
The following files were used as context for generating this wiki page:The Adfluens application uses a PostgreSQL database, with the schema managed by Drizzle ORM. The database is designed to support the core functionality of analyzing YouTube channels, user authentication, and a multi-tenant system for organizations and teams. The schema is divided into three main areas: core application data (channels), user and session management, and a multi-tenancy structure for collaborative workspaces.
The authentication and multi-tenancy aspects appear to be heavily influenced or managed by the better-auth library, which provides a structured way to handle users, organizations, and roles. The schema definitions are generated and migrated using drizzle-kit.
Sources: packages/db/package.json, packages/auth/package.json, packages/db/drizzle/meta/0002_snapshot.json
Entity Relationship Diagram
This diagram shows the overall structure and relationships between the tables in the database.
This diagram is a representation of the relationships defined in the Drizzle snapshot.
Sources: packages/db/drizzle/meta/0002_snapshot.json
Core Application Schema
This section describes tables that are central to the Adfluens application's primary purpose of analyzing YouTube channels.
channels
This table stores information about the YouTube channels being tracked and analyzed.
| Field | Type | Constraints | Description |
|---|---|---|---|
id | varchar(255) | PRIMARY KEY, NOT NULL | The unique identifier for the channel (YouTube's ID). |
title | varchar(255) | NOT NULL | The title of the YouTube channel. |
description | text | NULLABLE | The channel's description. |
thumbnail_url | text | NULLABLE | URL for the channel's thumbnail image. |
subscriber_count | integer | NULLABLE | The number of subscribers. |
video_count | integer | NULLABLE | The total number of videos on the channel. |
view_count | integer | NULLABLE | The total number of views for the channel. |
created_at | timestamp | NOT NULL, DEFAULT now() | Timestamp of when the record was created. |
updated_at | timestamp | NOT NULL, DEFAULT now() | Timestamp of when the record was last updated. |
Sources: packages/db/drizzle/meta/0000_snapshot.json, packages/db/src/schema/index.ts
Authentication & User Management
These tables form the foundation for user authentication, identity, and session management, provided by the better-auth library.
Sources: packages/auth/package.json, packages/db/drizzle/meta/0002_snapshot.json
user
Stores the primary profile information for each user.
| Field | Type | Constraints | Description |
|---|---|---|---|
id | varchar(36) | PRIMARY KEY, NOT NULL | Unique identifier for the user. |
name | text | NOT NULL | The user's full name. |
email | varchar(255) | UNIQUE, NOT NULL | The user's unique email address. |
email_verified | boolean | NOT NULL, DEFAULT false | Flag indicating if the email has been verified. |
image | text | NULLABLE | URL for the user's profile picture. |
created_at | timestamp | NOT NULL, DEFAULT now() | Timestamp of user creation. |
updated_at | timestamp | NOT NULL, DEFAULT now() | Timestamp of last update. |
account
Links external provider accounts (e.g., Google, GitHub) to a user in the user table.
| Field | Type | Constraints | Description |
|---|---|---|---|
id | varchar(36) | PRIMARY KEY, NOT NULL | Unique identifier for the account link. |
account_id | text | NOT NULL | The ID from the external provider. |
provider_id | text | NOT NULL | The name of the provider (e.g., 'google'). |
user_id | varchar(36) | FOREIGN KEY (user.id) | Links to the user in the user table. |
access_token | text | NULLABLE | OAuth access token. |
refresh_token | text | NULLABLE | OAuth refresh token. |
access_token_expires_at | timestamp | NULLABLE | Expiration date for the access token. |
session
Stores active user sessions for maintaining login state.
| Field | Type | Constraints | Description |
|---|---|---|---|
id | varchar(36) | PRIMARY KEY, NOT NULL | Unique identifier for the session. |
token | text | UNIQUE, NOT NULL | The session token. |
expires_at | timestamp | NOT NULL | When the session expires. |
user_id | varchar(36) | FOREIGN KEY (user.id) | The user associated with this session. |
verification
Stores tokens for processes like email verification or password resets.
| Field | Type | Constraints | Description |
|---|---|---|---|
id | varchar(36) | PRIMARY KEY, NOT NULL | Unique identifier for the verification record. |
identifier | text | NOT NULL | The identifier being verified (e.g., email). |
value | text | NOT NULL | The verification token or code. |
expires_at | timestamp | NOT NULL | When the token expires. |
Multi-Tenancy & Authorization
This set of tables enables multi-tenancy, allowing users to be part of organizations and teams with specific roles.
Sources: packages/db/drizzle/0002_amused_wasp.sql, packages/db/drizzle/meta/0002_snapshot.json
organization
Represents a top-level workspace or company.
| Field | Type | Constraints | Description |
|---|---|---|---|
id | varchar(36) | PRIMARY KEY, NOT NULL | Unique identifier for the organization. |
name | varchar(255) | NOT NULL | The organization's name. |
slug | varchar(255) | UNIQUE, NOT NULL | A URL-friendly slug for the organization. |
logo | text | NULLABLE | URL for the organization's logo. |
created_by | varchar(36) | FOREIGN KEY (user.id) | The user who created the organization. |
member
A join table that links users to organizations and defines their role within that organization.
| Field | Type | Constraints | Description |
|---|---|---|---|
id | varchar(36) | PRIMARY KEY, NOT NULL | Unique identifier for the membership. |
organization_id | varchar(36) | FOREIGN KEY (organization.id), UNIQUE | The organization the user is a member of. |
user_id | varchar(36) | FOREIGN KEY (user.id), UNIQUE | The user who is a member. |
role | varchar(50) | NOT NULL | The user's role (e.g., 'admin', 'member'). |
invitation
Stores pending invitations for users to join an organization.
| Field | Type | Constraints | Description |
|---|---|---|---|
id | varchar(36) | PRIMARY KEY, NOT NULL | Unique identifier for the invitation. |
organization_id | varchar(36) | FOREIGN KEY (organization.id) | The organization the user is invited to. |
email | varchar(255) | NOT NULL | The email address of the invitee. |
role | varchar(50) | NOT NULL | The role the user will have upon accepting. |
token | varchar(255) | UNIQUE, NOT NULL | The unique token for this invitation. |
status | varchar(50) | NOT NULL, DEFAULT 'pending' | The status of the invitation. |
expires_at | timestamp | NOT NULL | When the invitation expires. |
team
Represents a sub-group within an organization.
| Field | Type | Constraints | Description |
|---|---|---|---|
id | varchar(36) | PRIMARY KEY, NOT NULL | Unique identifier for the team. |
name | varchar(255) | NOT NULL | The team's name. |
slug | varchar(255) | NOT NULL | A URL-friendly slug for the team. |
organization_id | varchar(36) | FOREIGN KEY (organization.id) | The organization this team belongs to. |
team_member
A join table linking users to teams.
| Field | Type | Constraints | Description |
|---|---|---|---|
id | varchar(36) | PRIMARY KEY, NOT NULL | Unique identifier for the team membership. |
team_id | varchar(36) | FOREIGN KEY (team.id), UNIQUE | The team the user is a member of. |
user_id | varchar(36) | FOREIGN KEY (user.id), UNIQUE | The user who is a member. |
role | varchar(50) | NOT NULL | The user's role within the team. |