---
description: Review an entity store PR
argument-hint: <pr-number>
allowed-tools: Read, Bash, Glob, Grep
disable-model-invocation: true
context: fork
---

# Review Entity Store Agent

Audit an entity-store PR. Answer the question: **"Is the data model right?"**

A domain may contain multiple entities. Verify all entities in the domain are covered.

## Project Root

The PR targets one project: `connect-rpc-backend/` or `grpc-backend/`.
Identify which project from the PR file paths.

## How to review

1. Fetch the PR diff:
   ```
   gh pr diff <number>
   ```

2. Identify the domain and all its entities. Cross-reference with proto definitions in `protos/`. Note: the domain name may group multiple proto packages (e.g., `collaboration` domain covers `space.v1` and `content.v1` protos).

3. Check every item below. For each, report **PASS** or **FAIL** with a brief explanation.

## Checklist

### SQL Migration — `sql/migrations/<NNNN>_create_<domain>.sql`

- [ ] Migration creates Postgres schema with `CREATE SCHEMA IF NOT EXISTS <domain>`
- [ ] All tables are schema-qualified (`<domain>.<entity>`)
- [ ] Single migration file contains all entity tables for the domain
- [ ] File has sequential 4-digit migration number (e.g., `0001`, `0002`) with no gaps or conflicts
- [ ] Has `-- +goose Up` and `-- +goose Down` annotations
- [ ] Each table has UUID primary key with `DEFAULT gen_random_uuid()`
- [ ] Each table has `created_at TIMESTAMPTZ NOT NULL DEFAULT now()`
- [ ] Each table has `updated_at TIMESTAMPTZ NOT NULL DEFAULT now()`
- [ ] Each table has `deleted_at TIMESTAMPTZ` nullable column (soft delete)
- [ ] Column types match proto field types (string → TEXT, int32 → INT, etc.)
- [ ] Proto enums map to INT
- [ ] Proto `repeated string` maps to `TEXT[]`
- [ ] Proto `google.protobuf.Timestamp` maps to `TIMESTAMPTZ`
- [ ] Foreign keys reference parent tables with appropriate `ON DELETE` behavior
- [ ] Down migration drops tables in reverse dependency order with `IF EXISTS`, then drops schema

### sqlc Queries — `sql/queries/<domain>/`

For each entity in the domain:

- [ ] Separate `.sql` file per entity (e.g., `<entity_a>.sql`, `<entity_b>.sql`)
- [ ] Has `Get<Entity> :one` query selecting by `id`
- [ ] Has `List<Entity> :many` query with `LIMIT` and `OFFSET` using `sqlc.arg()`
- [ ] Has `Count<Entity> :one` query
- [ ] Has `Create<Entity> :one` with `RETURNING *`
- [ ] Has `Update<Entity> :one` using `sqlc.narg()` + `COALESCE` for optional fields
- [ ] Update query includes `updated_at = now()`
- [ ] Has `SoftDelete<Entity> :one` setting `deleted_at = now()`, returning the row
- [ ] Has `Restore<Entity> :one` setting `deleted_at = NULL`, returning the row
- [ ] No hard delete queries
- [ ] All read queries filter with `AND deleted_at IS NULL`
- [ ] Update query filters with `AND deleted_at IS NULL`
- [ ] All `sqlc.arg()` / `sqlc.narg()` names are snake_case
- [ ] Query names prefixed with entity name (no collisions in shared package)
- [ ] Child entities have `ListBy<Parent>` queries where applicable

### sqlc.yaml

- [ ] Single entry per domain (not per entity)
- [ ] `engine: "postgresql"`
- [ ] `queries` points to `sql/queries/<domain>/`
- [ ] `schema` points to `sql/migrations/`
- [ ] `out` points to `gen/db/<domain>`
- [ ] `sql_package: "pgx/v5"`
- [ ] UUID override maps to `github.com/gofrs/uuid/v5`
- [ ] Timestamptz override maps to `time.Time`

### Proto ↔ SQL Consistency

For each entity:

- [ ] Proto field names align with SQL column names (allowing for case convention differences)
- [ ] Every proto field that maps to a DB column has a corresponding sqlc query parameter
- [ ] No Go source files modified (this PR is schema-only)

## Output format

```
## Entity Store PR Audit — <domain>

### Summary
<one sentence: pass or issues found>

### Entities Found
| Entity | Table | Query File | CRUD Complete |
|--------|-------|------------|---------------|
| <entity_a> | <entity_a> | <entity_a>.sql | yes |
| <entity_b> | <entity_b> | <entity_b>.sql | yes |

### Proto ↔ SQL Consistency (per entity)
| Entity | Proto Field | Type | SQL Column | Type | Match |
|--------|-------------|------|------------|------|-------|
| <entity_a> | id | string | id | UUID | yes |
| ... | ... | ... | ... | ... | ... |

### Results
| Check | Status | Notes |
|-------|--------|-------|
| migration numbering | PASS | |
| ... | FAIL | <explanation> |

### Issues
<numbered list of FAIL items with details and suggested fixes>
```

## PR Context

- PR diff: !`gh pr diff $ARGUMENTS`
- PR info: !`gh pr view $ARGUMENTS --json number,title,body,state,baseRefName,headRefName,url`