Cursor rule

1---
2description: 
3globs: 
4alwaysApply: true
5---
6## Database
7 
8Find the schema here: [schema.sql](mdc:packages/db/schema.sql)
9 
10### Commands
11 
12Run from the root directory:
13 
14- `DATABASE_URL=postgres://localhost:5432/monorepo-scaffold pnpm --filter @app/db db:migrate:create <name>` - Create a new database migration template
15- `DATABASE_URL=postgres://localhost:5432/monorepo-scaffold pnpm --filter @app/db db:migrate` - Run database migrations
16- `DATABASE_URL=postgres://localhost:5432/monorepo-scaffold pnpm --filter @app/db db:migrate:down` - Rollback database migrations
17- `DATABASE_URL=postgres://localhost:5432/monorepo-scaffold pnpm --filter @app/db db:reset` - Reset database
18- `DATABASE_URL=postgres://localhost:5432/monorepo-scaffold pnpm --filter @app/db db:seed` - Seed database with initial data (default tenant and permissions)
19 
20### Create a new migration
21 
221. Run `db:migrate:create <myname>`
232. Edit the file it creates
243. Run `db:migrate`
25 
26Do not generate types.gen.ts files yourself - they'll be auto generated.
27 
28### Using Kysely Type Helpers
29 
30When working with database types in TypeScript, always use Kysely's type helper utilities for proper type safety:
31 
321. **Selectable<T>** - Use when selecting/reading data from the database:
33```typescript
34   import type { Selectable } from 'kysely'
35   import type { User } from '@app/db/types'
36   
37   // Function that returns user data from DB
38   async function getUser(id: string): Promise<Selectable<User> | null> {
39     return await db.selectFrom('users').where('id', '=', id).selectAll().executeTakeFirst()
40   }
41   
42   // Component props
43   interface UserProfileProps {
44     user: Selectable<User>
45   }
46```
47 
482. **Insertable<T>** - Use when inserting data into the database:
49```typescript
50   import type { Insertable } from 'kysely'
51   import type { Project } from '@app/db/types'
52   
53   // Function parameters for creating records
54   async function createProject(data: Insertable<Project>): Promise<Selectable<Project>> {
55     return await db.insertInto('projects').values(data).returningAll().executeTakeFirstOrThrow()
56   }
57   
58   // Partial inserts with required fields
59   async function createUser(data: Partial<Insertable<User>> & { email: string; tenantId: string }) {
60     return await db.insertInto('users').values({
61       emailVerified: false,
62       status: 'active',
63       ...data,
64     }).returningAll().executeTakeFirstOrThrow()
65   }
66```
67 
683. **Updateable<T>** - Use when updating records in the database:
69```typescript
70   import type { Updateable } from 'kysely'
71   import type { Task } from '@app/db/types'
72   
73   async function updateTask(id: string, data: Updateable<Task>): Promise<Selectable<Task>> {
74     return await db.updateTable('tasks')
75       .set(data)
76       .where('id', '=', id)
77       .returningAll()
78       .executeTakeFirstOrThrow()
79   }
80```
81 
824. **General Guidelines**:
83   - Never use raw table types directly (e.g., `User`, `Project`) for function parameters or return types
84   - Always wrap with appropriate helper based on the operation
85   - For partial updates/inserts, combine `Partial<Insertable<T>>` or `Partial<Updateable<T>>` with required fields
86   - When passing database records between functions/components, use `Selectable<T>`
87   - We use underscore naming for table columns in the database, but Kysely always maps these to camelCase names. So from within TypeScript, you will need to use camelCase when interacting with a column, say, using it in a `where` condition.
88   - **Timestamp columns are typed as `Date`**: Columns like `TIMESTAMP` or `TIMESTAMPTZ` are automatically returned as JavaScript `Date` objects (not strings). You can safely call `getTime()` etc. without parsing.
89 
90### Handling JSONB Types
91 
92When working with JSONB columns in the database, you need to specify proper TypeScript types to ensure type safety. Follow these steps:
93 
941. **Create column type definitions** in `packages/db/src/column-types.ts`:
95 
96```typescript
97export type ProjectStage = {
98  name: string
99  description: string
100}
101 
102// Includes RawBuilder to allow for JSONB
103export type ProjectStageColumnType = ColumnType<
104  ProjectStage[] | null,
105  ProjectStage[] | null | RawBuilder<ProjectStage[]>,
106  ProjectStage[] | null | RawBuilder<ProjectStage[]>
107>
108```
109 
1102. **Reference the type in Kysely codegen configuration** in `packages/db/.kysely-codegenrc.yaml`:
111 
112```yaml
113serializer-properties:
114  'public.projects.stages': 'import("./src/column-types").ProjectStageColumnType | null'
115```
116 
1173. **Use the type in your migrations**:
118 
119```sql
120ALTER TABLE projects ADD COLUMN tools_config JSONB;
121```
122 
123The types will be automatically generated and available through `@app/db/types` after running the migration and type generation.
124 
125**Important**: Always define explicit TypeScript interfaces for JSONB columns rather than using generic types like `any` or `unknown`. This ensures type safety throughout the application.