# Task: Implement Postgres-repo (PostgreSQLDatabase) changes

You are working in the PostgreSQLDatabase repo. This repo owns ALL
Postgres DDL — tables, columns, indexes, FKs, views, functions, stored
procedures, triggers, seed data, and Patch one-offs. The same files are
consumed as a git submodule by `CitusDistributionLogic` for the sharded
deployment.

## First: read these in order

1. `CLAUDE.md` at the repo root — the team's canonical conventions.
2. `non_distributed_changelog.xml` — master, shows deployment order.
3. The plan in `_PLAN.yaml`.
4. At least one style anchor: a recent file matching what you're about
   to write (table create, alter, procedure, function, view, or
   constraint).

⚠ The team's `CLAUDE.md` calls the shard column `ProjectShardKey` in
some places. **The actual code uses `ProjectShardId` (INT) universally.**
Match the code. The `fn_GetProjectShardId` function is the real bridge
from `ProjectId UUID` → `ProjectShardId INT`.

## Repo layout

```
Database/
├── xyz/{Tables, Constraints, Views, Procedures, Functions, Triggers}
├── staging/{Tables, Views, Procedures, Functions}
├── reporting/{Tables, Constraints, Views, Procedures, Functions}
├── Patch/                              # one-off data backfills
└── roles_and_permissions.sql           # runs first
non_distributed_changelog.xml           # master; uses <includeAll>
```

## Two file patterns

> **Changeset author — MANDATORY:** every `--changeset` header you write
> MUST use `agentneo` (lowercase) as the author (the part before the `:`),
> e.g. `--changeset agentneo:NNN_xyz_add_widget`. Never substitute a human
> name, `yourname`, or a git username. The `-- Created by:` comment line uses
> the display name `AgentNeo` (capitalized). State in your report that the
> change file is AI-generated and was authored as `agentneo`.

### Tables — depends on creation date

**For NEW tables** (modern convention, May 2026+):
- Pick the next numeric prefix: `ls Database/<schema>/Tables/ | sort -V | tail -1`, take `last + 1`.
- Filename: `NNN_<schema>_<short_description>.sql`
- Header: `--changeset agentneo:NNN_<schema>_<short_description> runOnChange:true stripComments:true splitStatements:true endDelimiter:;`
- Body: `CREATE TABLE IF NOT EXISTS <schema>."TableName" (...);`
- **Subsequent column changes go in the SAME file** (additional
  `ALTER TABLE ... ADD COLUMN IF NOT EXISTS ...` statements appended).
- **NEVER use `DROP TABLE IF EXISTS`** — it risks wiping prod.

**For ALTERS to existing tables** (created before May 2026):
- The original create file is non-editable (no `runOnChange`).
- Look for an existing `103_..._<tablename>_alter.sql` etc. and APPEND
  to it.
- If no alter file exists for that table, create
  `<next_number>_<schema>_<tablename>_alter.sql` (continue from
  highest existing alter, currently in 100s) with `runOnChange:true`,
  and ALL future column changes for that table go in this single file.

### Procedures, Functions, Views, Triggers — idempotent

- Filename = object name (e.g. `usp_InsertCoordinate.sql`,
  `fn_GetCoordinates.sql`, `vw_Coordinate.sql`). One file per object.
- Header: `--changeset agentneo:<ObjectName> runOnChange:true stripComments:false endDelimiter:/`
- **Body MUST start with `DROP PROCEDURE/FUNCTION/VIEW IF EXISTS …;`**
  before `CREATE OR REPLACE` (signature changes need the drop).
- **Last line of file MUST be `/`** (because `endDelimiter:/`).
- Edit in place when the object changes.

### When a column is added/removed/altered, amend procs to cover ALL columns

If `postgres_changes` adds/removes/alters a column, the read/write procs and
functions for that entity must cover the table's FULL current column set — not
just the changed one. A partial proc desyncs the API layer and breaks its tests.
- Write procs (usp_Update<Entity>, usp_Insert<Entity>) accept/write EVERY
  relevant column incl. the new one. E.g. after adding Project.EnableAI,
  usp_UpdateProject sets EnableAI alongside the existing columns — amend the
  general update proc, don't add a narrow usp_UpdateProjectEnableAI.
- Read fns (fn_Get<Entity>) SELECT/RETURNS TABLE the full current column set.
- On REMOVE/RENAME, drop/rename it everywhere it appears.
- Note the column-coverage change in _SPECIALIST_REPORT.md so the API2
  specialist updates its DTOs, mocks, fixtures, and tests to match.

### Constraints (FKs and indexes)

- Live in `Database/<schema>/Constraints/`.
- ALL FKs go here, NOT in the table-create file. **Required for Citus
  submodule compatibility.**
- Filename: `NNN_<schema>_<topic>_constraints.sql`, numeric prefix
  matching the related table file.
- Use `ALTER TABLE <schema>."Foo" ADD CONSTRAINT "Foo_Bar_fkey" FOREIGN
  KEY ("BarId") REFERENCES <schema>."Bar" ("BarId");`
- **For project-scoped FKs:** include `ProjectShardId` in the FK:
  `FOREIGN KEY ("ProjectShardId", "BarId") REFERENCES <schema>."Bar"
  ("ProjectShardId", "BarId")`.

### Indexes — strictly opt-in via the plan's `index_changes`

**Indexes are NOT created by default. The specialist writes ONE
`CREATE INDEX` statement per entry in the plan's `index_changes`
list, and ZERO otherwise.**

The list has already been filtered by the human reviewer at Gate 2 —
they ticked the proposals they wanted. Anything not in the list was
explicitly rejected and must NOT be added back, no matter how
obvious the omission seems to you.

When `index_changes` has entries:

- Append each one to `Database/<schema>/Constraints/999_indexes.sql`.
  Never create a new file for indexes. Group new entries by their
  table with a `-- <schema>."<TableName>"` header comment if the
  section doesn't already exist for that table.
- Use the entry's `name` and `columns` literally:
  ```sql
  -- <schema>."<TableName>"
  CREATE INDEX IF NOT EXISTS "<name>" ON
      <schema>."<TableName>" ("Col1", "Col2");
  ```
- If `unique: true`, emit `CREATE UNIQUE INDEX IF NOT EXISTS ...`.
- The file's existing changeset already has `runOnChange:true` and
  `endDelimiter:;`; just append your `CREATE INDEX` statements.

When `index_changes` is `none` or empty:
- **Do nothing in 999_indexes.sql.** Do not propose, invent, or
  suggest indexes inline. The human consciously chose to ship without
  them; respect that.

The PK index is created automatically by Postgres from the
`PRIMARY KEY (...)` constraint. Don't add a separate `CREATE INDEX`
for the PK columns.

## Object header (mandatory comment block)

Every new database object includes this block inside the file (after
the changeset header):

```sql
----------------------------------------------------------------------------------------------------
-- Created by: AgentNeo
-- Created on: <DD/MM/YYYY today>
-- Description: <Short, one-line description>
----------------------------------------------------------------------------------------------------
```

## Procedure template (use verbatim)

```sql
--liquibase formatted sql
--changeset agentneo:usp_<Name> runOnChange:true stripComments:false endDelimiter:/
--comment: Create or replace <schema>.usp_<Name>

----------------------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  <DD/MM/YYYY>
-- Description: <description>
----------------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS <schema>."usp_<Name>";
CREATE OR REPLACE PROCEDURE <schema>."usp_<Name>"(_projectId UUID, _otherParam TEXT)
LANGUAGE plpgsql AS $$
DECLARE
    _projectShardId INT;
    _sqlState TEXT; _message TEXT; _detail TEXT; _hint TEXT; _context TEXT;
BEGIN
    SELECT xyz."fn_GetProjectShardId"(_projectId) INTO _projectShardId;

    -- body here, always filter by _projectShardId

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        GET STACKED DIAGNOSTICS
            _sqlState := RETURNED_SQLSTATE, _message := MESSAGE_TEXT,
            _detail := PG_EXCEPTION_DETAIL, _hint := PG_EXCEPTION_HINT,
            _context := PG_EXCEPTION_CONTEXT;
        INSERT INTO xyz."DbException" ("DbExceptionId", "SqlState", "Message", "Detail", "Hint", "Context")
        VALUES (GEN_RANDOM_UUID(), _sqlState, _message, _detail, _hint, _context);
        COMMIT;
        RAISE;
END;$$;
/
```

## Parameter naming — mirror the target column

Procedure and function parameters that carry audit values MUST be
named to match the column they assign to:

| Target column      | Parameter name      |
| ------------------ | ------------------- |
| `"LastModifiedBy"` | `_lastModifiedBy`   |
| `"LastModifiedOn"` | `_lastModifiedOn`   |
| `"UpdatedBy"`      | `_updatedBy`        |
| `"UpdatedOn"`      | `_updatedOn`        |
| `"CreatedBy"`      | `_createdBy`        |
| `"DeletedBy"`      | `_deletedBy`        |

**NEVER use generic names like `_modifiedBy`, `_editedBy`,
`_modifierUser`.** If you find yourself reaching for a generic name,
inspect the target table's columns and use the matching-cased
parameter.

**How to pick between `_lastModifiedBy` and `_updatedBy`**: read the
table file in `Database/<schema>/Tables/`. The table tells you which
audit column it has — pre-May-2026 tables have `UpdatedBy`,
post-May-2026 tables have `LastModifiedBy`. Use the parameter name
that matches whichever column is on the table.

Wrong (real example from `usp_UpdateFileReference.sql` in the repo):

```sql
CREATE OR REPLACE PROCEDURE xyz."usp_UpdateFileReference"(
    _modifiedBy TEXT,                 -- generic name
    ...
)
...
    "LastModifiedBy" = _modifiedBy,   -- column name and param name diverge
```

Right:

```sql
CREATE OR REPLACE PROCEDURE xyz."usp_UpdateFileReference"(
    _lastModifiedBy TEXT,             -- name matches column
    ...
)
...
    "LastModifiedBy" = _lastModifiedBy,
```

## Column-name verification — required before writing UPDATE/INSERT

Before you write any UPDATE, INSERT, or SELECT in a procedure or
function, **read the target table's create file** in
`Database/<schema>/Tables/` and use the exact column names from it.

Common drift patterns to avoid (NONE of these columns exist on any
table in this repo):

- `"ModifiedBy"` / `"ModifiedOn"` — use `LastModifiedBy`/`LastModifiedOn`
  (new tables) or `UpdatedBy`/`UpdatedOn` (legacy tables).
- `"EditedBy"` / `"EditedOn"` — use `LastModifiedBy`/`LastModifiedOn`.
- `"UpdatedDate"` / `"DateModified"` — use `UpdatedOn` / `LastModifiedOn`.
- `"CreatedOn"` — use `InsertedOn` (every table uses this).
- `"InsertedBy"` — use `CreatedBy` (yes the naming is asymmetric;
  that's the convention).

If you're unsure which audit columns a particular table has, the
quickest check is:

```bash
grep -E '"(LastModifiedBy|UpdatedBy|CreatedBy|InsertedOn)"' Database/<schema>/Tables/<table_file>.sql
```

If the table has `LastModifiedBy`, use `_lastModifiedBy` in your
procedure. If it has `UpdatedBy`, use `_updatedBy`. Don't paraphrase
from memory.

## Function template

```sql
--liquibase formatted sql
--changeset agentneo:fn_<Name> runOnChange:true stripComments:false endDelimiter:/
--comment: Create or replace <schema>.fn_<Name>

DROP FUNCTION IF EXISTS <schema>."fn_<Name>";
CREATE OR REPLACE FUNCTION <schema>."fn_<Name>"(_projectId UUID)
RETURNS TABLE ("foo" UUID, "bar" INT)
AS $$
DECLARE
    _projectShardId INT;
BEGIN
    SELECT xyz."fn_GetProjectShardId"(_projectId) INTO _projectShardId;
    RETURN QUERY
    SELECT a."FooId", a."Bar"
    FROM <schema>."Foo" a
    WHERE a."ProjectShardId" = _projectShardId;
END
$$ LANGUAGE plpgsql;
/
```

## Project-scoped table requirements (hard rules)

A new project-scoped table MUST:

1. Include `"ProjectShardId" INT NOT NULL` as a column.
2. Use a composite PK: `("ProjectShardId", "<TableName>Id")`.
3. Include `"Id" INT GENERATED ALWAYS AS IDENTITY` (internal audit key).
4. Include `"InsertedOn" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT
   TIMEZONE('UTC', NOW())`.
5. Include `"CreatedBy" TEXT NOT NULL`.
6. Include `"LastModifiedOn" TIMESTAMP WITH TIME ZONE NULL` and
   `"LastModifiedBy" TEXT NULL` if the entity is mutable.
7. End with `ALTER TABLE <schema>."<TableName>" OWNER TO datapipeline;`.
8. Have ALL FKs in a separate `Constraints/NNN_<schema>_<topic>_constraints.sql`.
9. Have indexes ONLY as instructed by `index_changes` (see Indexes
   section above). The PK index is auto-created; no others by default.

Reference/lookup tables (small, replicated everywhere via Citus) do NOT
need `ProjectShardId`. Their PK is just `("<TableName>Id")`.

**Column names must match the existing repo conventions exactly** —
`InsertedOn` / `CreatedBy` / `LastModifiedOn` / `LastModifiedBy` /
`IsDeleted` / `DeletedOn`. Don't substitute synonyms (no `UpdatedOn`,
`LastEditedOn`, `ModifiedDate`, etc.). The plan check catches these
before the work reaches you, but be alert anyway.

## SQL style

- **Keywords:** UPPERCASE
- **Identifiers:** `"PascalCase"` in double quotes
- **Schemas:** lowercase (`xyz`, `staging`, `reporting`)
- **Tables:** singular nouns
- **Custom types/enums:** `snake_case`

Query formatting (per CLAUDE.md): NOT waterfall style. Indent only for
clarity. Aliases right after the FROM table.

## Schema policy

- `xyz` — operational, default for new work
- `staging` — ETL landing zone; new ingest procedures land here
- `reporting` — analytics; views/functions/tables that derive from `xyz`
- `public` — Liquibase metadata; do NOT touch

## Patch/ directory (one-off backfills)

For features that need a one-time data fix or backfill (NOT schema DDL):

- Filename: `NNN_patch_<description>.sql` (continue from highest)
- Standard table-file header.
- Body: `INSERT`, `UPDATE`, or other one-off SQL.
- Use only for data, not DDL. Schema DDL goes in `Tables/`,
  `Constraints/`, etc.

## Verification

```bash
./build            # fresh postgres on :5430; deploys full changelog from scratch
./test --no-docker # if postgres is already running on :5490; runs integration tests
```

The `./build` command is the gate — it confirms the changelog still
deploys cleanly from-scratch with your new file in place. If it fails,
something is wrong (usually: a constraint references a column that
doesn't exist yet, or numeric ordering puts a dependent file before its
dependency).

If neither command is available (no Docker), do a static review and
note in the report that runtime verification couldn't be performed.

## Reporting back

Write `_SPECIALIST_REPORT.md` at the repo root with:
- Files added / modified, grouped by directory.
- Numeric prefix(es) chosen and why.
- For each new project-scoped table: confirmation that
  `ProjectShardId` is in the PK, FKs are in the Constraints/ file,
  and indexes are in 999_indexes.sql.
- Whether `./build` and/or `./test` ran clean.
- Any deviations from the plan, with reasons.
- Open questions for human reviewer.

## Hard constraints

- Do NOT edit existing non-`runOnChange:true` table files.
- Do NOT use `DROP TABLE IF EXISTS` (catastrophic risk).
- Do NOT put FKs in the table-create file — they MUST be in
  `Constraints/`.
- Do NOT create a separate file for each new index — append to
  `999_indexes.sql`.
- Do NOT touch the `public` schema.
- Do NOT modify `non_distributed_changelog.xml` unless introducing a
  new top-level schema.
- If the plan says ProjectShardKey (a documentation drift), treat it as
  ProjectShardId — match the actual code.
- If the plan asks for something that conflicts with conventions, STOP
  and document in your report. Do not invent.
- The plan in `_PLAN.yaml` is the contract.