# Setting Up Separate Auth Database

This guide explains how to set up a separate database for authentication and pending identifications, so you don't need to write to the read-only `punimtag` database.

## Why a Separate Database?

The `punimtag` database is read-only, but we need to store:
- User accounts (for login/authentication)
- Pending identifications (face identifications waiting for admin approval)

By using a separate database (`punimtag_auth`), we can:
- ✅ Keep the punimtag database completely read-only
- ✅ Store user data and identifications separately
- ✅ Maintain data integrity without foreign key constraints across databases

## Setup Steps

### 1. Create the Auth Database

Run the SQL script as a PostgreSQL superuser:

```bash
psql -U postgres -f setup-auth-database.sql
```

Or connect to PostgreSQL and run manually:

```sql
-- Create the database
CREATE DATABASE punimtag_auth;

-- Connect to it
\c punimtag_auth

-- Then run the rest of setup-auth-database.sql
```

### 2. Configure Environment Variables

Add `DATABASE_URL_AUTH` to your `.env` file:

```bash
DATABASE_URL_AUTH="postgresql://username:password@localhost:5432/punimtag_auth"
```

**Note:** You can use the same PostgreSQL user that has access to the punimtag database, or create a separate user specifically for the auth database.

### 3. Generate Prisma Clients

Generate both Prisma clients:

```bash
# Generate main client (for punimtag database)
npm run prisma:generate

# Generate auth client (for punimtag_auth database)
npm run prisma:generate:auth

# Or generate both at once:
npm run prisma:generate:all
```

### 4. Create Admin User

After the database is set up and Prisma clients are generated, create an admin user:

```bash
npx tsx scripts/create-admin-user.ts
```

This will create an admin user with:
- **Email:** admin@admin.com
- **Password:** admin
- **Role:** Admin (can approve identifications)

### 5. Verify Setup

1. **Check tables exist:**
   ```sql
   \c punimtag_auth
   \dt
   ```
   You should see `users` and `pending_identifications` tables.

2. **Check admin user:**
   ```sql
   SELECT email, name, is_admin FROM users WHERE email = 'admin@admin.com';
   ```

3. **Test registration:**
   - Go to http://localhost:3001/register
   - Create a new user account
   - Verify it appears in the `punimtag_auth` database

4. **Test login:**
   - Go to http://localhost:3001/login
   - Login with admin@admin.com / admin

## Database Structure

### `punimtag_auth` Database

- **users** - User accounts for authentication
- **pending_identifications** - Face identifications pending admin approval

### `punimtag` Database (Read-Only)

- **photos** - Photo metadata
- **faces** - Detected faces in photos
- **people** - Identified people
- **tags** - Photo tags
- etc.

## Important Notes

### Foreign Key Constraints

The `pending_identifications.face_id` field references `faces.id` in the `punimtag` database, but we **cannot use a foreign key constraint** across databases. The application validates that faces exist when creating pending identifications.

### Face ID Validation

When a user identifies a face, the application:
1. Validates the `faceId` exists in the `punimtag` database (read-only check)
2. Stores the identification in `punimtag_auth.pending_identifications` (write operation)

This ensures data integrity without requiring write access to the punimtag database.

## Troubleshooting

### "Cannot find module '../node_modules/.prisma/client-auth'"

Make sure you've generated the auth Prisma client:
```bash
npm run prisma:generate:auth
```

### "relation 'users' does not exist"

Make sure you've created the auth database and run the setup script:
```bash
psql -U postgres -f setup-auth-database.sql
```

### "permission denied for table users"

Make sure your database user has the necessary permissions. You can grant them with:
```sql
GRANT ALL PRIVILEGES ON DATABASE punimtag_auth TO your_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_user;
```

### "DATABASE_URL_AUTH is not defined"

Make sure you've added `DATABASE_URL_AUTH` to your `.env` file.

## Migration from Old Setup

If you previously had `users` and `pending_identifications` tables in the `punimtag` database:

1. **Export existing data** (if any):
   ```sql
   \c punimtag
   \copy users TO 'users_backup.csv' CSV HEADER;
   \copy pending_identifications TO 'pending_identifications_backup.csv' CSV HEADER;
   ```

2. **Create the new auth database** (follow steps above)

3. **Import data** (if needed):
   ```sql
   \c punimtag_auth
   \copy users FROM 'users_backup.csv' CSV HEADER;
   \copy pending_identifications FROM 'pending_identifications_backup.csv' CSV HEADER;
   ```

4. **Update your `.env` file** with `DATABASE_URL_AUTH`

5. **Regenerate Prisma clients** and restart your application

