// INVALID - What Drizzle Kit generated
id: text().default(gen_descriptor_id(\'f-member\'::text)).notNull()

// VALID - What it should be
id: text().default(sql`gen_descriptor_id('f-member'::text)`).notNull()
```typescript

This resulted in **389+ linter errors** and prevented the application from compiling.

## Solution

We've implemented a **minimal working schema** approach:

### Current Setup

- **`src/schema.ts`** - Minimal working schema with core tables only (users, companies, invoices, customers)
- **`src/schema.backup.ts`** - Full introspected schema (with syntax errors, for reference)
- **`migrations/schema.ts`** - Another backup of the introspected schema

### Core Tables Included

The minimal schema includes these essential tables:
- `users`
- `companies`
- `invoices`
- `customers`

## How to Add More Tables

You have **three options** to add more tables to your schema:

### Option 1: Add Tables Manually (Recommended)

1. **View your database structure** using Drizzle Studio:
   ```bash
   pnpm db:studio
   ```typescript

2. **Define the table** in `src/schema.ts` following this pattern:
   ```typescript
   export const myTable = pgTable("my_table", {
     id: uuid("id").primaryKey().defaultRandom(),
     createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
     name: text("name"),
     // ... add more columns
   });
   ```typescript

3. **Reference the backup** - Check `src/schema.backup.ts` for table definitions (but fix any syntax errors)

### Option 2: Selective Copy from Backup

1. Open `src/schema.backup.ts`
2. Find the table you need
3. Copy it to `src/schema.ts`
4. **Fix any syntax errors**:
   - Replace `gen_descriptor_id(\'xxx\'::text)` with `sql\`gen_descriptor_id('xxx'::text)\``
   - Replace unterminated strings with proper SQL template literals

### Option 3: Query Database Directly

Use SQL to see table structure:

```typescript
import { db } from './src/db';
import { sql } from 'drizzle-orm';

// Get table columns
const result = await db.execute(sql`
  SELECT column_name, data_type, is_nullable, column_default
  FROM information_schema.columns
  WHERE table_name = 'your_table_name'
  AND table_schema = 'public'
  ORDER BY ordinal_position;
`);
```typescript

Then manually create the Drizzle schema definition.

## Common Table Patterns

### Basic Table with UUID Primary Key
```typescript
export const myTable = pgTable("my_table", {
  id: uuid("id").primaryKey().defaultRandom(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
  name: text("name").notNull(),
  description: text("description"),
});
```typescript

### Table with Foreign Key
```typescript
export const orders = pgTable("orders", {
  id: uuid("id").primaryKey().defaultRandom(),
  customerId: uuid("customer_id").references(() => customers.id),
  amount: numeric("amount"),
});
```typescript

### Table with Enum
```typescript
export const myStatus = pgEnum("my_status", ['active', 'inactive', 'pending']);

export const myTable = pgTable("my_table", {
  id: uuid("id").primaryKey(),
  status: myStatus("status").default('pending'),
});
```typescript

### Table with JSON
```typescript
export const myTable = pgTable("my_table", {
  id: uuid("id").primaryKey(),
  metadata: jsonb("metadata"),
  settings: json("settings"),
});
```typescript

### Table with Indexes
```typescript
export const myTable = pgTable("my_table", {
  id: uuid("id").primaryKey(),
  email: text("email").notNull(),
  companyId: uuid("company_id"),
}, (table) => ({
  emailIdx: index("my_table_email_idx").on(table.email),
  companyIdx: index("my_table_company_idx").on(table.companyId),
}));
```typescript

## Known Syntax Errors in Backup

If copying from `src/schema.backup.ts`, watch out for:

### 1. Escaped Quotes in Functions
```typescript
// WRONG (in backup)
.default(gen_descriptor_id(\'f-member\'::text))

// CORRECT
.default(sql`gen_descriptor_id('f-member'::text)`)
```typescript

### 2. Unterminated String Literals
```typescript
// WRONG
.default(gen_random_uuid())

// CORRECT
.defaultRandom()
// OR
.default(sql`gen_random_uuid()`)
```typescript

### 3. Complex SQL Expressions
```typescript
// WRONG
.default(sql`(EXTRACT(epoch FROM (now() AT TIME ZONE 'utc'`)

// CORRECT
.default(sql`EXTRACT(epoch FROM (now() AT TIME ZONE 'utc'))::bigint`)
```typescript

## Syncing Schema to API

**IMPORTANT**: After modifying the schema, you must sync it to the API service:

```bash
pnpm sync:schema
```typescript

This copies `src/schema.ts` to `apps/api/src/schema.ts`. The API service has strict TypeScript module resolution and requires a local copy of the schema.

For more details, see `SCHEMA_SYNC.md` in the project root.

## Testing Your Schema

After adding tables:

1. **Check for errors**:
   ```bash
   # TypeScript will show any errors
   pnpm tsc --noEmit
   ```typescript

2. **Test with Drizzle Studio**:
   ```bash
   pnpm db:studio
   # Browse your tables visually
   ```typescript

3. **Test queries**:
   ```typescript
   import { db } from './src/db';
   import * as schema from './src/schema';
   
   const users = await db.select().from(schema.users).limit(10);
   console.log(users);
   ```typescript

## Alternative: Use Raw SQL

If you encounter issues with complex tables, you can always use raw SQL:

```typescript
import { db } from './src/db';
import { sql } from 'drizzle-orm';

const result = await db.execute(sql`
  SELECT * FROM complex_table WHERE id = ${id}
`);
```typescript

## Reporting Issues

If you find the same issue with Drizzle Kit introspection:

1. Check [Drizzle ORM GitHub Issues](https://github.com/drizzle-team/drizzle-orm/issues)
2. Report the bug with:
   - Your Drizzle Kit version (`0.31.5`)
   - Database type (PostgreSQL/Supabase)
   - Example of malformed output
   - Database schema that causes the issue

## Summary

**What Works Now**:
- Core tables (users, companies, invoices, customers)
- Database connection
- Drizzle ORM queries
- Type safety for defined tables

**What Needs Work**:
- Add remaining tables manually as needed
- Fix syntax errors when copying from backup
- Test each table after adding

**Next Steps**:
1. Start using the core tables
2. Add additional tables as you need them
3. Test thoroughly
4. Consider reporting the Drizzle Kit bug

## Files Reference

```files
src/
├── schema.ts
├── schema.backup.ts
├── db.ts
lib/
└── db/
    └── drizzle-service.ts
migrations/
└── schema.ts