Database Schema (Drizzle)
Relevant source files
The following files were used as context for generating this wiki page:The project utilizes Drizzle ORM to manage its database schema, interactions, and migrations. The database is configured for a MySQL dialect and its primary role is to support user authentication and store user profile information. The schema is defined programmatically using TypeScript, providing type safety and a single source of truth for data models.
The core of the database layer consists of the schema definition, a connection manager, and a set of data access functions. The setup is designed to be extensible, with placeholders for adding more tables and relationships as the application's features grow.
Sources: drizzle/schema.ts, server/db.ts, drizzle.config.ts
Configuration
Drizzle is configured via the drizzle.config.ts file. This configuration specifies the dialect, the location of the schema definition file, and the output directory for generated migration files. The database connection URL is sourced from the DATABASE_URL environment variable, which is a requirement for running any Drizzle commands.
Sources: drizzle.config.ts:3-13
| Parameter | Value | Description |
|---|---|---|
schema | ./drizzle/schema.ts | Path to the file containing table definitions. |
out | ./drizzle | Output directory for migration files. |
dialect | mysql | Specifies that the database is MySQL-compatible. |
dbCredentials.url | process.env.DATABASE_URL | The connection string for the database. |
Sources: drizzle.config.ts:10-13
Database Connection
A lazy connection pattern is used to initialize the Drizzle instance. The getDb function in server/db.ts ensures that a database connection is only attempted when it's first needed and if the DATABASE_URL is present. This allows local tooling to run without requiring a live database connection.
Sources: server/db.ts:6-16
This diagram illustrates the lazy database connection logic.
Sources: server/db.ts:7-16
Schema Definition
The database schema is defined in drizzle/schema.ts. Currently, it contains a single table, users, which is central to the application's authentication flow.
Sources: drizzle/schema.ts:6-23
users Table
This table stores information about users who authenticate via the Manus OAuth flow. The openId serves as the unique identifier linking the user to the OAuth provider.
The following diagram shows the entity relationship model for the users table.
Columns
| Column | Type | Constraints | Description |
|---|---|---|---|
id | int | AUTO_INCREMENT, PRIMARY KEY | Surrogate primary key for internal relations. |
openId | varchar(64) | NOT NULL, UNIQUE | Manus OAuth identifier. Used as the primary business key for a user. |
name | text | NULL | The user's full name. |
email | varchar(320) | NULL | The user's email address. |
loginMethod | varchar(64) | NULL | The method used to log in (e.g., 'google', 'email'). |
role | enum('user', 'admin') | NOT NULL, DEFAULT 'user' | User's role within the application. |
createdAt | timestamp | NOT NULL, DEFAULT NOW() | Timestamp of when the user record was created. |
updatedAt | timestamp | NOT NULL, DEFAULT NOW(), ON UPDATE NOW() | Timestamp of the last update to the record. |
lastSignedIn | timestamp | NOT NULL, DEFAULT NOW() | Timestamp of the user's last sign-in. |
Sources: drizzle/schema.ts:10-23, drizzle/meta/0000_snapshot.json:12-69
Relationships
The drizzle/relations.ts file is currently empty. This indicates that no explicit Drizzle relations (like one or many) have been defined between tables yet. The file exists as a placeholder for future schema expansion.
Sources: drizzle/relations.ts:1
Database Operations
The server/db.ts file provides functions to interact with the database. These functions abstract the raw Drizzle queries.
Sources: server/db.ts
| Function | Description |
|---|---|
getDb() | Lazily creates and returns the Drizzle database instance. |
upsertUser(user: InsertUser) | Creates a new user or updates an existing one based on openId. It dynamically builds the update set to only include provided fields. |
getUserByOpenId(openId: string) | Retrieves a single user from the database by their openId. |
Sources: server/db.ts:7-101
upsertUser Flow
The upsertUser function is the primary mechanism for synchronizing user data from the OAuth provider to the local database. It requires an openId and intelligently updates only the fields that are passed in the user object. If the user is the owner (defined by ENV.ownerOpenId), their role is automatically set to admin.
This sequence diagram shows the logic of the upsertUser function.
getUserByOpenId Flow
This function provides a simple way to fetch a user's complete record using their unique openId. It is used extensively during authentication checks.