Adding New Database Models

Relevant source files

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

• packages/db/src/schema/index.ts
• packages/db/package.json
• packages/db/drizzle/0000_normal_the_order.sql
• packages/db/src/seed.ts
• turbo.json
• packages/db/drizzle/meta/0000_snapshot.json

Adding new database models to the Subito project involves a structured workflow using Drizzle ORM to define schemas, generate migrations, and seed data. The project utilizes a PostgreSQL database with strongly typed schemas defined in TypeScript.

The database architecture is centralized within the @subito/db package, which exports the schema definitions for use in other services such as the API. The process ensures type safety across the monorepo by linking the database types directly to the application logic.
Sources: packages/db/package.json:20-23, apps/api/package.json:22

Schema Definition Workflow

Database models are defined as TypeScript objects using the pgTable function from drizzle-orm/pg-core. This approach allows developers to define columns, data types, constraints, and relationships in a single location.

Workflow Steps

The following diagram illustrates the lifecycle of adding a new database model from initial definition to deployment.

The diagram shows the sequential steps required to transition a TypeScript schema into a live database table.
Sources: packages/db/package.json:10-14, turbo.json:44-53

Drizzle Commands and Tooling

The @subito/db package provides several scripts to manage the database lifecycle. These are typically executed via pnpm or npm.

Command	Description
db:generate	Scans schema files and generates SQL migration files in the drizzle/ directory.
db:migrate	Executes pending SQL migrations against the configured database.
db:push	Directly pushes schema changes to the database (useful for rapid development).
db:studio	Launches a GUI (Drizzle Studio) to explore and manipulate data.
db:seed	Populates the database with initial or test data using tsx.

Sources: packages/db/package.json:10-14, turbo.json:44-53

Defining Core Entities

Entities are defined with specific PostgreSQL types and constraints. The project uses uuid for primary keys and establishes foreign key relationships using the .references() method.

Example: User and Partner Relationship

The system enforces strict relationships between core entities. For instance, a partner must always be linked to a user record.

The ER diagram demonstrates the one-to-one relationship between users and partners.
Sources: packages/db/drizzle/0000_normal_the_order.sql:35-77, packages/db/drizzle/meta/0000_snapshot.json:173-300

Technical Constraints Table

Commonly used field types and their constraints found in the project:

Type	Project Implementation	Default / Constraint
Primary Key	uuid	gen_random_uuid()
Timestamps	timestamp	now()
Enumerations	pgEnum	Custom defined types (e.g., user_role)
Financials	numeric(12, 2)	Precision 12, Scale 2
Metadata	jsonb	Flexible storage for objects

Sources: packages/db/drizzle/0000_normal_the_order.sql:1-33, packages/db/drizzle/meta/0000_snapshot.json:40-100

Seeding New Models

After creating a new model, it should be added to the seeding script to facilitate local development. The seed.ts file uses drizzle-orm to insert mock data.

Seeding Logic

The seeding script follows a specific order to satisfy foreign key constraints.

The flow ensures that parent records exist before child records that reference them are created.
Sources: packages/db/src/seed.ts:60-350

Seeding Example Snippet

Developers use the db.insert() method within src/seed.ts.

const newUser = await db
  .insert(schema.users)
  .values({
    phone: "+919000000001",
    role: "admin",
    isActive: true,
  })
  .returning();

Sources: packages/db/src/seed.ts:65-79

Configuration and Environment

Database connectivity is managed through environment variables defined in the root of the project and handled by turbo.json.

Variable	Description
DATABASE_URL	The full PostgreSQL connection string.
DB_POOL_MAX	Maximum number of concurrent connections.
DB_POOL_IDLE_TIMEOUT_MS	Timeout for idle connections in the pool.

Sources: turbo.json:4-8, packages/db/src/seed.ts:6-7

Conclusion

Adding new database models requires updating the TypeScript schema, generating and running migrations, and optionally updating the seed script. This structured approach maintains synchronization between the application code and the PostgreSQL database schema while ensuring type safety across the Subito services.