# Postgres Specialist Report — PAPI-3535

## Summary

Added an optional `_typeNames TEXT[]` parameter to the existing read function
`xyz."fn_GetIssueList"` so that `GET /api/v2/projects/{projectId}/issues` can be
filtered by issue type name. When the parameter is `NULL` or an empty array the
function behaves byte-for-byte as before (full, unfiltered list). When type
names are supplied, only issues whose `IssueType.TypeName` matches one of the
values are returned (OR semantics, case-insensitive).

This file is **AI-generated**. The changeset header on the touched object was
authored as `agentneo`.

## Reviewer feedback addressed

> **"Did you do client side filtering even though we're doing a postgres change?"**

No. The filtering is performed **server-side inside Postgres**, not in
application code. The `_typeNames` filter is a predicate in the `WHERE` clause
of `fn_GetIssueList` (added at the bottom of the existing predicate block):

```sql
AND (
    _typeNames IS NULL
    OR cardinality(_typeNames) = 0
    OR LOWER(typ."TypeName") = ANY(SELECT LOWER(name) FROM unnest(_typeNames) AS name)
)
```

Key points confirming this is server-side, not client-side:

- The predicate is evaluated by the database engine and is applied **before**
  `ORDER BY issue."Id"` and `LIMIT _limit`. Rows excluded by the type filter
  never leave Postgres, so pagination (`_lastFetchedIndexId` / `_limit`) and the
  ~11.7 MB payload reduction are honoured at the source.
- It joins the already-present `xyz."IssueType" typ` alias (composite-key join on
  `IssueTypeId` + `ProjectShardId`) and matches on `TypeName` — not on the
  project-scoped `IssueTypeId` — exactly as the plan requires.
- It follows the existing server-side precedent
  `Database/reporting/Functions/fn_GetLegacyIssue.sql:115`
  (`typ."TypeName" IN ('Quality', 'Observation', ...)`), generalised to a
  parameterised array.
- `unnest(_typeNames)` and `ANY(...)` are fully evaluated in-engine; no part of
  the match is delegated to the API/service layer.

No code change was required to satisfy the feedback — the implementation was
already server-side. This report is added to make that explicit. (The API2
specialist must still pass the parsed array straight through to the single
`fn_GetIssueList` call and must NOT post-filter in application code.)

## Files added / modified

### `Database/xyz/Functions/`
- **Modified** `fn_GetIssueList.sql` — added `_typeNames TEXT[] DEFAULT NULL` as
  the final (trailing) parameter and the server-side filter predicate. Added a
  `DROP FUNCTION IF EXISTS` overload for the previous 5-arg signature so the new
  6-arg signature replaces it cleanly (an added parameter changes the routine's
  argument list, which `CREATE OR REPLACE` alone cannot do). Edited in place
  (`runOnChange:true`, body begins with `DROP FUNCTION IF EXISTS`, `endDelimiter:/`,
  file ends with `/`).

### Repo root
- **Added** `_SPECIALIST_REPORT.md` (this file).

## Numeric prefixes

None used — the only changed object is a function. Functions are one-file-per-object
and idempotent (no numeric prefix), edited in place per repo convention.

## Project-scoped table checklist

Not applicable. No new tables, columns, constraints, FKs, or indexes were
created. This is a read-only function change.

- `index_changes`: `none` — nothing added to `999_indexes.sql`.
- `seed_data_changes` / `reporting_changes` / `citus_changes`: `none`.

## Column-coverage note

The function's `RETURNS TABLE` column set, column order, ordering
(`ORDER BY issue."Id" ASC`), and pagination semantics are **unchanged**. No
column was added/removed/altered on `Issue` or `IssueType`, so no general
read/write proc column-set amendment was required. The only signature change is
the additive trailing parameter — the `NULL`/absent path is identical to the
pre-change behaviour, preserving backward compatibility for web, XYZMobile, and
AtomOS.

## Decisions applied (from plan / Gate 2)

- **Case sensitivity**: implemented case-INSENSITIVE matching
  (`LOWER(typ."TypeName") = ANY(SELECT LOWER(name) FROM unnest(_typeNames) AS name)`).
- **Unknown typeName**: yields an empty (or partial) filtered list, not a 400 and
  not a fallback to the full list — additive OR semantics; an unmatched value
  contributes no rows.
- **No hard cap** on the number of type names at the DB layer (any bound is an
  API2 validator concern).

## Verification

- Static review performed. The added predicate references only the existing
  `typ."TypeName"` column on the already-joined `xyz."IssueType"` alias; no new
  schema objects are referenced, so changelog ordering is unaffected.
- `./build` / `./test` were not run in this environment (no Docker available).
  Runtime from-scratch deployment verification could not be performed here.

## Deviations from plan

None.

## Open questions for human reviewer

None outstanding. The Gate-2 decisions (case-insensitive, empty-on-unmatched, no
DB-layer cap) are implemented as recorded in the plan.