Database Schema Definition

Relevant source files

The following files were used as context for generating this wiki page:

• packages/db/drizzle/meta/0000_snapshot.json
• apps/api/src/lib/auth.ts
• packages/db/package.json
• README.md
• packages/db/src/index.ts
• packages/contract/src/schemas/user.ts

The database schema for this project is managed within the packages/db workspace package. It utilizes Drizzle ORM to define and interact with a PostgreSQL database. The schema is primarily designed to support the authentication system provided by the better-auth library, but is structured to be extensible for future application features.

The schema definitions are consumed by the API server (apps/api) to configure the database adapter for authentication and will be used for any direct data manipulation. Drizzle Kit is used for managing database migrations and keeping the schema in sync with the database state.

Sources: README.md, packages/db/package.json, apps/api/src/lib/auth.ts

Core Technologies

The database system is built on the following core technologies:

Technology	Description	Source
Drizzle ORM	A TypeScript ORM for SQL databases used to define the schema and interact with the database.	[README.md:30](https://github.com/gthomas08/contract-based-monorepo/blob/HEAD/README.md#L30)
PostgreSQL	The underlying relational database system.	[README.md:31](https://github.com/gthomas08/contract-based-monorepo/blob/HEAD/README.md#L31)
Drizzle Kit	A CLI tool used for generating and pushing schema changes to the database.	[packages/db/package.json:13-14](https://github.com/gthomas08/contract-based-monorepo/blob/HEAD/packages/db/package.json#L13-L14)

Sources: README.md, packages/db/package.json

Schema Architecture

The database schema is defined programmatically using Drizzle ORM's syntax. The primary tables are related to user authentication. The schema definitions are centralized in the @contract-based-monorepo/db package and exported for consumption in other parts of the monorepo, such as the API.

This diagram illustrates the relationship between the schema definition and its consumption by the authentication library in the API.

Schema Consumption

The API server consumes the exported schema to configure the better-auth library. It uses a drizzleAdapter which maps the Drizzle schema objects to the tables required by the authentication system.

// apps/api/src/lib/auth.ts:2-17
import { db, schema } from "@contract-based-monorepo/db";
import { betterAuth } from "better-auth";
import { drizzleAdapter } from "better-auth/adapters/drizzle";
import { openAPI } from "better-auth/plugins";

export const auth = betterAuth({
  // ...
  database: drizzleAdapter(db, {
    provider: "pg",
    schema: {
      user: schema.user,
      session: schema.session,
      account: schema.account,
      verification: schema.verification,
    },
  }),
});

Sources: apps/api/src/lib/auth.ts:2-17

Entity Relationship Diagram

The following diagram shows the relationships between the core authentication tables defined in the schema. The user table is central, with session and account tables holding one-to-many relationships to it.

Table Definitions

The database schema is composed of four main tables to handle authentication. The structure is based on the drizzle/meta/0000_snapshot.json file, which is a snapshot of the database state generated by Drizzle Kit.

Sources: packages/db/drizzle/meta/0000_snapshot.json

user Table

Stores user profile information.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, NOT NULL	Unique identifier for the user.
name	text	NOT NULL	The user's name.
email	text	UNIQUE, NOT NULL	The user's email address.
email_verified	boolean	NOT NULL	Flag indicating if the email has been verified.
image	text	NULL	URL to the user's profile image.
created_at	timestamp	NOT NULL	Timestamp of when the user was created.
updated_at	timestamp	NOT NULL	Timestamp of the last update to the user record.

Sources: packages/db/drizzle/meta/0000_snapshot.json:173-219

session Table

Stores user session information for tracking authenticated states.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, NOT NULL	Unique identifier for the session.
expires_at	timestamp	NOT NULL	Timestamp when the session expires.
token	text	UNIQUE, NOT NULL	The session token.
user_id	text	FOREIGN KEY (user.id), NOT NULL	The ID of the user this session belongs to.
ip_address	text	NULL	IP address from which the session was created.
user_agent	text	NULL	User agent string of the client.
created_at	timestamp	NOT NULL	Timestamp of when the session was created.
updated_at	timestamp	NOT NULL	Timestamp of the last update to the session.

Sources: packages/db/drizzle/meta/0000_snapshot.json:96-148

account Table

Stores information for third-party authentication providers (e.g., OAuth) linked to a user.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, NOT NULL	Unique identifier for the account link.
account_id	text	NOT NULL	The ID of the account from the provider.
provider_id	text	NOT NULL	The identifier for the auth provider (e.g., 'google').
user_id	text	FOREIGN KEY (user.id), NOT NULL	The ID of the local user this account is linked to.
access_token	text	NULL	OAuth access token.
refresh_token	text	NULL	OAuth refresh token.
password	text	NULL	Hashed password for email/password accounts.
created_at	timestamp	NOT NULL	Timestamp of when the account link was created.
updated_at	timestamp	NOT NULL	Timestamp of the last update.

Sources: packages/db/drizzle/meta/0000_snapshot.json:11-94

verification Table

Stores tokens for processes like email verification or password resets.

Column	Type	Constraints	Description
id	text	PRIMARY KEY, NOT NULL	Unique identifier for the verification record.
identifier	text	NOT NULL	The identifier being verified (e.g., email address).
value	text	NOT NULL	The verification token or code.
expires_at	timestamp	NOT NULL	Timestamp when the verification token expires.
created_at	timestamp	NULL	Timestamp of creation.
updated_at	timestamp	NULL	Timestamp of last update.

Sources: packages/db/drizzle/meta/0000_snapshot.json:221-255