This document provides a detailed description of the database schema for the CityU Vet Sim project, as defined in src/server/db/schema.ts using Drizzle ORM.
user Tablesession Tableaccount Tableverification Tableposts Table (Example)simulationNotes TableThe database schema is defined in src/server/db/schema.ts using Drizzle ORM's schema declaration API. The createTable function is used to automatically prefix table names, supporting multi-project schema usage.
// src/server/db/schema.ts
export const createTable = pgTableCreator((name) => `cityu-vet-sim_${name}`);
user TableStores user information for the application.
id: Primary key, text type.name: User's full name, not null.email: User's email, not null, unique.emailVerified: Boolean indicating if email is verified, not null.image: URL to user's profile image.createdAt: Timestamp of user creation, not null.updatedAt: Timestamp of last update, not null.personality: Text field to store user's personality profile, with a default empty string.export const user = createTable("user", {
id: text("id").primaryKey(),
name: text("name").notNull(),
email: text("email").notNull().unique(),
emailVerified: boolean("email_verified").notNull(),
image: text("image"),
createdAt: timestamp("created_at").notNull(),
updatedAt: timestamp("updated_at").notNull(),
personality: text("personality").default(""),
});
session TableManages user sessions, linked to the user table.
id: Primary key, text type.expiresAt: Timestamp when the session expires, not null.token: Unique session token, not null, unique.createdAt: Timestamp of session creation, not null.updatedAt: Timestamp of last update, not null.ipAddress: IP address from which the session originated.userAgent: User agent string of the client.userId: Foreign key referencing user.id, not null, with cascade on delete.export const session = createTable("session", {
id: text("id").primaryKey(),
expiresAt: timestamp("expires_at").notNull(),
token: text("token").notNull().unique(),
createdAt: timestamp("created_at").notNull(),
updatedAt: timestamp("updated_at").notNull(),
ipAddress: text("ip_address"),
userAgent: text("user_agent"),
userId: text("user_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
});
account TableStores information about user accounts, especially for external providers or password-based logins, linked to the user table.
id: Primary key, text type.accountId: Unique identifier for the account, not null.providerId: Identifier for the authentication provider, not null.userId: Foreign key referencing user.id, not null, with cascade on delete.accessToken, refreshToken, idToken: OAuth tokens.accessTokenExpiresAt, refreshTokenExpiresAt: Expiration timestamps for tokens.scope: OAuth scope.password: Hashed password for email/password authentication.createdAt: Timestamp of account creation, not null.updatedAt: Timestamp of last update, not null.export const account = createTable("account", {
id: text("id").primaryKey(),
accountId: text("account_id").notNull(),
providerId: text("provider_id").notNull(),
userId: text("user_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
accessToken: text("access_token"),
refreshToken: text("refresh_token"),
idToken: text("id_token"),
accessTokenExpiresAt: timestamp("access_token_expires_at"),
refreshTokenExpiresAt: timestamp("refresh_token_expires_at"),
scope: text("scope"),
password: text("password"),
createdAt: timestamp("created_at").notNull(),
updatedAt: timestamp("updated_at").notNull(),
});
verification TableUsed for email verification tokens or other one-time verification processes.
id: Primary key, text type.identifier: Identifier (e.g., email address), not null.value: The verification token or code, not null.expiresAt: Timestamp when the verification expires, not null.createdAt, updatedAt: Timestamps for creation and update.export const verification = createTable("verification", {
id: text("id").primaryKey(),
identifier: text("identifier").notNull(),
value: text("value").notNull(),
expiresAt: timestamp("expires_at").notNull(),
createdAt: timestamp("created_at"),
updatedAt: timestamp("updated_at"),
});
posts Table (Example)An example table, likely a remnant from the T3 Stack boilerplate, used for basic data storage and demonstration of Drizzle.
id: Primary key, integer, auto-generated.name: Text field, length 256, indexed.createdAt: Timestamp with timezone, default current timestamp, not null.updatedAt: Timestamp with timezone, updated on change.export const posts = createTable(
"post",
(d) => ({
id: d.integer().primaryKey().generatedByDefaultAsIdentity(),
name: d.varchar({ length: 256 }),
createdAt: d
.timestamp({ withTimezone: true })
.default(sql`CURRENT_TIMESTAMP`)
.notNull(),
updatedAt: d.timestamp({ withTimezone: true }).$onUpdate(() => new Date()),
}),
(t) => [index("name_idx").on(t.name)],
);
simulationNotes TableCrucial for the veterinary simulation, this table stores notes and performance data related to student simulations.
id: Primary key, text type.userId: Foreign key referencing user.id, not null, with cascade on delete.skillId: Identifier for the communication skill being practiced, not null.caseId: Identifier for the specific veterinary case, not null.notes: Text field to store student notes or performance feedback, default empty string, not null.createdAt: Timestamp of note creation, default current timestamp, not null.updatedAt: Timestamp of last update, updated on change.export const simulationNotes = createTable("simulation_notes", {
id: text("id").primaryKey(),
userId: text("user_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
skillId: text("skill_id").notNull(),
caseId: text("case_id").notNull(),
notes: text("notes").notNull().default(""),
createdAt: timestamp("created_at")
.default(sql`CURRENT_TIMESTAMP`)
.notNull(),
updatedAt: timestamp("updated_at").$onUpdate(() => new Date()),
});