# Database Subagent Reference

## Quick Navigation

| Resource           | Path                                     |
| ------------------ | ---------------------------------------- |
| Master Schema      | `sql/master_schema_rollup.sql`           |
| Initial Schema     | `sql/initial_schema.sql`                 |
| Migrations         | `sql/migrations/*.sql`                   |
| Triggers/Functions | `sql/Initial_triggers_and_functions.sql` |
| Initial Data       | `sql/initial_data.sql`                   |
| Drop Script        | `sql/drop_tables.sql`                    |
| Repositories       | `src/services/db/*.db.ts`                |
| Connection         | `src/services/db/connection.db.ts`       |
| Errors             | `src/services/db/errors.db.ts`           |

---

## Database Credentials

### Environments

| Environment   | User                 | Database             | Host                        |
| ------------- | -------------------- | -------------------- | --------------------------- |
| Production    | `flyer_crawler_prod` | `flyer-crawler-prod` | `DB_HOST` secret            |
| Test          | `flyer_crawler_test` | `flyer-crawler-test` | `DB_HOST` secret            |
| Dev Container | `postgres`           | `flyer_crawler_dev`  | `postgres` (container name) |

### Connection (Dev Container)

```bash
# Inside container
psql -U postgres -d flyer_crawler_dev

# From Windows via Podman
podman exec -it flyer-crawler-dev psql -U postgres -d flyer_crawler_dev
```

### Connection (Production/Test via SSH)

```bash
# SSH to server, then:
PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -U $DB_USER -d $DB_NAME
```

---

## Schema Tables (Core)

| Table                 | Purpose              | Key Columns                                                     |
| --------------------- | -------------------- | --------------------------------------------------------------- |
| `users`               | Authentication       | `user_id` (UUID PK), `email`, `password_hash`                   |
| `profiles`            | User data            | `user_id` (FK), `full_name`, `role`, `points`                   |
| `addresses`           | Normalized addresses | `address_id`, `address_line_1`, `city`, `latitude`, `longitude` |
| `stores`              | Store chains         | `store_id`, `name`, `logo_url`                                  |
| `store_locations`     | Physical locations   | `store_location_id`, `store_id` (FK), `address_id` (FK)         |
| `flyers`              | Uploaded flyers      | `flyer_id`, `store_id` (FK), `image_url`, `status`              |
| `flyer_items`         | Extracted deals      | `flyer_item_id`, `flyer_id` (FK), `name`, `price`               |
| `categories`          | Item categories      | `category_id`, `name`                                           |
| `master_items`        | Canonical items      | `master_item_id`, `name`, `category_id` (FK)                    |
| `shopping_lists`      | User lists           | `shopping_list_id`, `user_id` (FK), `name`                      |
| `shopping_list_items` | List items           | `shopping_list_item_id`, `shopping_list_id` (FK)                |
| `watchlist`           | Price alerts         | `watchlist_id`, `user_id` (FK), `search_term`                   |
| `activity_log`        | Audit trail          | `activity_log_id`, `user_id`, `action`, `details`               |

---

## Schema Sync Rule (CRITICAL)

**Both files MUST stay synchronized:**

- `sql/master_schema_rollup.sql` - Used by test DB setup
- `sql/initial_schema.sql` - Used for fresh installs

**When adding columns:**

1. Add migration in `sql/migrations/NNN_description.sql`
2. Add column to `master_schema_rollup.sql`
3. Add column to `initial_schema.sql`
4. Test DB uses `master_schema_rollup.sql` - out-of-sync = test failures

---

## Migration Pattern

### Creating a Migration

```sql
-- sql/migrations/NNN_descriptive_name.sql

-- Add column with default
ALTER TABLE public.flyers
ADD COLUMN IF NOT EXISTS new_column TEXT DEFAULT 'value';

-- Add index
CREATE INDEX IF NOT EXISTS idx_flyers_new_column
ON public.flyers(new_column);

-- Update schema_info
UPDATE public.schema_info
SET schema_hash = 'new_hash', updated_at = now()
WHERE environment = 'production';
```

### Running Migrations

```bash
# Via psql
PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -U $DB_USER -d $DB_NAME -f sql/migrations/NNN_description.sql

# In CI/CD - migrations are checked via schema hash
```

---

## Repository Pattern (ADR-034)

### Method Naming Convention

| Prefix    | Behavior                          | Return Type      |
| --------- | --------------------------------- | ---------------- |
| `get*`    | Throws `NotFoundError` if missing | `Entity`         |
| `find*`   | Returns `null` if missing         | `Entity \| null` |
| `list*`   | Returns empty array if none       | `Entity[]`       |
| `create*` | Creates new record                | `Entity`         |
| `update*` | Updates existing record           | `Entity`         |
| `delete*` | Removes record                    | `void`           |
| `count*`  | Returns count                     | `number`         |

### Repository Template

```typescript
// src/services/db/entity.db.ts
import { getPool } from './connection.db';
import { handleDbError, NotFoundError } from './errors.db';
import type { PoolClient } from 'pg';
import type { Logger } from 'pino';

export async function getEntityById(
  id: string,
  logger: Logger,
  client?: PoolClient,
): Promise<Entity> {
  const pool = client || getPool();
  try {
    const result = await pool.query('SELECT * FROM public.entities WHERE entity_id = $1', [id]);
    if (result.rows.length === 0) {
      throw new NotFoundError('Entity not found');
    }
    return result.rows[0];
  } catch (error) {
    handleDbError(error, logger, 'Failed to get entity', { id });
  }
}

export async function findEntityByName(
  name: string,
  logger: Logger,
  client?: PoolClient,
): Promise<Entity | null> {
  const pool = client || getPool();
  try {
    const result = await pool.query('SELECT * FROM public.entities WHERE name = $1', [name]);
    return result.rows[0] || null;
  } catch (error) {
    handleDbError(error, logger, 'Failed to find entity', { name });
  }
}

export async function listEntities(logger: Logger, client?: PoolClient): Promise<Entity[]> {
  const pool = client || getPool();
  try {
    const result = await pool.query('SELECT * FROM public.entities ORDER BY name');
    return result.rows;
  } catch (error) {
    handleDbError(error, logger, 'Failed to list entities', {});
  }
}
```

---

## Transaction Pattern (ADR-002)

```typescript
import { withTransaction } from './connection.db';

const result = await withTransaction(async (client) => {
  // All queries use same client
  const user = await userRepo.createUser(data, logger, client);
  const profile = await profileRepo.createProfile(user.user_id, profileData, logger, client);
  await activityRepo.logActivity('user_created', user.user_id, logger, client);
  return { user, profile };
});
// Commits on success, rolls back on any error
```

---

## Error Handling (ADR-001)

### Error Types

| Error                            | PostgreSQL Code | HTTP Status | Use Case                  |
| -------------------------------- | --------------- | ----------- | ------------------------- |
| `UniqueConstraintError`          | `23505`         | 409         | Duplicate record          |
| `ForeignKeyConstraintError`      | `23503`         | 400         | Referenced record missing |
| `NotNullConstraintError`         | `23502`         | 400         | Required field null       |
| `CheckConstraintError`           | `23514`         | 400         | Check constraint violated |
| `InvalidTextRepresentationError` | `22P02`         | 400         | Invalid type format       |
| `NumericValueOutOfRangeError`    | `22003`         | 400         | Number out of range       |
| `NotFoundError`                  | -               | 404         | Record not found          |
| `ForbiddenError`                 | -               | 403         | Access denied             |

### Using handleDbError

```typescript
import { handleDbError, NotFoundError } from './errors.db';

try {
  const result = await pool.query('INSERT INTO ...', [data]);
  if (result.rows.length === 0) throw new NotFoundError('Entity not found');
  return result.rows[0];
} catch (error) {
  handleDbError(
    error,
    logger,
    'Failed to create entity',
    { data },
    {
      uniqueMessage: 'Entity with this name already exists',
      fkMessage: 'Referenced category does not exist',
      defaultMessage: 'Failed to create entity',
    },
  );
}
```

---

## Connection Pool

```typescript
import { getPool, getPoolStatus } from './connection.db';

// Get pool (singleton)
const pool = getPool();

// Check pool status
const status = getPoolStatus();
// { totalCount: 20, idleCount: 15, waitingCount: 0 }
```

### Pool Configuration

| Setting                   | Value | Purpose             |
| ------------------------- | ----- | ------------------- |
| `max`                     | 20    | Max clients in pool |
| `idleTimeoutMillis`       | 30000 | Idle client timeout |
| `connectionTimeoutMillis` | 2000  | Connection timeout  |

---

## Common Queries

### Paginated List

```typescript
const result = await pool.query(
  `SELECT * FROM public.flyers
   ORDER BY created_at DESC
   LIMIT $1 OFFSET $2`,
  [limit, (page - 1) * limit],
);

const countResult = await pool.query('SELECT COUNT(*) FROM public.flyers');
const total = parseInt(countResult.rows[0].count, 10);
```

### Spatial Query (Find Nearby)

```typescript
const result = await pool.query(
  `SELECT sl.*, a.*,
   ST_Distance(a.location, ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography) as distance
   FROM public.store_locations sl
   JOIN public.addresses a ON sl.address_id = a.address_id
   WHERE ST_DWithin(a.location, ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography, $3)
   ORDER BY distance`,
  [longitude, latitude, radiusMeters],
);
```

### Upsert Pattern

```typescript
const result = await pool.query(
  `INSERT INTO public.stores (name, logo_url)
   VALUES ($1, $2)
   ON CONFLICT (name) DO UPDATE SET
     logo_url = EXCLUDED.logo_url,
     updated_at = now()
   RETURNING *`,
  [name, logoUrl],
);
```

---

## Database Reset Commands

### Dev Container

```bash
# Reset dev database (runs seed script)
podman exec -it flyer-crawler-dev npm run db:reset:dev

# Reset test database
podman exec -it flyer-crawler-dev npm run db:reset:test
```

### Manual SQL

```bash
# Drop all tables
podman exec -it flyer-crawler-dev psql -U postgres -d flyer_crawler_dev -f /app/sql/drop_tables.sql

# Recreate schema
podman exec -it flyer-crawler-dev psql -U postgres -d flyer_crawler_dev -f /app/sql/master_schema_rollup.sql

# Load initial data
podman exec -it flyer-crawler-dev psql -U postgres -d flyer_crawler_dev -f /app/sql/initial_data.sql
```

---

## Database Users Setup

```sql
-- Create database and user (as postgres superuser)
CREATE DATABASE "flyer-crawler-test";
CREATE USER flyer_crawler_test WITH PASSWORD 'password';
ALTER DATABASE "flyer-crawler-test" OWNER TO flyer_crawler_test;

-- Grant permissions
\c "flyer-crawler-test"
ALTER SCHEMA public OWNER TO flyer_crawler_test;
GRANT CREATE, USAGE ON SCHEMA public TO flyer_crawler_test;

-- Required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "postgis";

-- Verify permissions
\dn+ public
-- Should show 'UC' for the user
```

---

## Repository Files

| Repository            | Domain                     | Path                                  |
| --------------------- | -------------------------- | ------------------------------------- |
| `user.db.ts`          | Users, profiles            | `src/services/db/user.db.ts`          |
| `flyer.db.ts`         | Flyers, processing         | `src/services/db/flyer.db.ts`         |
| `store.db.ts`         | Stores                     | `src/services/db/store.db.ts`         |
| `storeLocation.db.ts` | Store locations            | `src/services/db/storeLocation.db.ts` |
| `address.db.ts`       | Addresses                  | `src/services/db/address.db.ts`       |
| `category.db.ts`      | Categories                 | `src/services/db/category.db.ts`      |
| `shopping.db.ts`      | Shopping lists, watchlists | `src/services/db/shopping.db.ts`      |
| `price.db.ts`         | Price history              | `src/services/db/price.db.ts`         |
| `gamification.db.ts`  | Achievements, points       | `src/services/db/gamification.db.ts`  |
| `notification.db.ts`  | Notifications              | `src/services/db/notification.db.ts`  |
| `recipe.db.ts`        | Recipes                    | `src/services/db/recipe.db.ts`        |
| `receipt.db.ts`       | Receipts                   | `src/services/db/receipt.db.ts`       |
| `admin.db.ts`         | Admin operations           | `src/services/db/admin.db.ts`         |
