Database Information

Structure

Column Ordering Conventions

1. Identifiers first

   • id (primary key)
   • foreign keys (user_id, project_id)

2. Core business fields

   • fields that define the record itself

3. Metadata / bookkeeping

   • created_at
   • updated_at
   • deleted_at
   • version
   • audit columns

4. Rarely used or optional fields last

Why: cleaner to scan, PK/FK grouping improves clarity, aligns with common tool-generated structures.

Drizzle ORM

Drizzle is the chosen ORM for this project. It seems a good transition from the sql code from access, using the sql like syntax. Also good performance and zod integration.

Structure

drizzle

The drizzle folder is outside of source, at root level. It is used for migrations and type checking during development, but not during runtime.

db

The drizzle inastance is created in src/db.

Migrations

For an existing database (initial):

• run db:dev:push, will fail at creating relations to users
• run db:seed, adds the initial users (including me at the proper id)
• run db:dev:push, now the relations are created
• add emailVerification date to users (optional)

Utilities

// /utils/db.utils.ts

function transformUserId(userId: unknown): UserIdType

This function infers the type directly from the users.id field in the db. Use for every call that needs a user id and it will gurantee the type.