# SSP Table Rename Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Rename three verbosely-named OSCAL SSP tables to short `ssp(s)_*` names across the jedi-oscal package, the BE main project, and 4 DB environments — with zero data loss and zero runtime regression.

**Architecture:** In-place `ALTER TABLE ... RENAME` (data + FK integrity preserved automatically by PostgreSQL — no drop/recreate). ORM (`__tablename__` + schema-qualified FK strings + explicit index names) and BE raw SQL must change in lockstep with the DB rename, because old-code-vs-renamed-table and new-code-vs-old-table both crash. jedi-oscal is modified via dev path dependency first; published to Nexus only after the whole change passes smoke.

**Tech Stack:** Python 3 / SQLAlchemy 2.x (Mapped/mapped_column) / PostgreSQL (schema `oscal` + `compliance`) / Poetry / Nexus private PyPI.

---

## Rename Map (LOCKED)

| Current | New |
|---------|-----|
| `oscal.system_security_plans` | `oscal.ssps` |
| `oscal.system_security_plan_control_implementations` | `oscal.ssp_control_implementations` |
| `oscal.system_security_plans_system_characteristics` | `oscal.ssp_system_characteristics` |

**Decisions locked with user (2026-06-10):**
1. Core table → **`ssps`** (plural, matches existing `ssp_components` / `ssp_inventory_items` convention).
2. Modify **jedi-oscal** package via **dev path dependency**; publish to Nexus only at closing.
3. **Rename index / constraint / sequence / pkey** old-name remnants too (clean, not just `ALTER TABLE`).

## Scope Boundaries (DO NOT cross)

- **ONLY table names change. Columns are NOT renamed.** The FK column `system_security_plan_id` (on `ssp_control_implementations`, `ssp_system_characteristics`, `ssp_control_implementation_objectives`) and `ssp_id` (on components/inventory/leveraged) stay exactly as-is. Do not "tidy" column names — out of scope, would explode the blast radius.
- The already-`ssp_`-prefixed tables (`ssp_components`, `ssp_docx_parse_jobs`, `ssp_reference_documents`, `ssp_inventory_items`, `ssp_control_implementation_objectives`, `ssp_leveraged_authorizations`, etc.) are **NOT** renamed. They are the child tables; they keep their names.
- FE (`compliance-manager-fe`) and E2E test (`compliance-manager-test`) repos: confirmed **zero** DB-table-name references — no changes there.
- API field names / JSON keys (`sspUid`, `system_security_plan_uid`) are **not** DB table names and are **out of scope**.

## Pre-flight (verify reality before editing — plan was written 2026-06-10)

> **DB credential note:** All migration psql commands below use `-U cmmgr` (required — `cm_app` is RLS-blocked and silently no-ops system-level writes, per CLAUDE.md). The live `.env` `DB_SECRET` currently holds the `cm_app` account, but in DEV/STG/POC `cmmgr` and `cm_app` share the SAME password (see memory `reference_dev_db_psql_port`), so `PGPASSWORD=<查.env DB_SECRET password>` works with `-U cmmgr`. Never write the literal password into any committed file.

- [ ] **Confirm DEV row counts unchanged** (sanity that we're targeting live data):
  Run: `PGPASSWORD=<查.env DB_SECRET> psql -h 192.168.50.188 -p 25432 -U cmmgr -d guidant_ai_dev -tA -c "SELECT relname, reltuples::bigint FROM pg_class WHERE relname IN ('system_security_plans','system_security_plan_control_implementations','system_security_plans_system_characteristics');"`
  Expected: three rows (~181 / ~2312 / ~180). If tables already renamed or counts wildly off, STOP and re-scope.
- [ ] **Confirm no NEW view/function dependency** appeared since scoping (re-run the introspection from the scoping step §4/§5). Expected: zero views, zero functions. If any appeared, add them to Phase 3.
- [ ] **Confirm jedi-oscal install form** in BE venv:
  Run: `python -c "import jedi_oscal, os; print(os.path.realpath(jedi_oscal.__file__))"` from BE root (with venv active).
  Expected: a `site-packages` path (pinned Nexus version). We will switch it to path-dep in Task 1.

---

## File Structure

**jedi-oscal package** (`~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal/jedi_oscal/`):
- `infra/model/ssp/ssp.py` — table `system_security_plans` → `ssps`; 6 index names; comment
- `infra/model/ssp/ssp_control_implementation.py` — table + 1 FK string
- `infra/model/ssp/ssp_system_characteristic.py` — table + 1 FK string
- `infra/model/ssp/ssp_control_implementation_objective.py` — 2 FK strings (table itself unchanged)
- `infra/model/base/oscal_component.py` — 1 FK string + comment
- `infra/model/base/oscal_inventory_item.py` — 1 FK string + comment
- `infra/model/base/oscal_leveraged_authorization.py` — 1 FK string + comment

**BE main project** (`~/Projects/Billows/Audit-Manager/compliance-manager-be/`):
- `infra/associations/model/project_system_characteristic.py` — 1 FK string + comment
- `app/module_frame/service/module_frame_template_copy_service.py` — 4 raw-SQL table references (RUNTIME-BREAKING)
- `scripts/smoke_test_ssp_docx_parser.py` — 2 raw-SQL references (RUNTIME — runs against live DB; fix with the rename, NOT deferred)
- `pyproject.toml` — dev path-dep toggle (revert before final commit)
- `scripts/sql/2026-06-10-ssp-table-rename.sql` — **new** migration
- Lower-priority (prose / doc-only, batched in Phase 6): BE service comments/docstrings, doc-gen scripts under `docs/`, test assertions, markdown docs.

---

## Phase 1 — jedi-oscal ORM (dev path-dep, no publish)

### Task 1: Switch BE to jedi-oscal path dependency

**Files:**
- Modify: `compliance-manager-be/pyproject.toml`

- [ ] **Step 1: Locate both jedi-oscal lines**

Run: `grep -n "jedi-oscal" /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be/pyproject.toml`
Expected (current reality): a PINNED entry in the PEP 621 `[project] dependencies` array — `"jedi-oscal==0.0.21"` (around line 80) — AND a COMMENTED path override under `[tool.poetry.dev-dependencies]` — `#jedi-oscal = { path = ".../jedi-oscal", develop = true}` (around line 95).

- [ ] **Step 2: Toggle to path dependency — TWO edits**

  (a) Comment out the pinned array entry (line ~80): `"jedi-oscal==0.0.21",` → `# "jedi-oscal==0.0.21",  # dev: using path override below`
  (b) Uncomment the dev-dependency path line (line ~95): remove the leading `#` from
  `#jedi-oscal = { path = "/Users/chouraymond/Projects/Jedicogy/module/jedi-python-package/jedi-oscal", develop = true}`

  (The path override MUST stay under the `[tool.poetry.dev-dependencies]` table — don't relocate it.)

- [ ] **Step 3: Resolve**

Run: `cd /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be && poetry update jedi-oscal`
Expected: resolves to the local path. (Use `poetry update`, never `poetry lock` — see CLAUDE.md.)

- [ ] **Step 4: Verify path is local**

Run: `python -c "import jedi_oscal, os; print(os.path.realpath(jedi_oscal.__file__))"`
Expected: a path under `~/Projects/Jedicogy/.../jedi-oscal/`, NOT site-packages.

- [ ] **Step 5: Do NOT commit this pyproject change yet** — it is a dev-only change, reverted in Phase 6. Note it in working tree only.

### Task 2: Rename core table in `ssp.py`

**Files:**
- Modify: `jedi-oscal/jedi_oscal/infra/model/ssp/ssp.py:36-45`

- [ ] **Step 1: Replace `__tablename__` and the 6 index names** (column `__tablename__` block, lines 36-45):

Old → New (exact string edits):
- `__tablename__ = "system_security_plans"` → `__tablename__ = "ssps"`
- `Index("ix_system_security_plans_uid", "uid")` → `Index("ix_ssps_uid", "uid")`
- `Index("ix_system_security_plans_profile_id", "profile_id")` → `Index("ix_ssps_profile_id", "profile_id")`
- `Index("ix_system_security_plans_status", "status")` → `Index("ix_ssps_status", "status")`
- `Index("ix_system_security_plans_metadata_id", "metadata_id")` → `Index("ix_ssps_metadata_id", "metadata_id")`
- `Index("ix_system_security_plans_document_id", "document_id")` → `Index("ix_ssps_document_id", "document_id")`
- `Index("ix_system_security_plans_group_id", "group_id")` → `Index("ix_ssps_group_id", "group_id")`

Leave unchanged: `UniqueConstraint(..., name="uq_ssp_group_version")` (already `ssp`-prefixed), the `{"schema": "oscal", ...}` dict, the Python class name `OscalSystemSecurityPlan` (class name is NOT the table name — leave it to avoid a giant cross-codebase rename out of scope).

- [ ] **Step 2: Verify no stray old table-name strings remain in this file**

Run: `grep -n "system_security_plans" /Users/chouraymond/Projects/Jedicogy/module/jedi-python-package/jedi-oscal/jedi_oscal/infra/model/ssp/ssp.py`
Expected: zero hits (the TYPE_CHECKING imports reference class names / module paths, not the table string — confirm none are the literal table name).

### Task 3: Rename `system_security_plan_control_implementations` table + FK in `ssp_control_implementation.py`

**Files:**
- Modify: `jedi-oscal/jedi_oscal/infra/model/ssp/ssp_control_implementation.py:36,68`

- [ ] **Step 1: Replace table name (line 36)**
`__tablename__ = "system_security_plan_control_implementations"` → `__tablename__ = "ssp_control_implementations"`

- [ ] **Step 2: Replace parent FK string (line 68)**
`ForeignKey("oscal.system_security_plans.id", ondelete="CASCADE")` → `ForeignKey("oscal.ssps.id", ondelete="CASCADE")`

- [ ] **Step 3: Update docstring (line 30)** — `對應 oscal.system_security_plan_control_implementations 表。` → `對應 oscal.ssp_control_implementations 表。`

Leave unchanged: `uq_ssp_ctrl_impl_ssp_control_identifier`, all `ix_ssp_ctrl_impl_*` indexes (already `ssp`-prefixed), the FK **column** `system_security_plan_id`.

### Task 4: Rename `system_security_plans_system_characteristics` table + FK in `ssp_system_characteristic.py`

**Files:**
- Modify: `jedi-oscal/jedi_oscal/infra/model/ssp/ssp_system_characteristic.py:23,48`

- [ ] **Step 1: Replace table name (line 23)**
`__tablename__ = "system_security_plans_system_characteristics"` → `__tablename__ = "ssp_system_characteristics"`

- [ ] **Step 2: Replace parent FK string (line 48)**
`ForeignKey("oscal.system_security_plans.id", ondelete="CASCADE")` → `ForeignKey("oscal.ssps.id", ondelete="CASCADE")`

Leave unchanged: all `ix_ssp_system_characteristics_*` indexes (already correct), the FK **column** `system_security_plan_id`.

### Task 5: Fix 2 FK strings in `ssp_control_implementation_objective.py`

**Files:**
- Modify: `jedi-oscal/jedi_oscal/infra/model/ssp/ssp_control_implementation_objective.py:59,64`

(NOTE: this table `ssp_control_implementation_objectives` is NOT renamed — only its two FK *targets* are.)

- [ ] **Step 1: Replace FK → ssps (line 59)**
`ForeignKey("oscal.system_security_plans.id", ondelete="CASCADE")` → `ForeignKey("oscal.ssps.id", ondelete="CASCADE")`

- [ ] **Step 2: Replace FK → ssp_control_implementations (line 64)**
`ForeignKey("oscal.system_security_plan_control_implementations.id", ondelete="CASCADE")` → `ForeignKey("oscal.ssp_control_implementations.id", ondelete="CASCADE")`

### Task 6: Fix 3 FK strings in base models

**Files:**
- Modify: `jedi-oscal/jedi_oscal/infra/model/base/oscal_component.py:91`
- Modify: `jedi-oscal/jedi_oscal/infra/model/base/oscal_inventory_item.py:86`
- Modify: `jedi-oscal/jedi_oscal/infra/model/base/oscal_leveraged_authorization.py:77`

- [ ] **Step 1: In each file, replace the FK string + the trailing comment**
`ForeignKey("oscal.system_security_plans.id", ondelete="CASCADE")` → `ForeignKey("oscal.ssps.id", ondelete="CASCADE")`
and the comment `FK → oscal.system_security_plans.id；隸屬之 SSP` → `FK → oscal.ssps.id；隸屬之 SSP` (3 occurrences, one per file).

- [ ] **Step 2: Also fix the docstring/comment mentions** flagged in scoping (oscal_component.py docstring L21, oscal_inventory_item.py docstring L19, oscal_leveraged_authorization.py docstring L15) — replace `system_security_plans` → `ssps` in prose.

- [ ] **Step 3: Fix the entity docstring** at `jedi-oscal/jedi_oscal/domain/entity/ssp/ssp_control_implementation_entity.py:13` — `對應 oscal.system_security_plan_control_implementations 表。` → `對應 oscal.ssp_control_implementations 表。` (so the Task 7 grep gate truly hits zero).

### Task 7: Package-wide grep gate (jedi-oscal)

- [ ] **Step 1: Confirm zero remaining literal old table names in jedi-oscal source**

Run:
```bash
grep -rn --include='*.py' -E 'system_security_plans\b|system_security_plan_control_implementations\b|system_security_plans_system_characteristics\b' /Users/chouraymond/Projects/Jedicogy/module/jedi-python-package/jedi-oscal/jedi_oscal/
```
Expected: zero hits. (The Python *class* name `OscalSystemSecurityPlan` has no trailing space-bounded table string, so `\b` on `system_security_plans` won't match it — but if any hit appears, inspect: class names stay, table strings go.)

- [ ] **Step 2: Confirm no other jedi-* package references these tables** (already verified clean in scoping, re-confirm):
```bash
grep -rn -E 'system_security_plan_control_implementations|system_security_plans_system_characteristics|"oscal\.system_security_plans' /Users/chouraymond/Projects/Jedicogy/module/jedi-python-package/ --include='*.py' | grep -v '/jedi-oscal/'
```
Expected: zero hits.

- [ ] **Step 3: Commit jedi-oscal changes** (in the jedi-oscal repo, explicit file adds, no `-am`):
```bash
cd /Users/chouraymond/Projects/Jedicogy/module/jedi-python-package/jedi-oscal
git add jedi_oscal/infra/model/ssp/ssp.py jedi_oscal/infra/model/ssp/ssp_control_implementation.py jedi_oscal/infra/model/ssp/ssp_system_characteristic.py jedi_oscal/infra/model/ssp/ssp_control_implementation_objective.py jedi_oscal/infra/model/base/oscal_component.py jedi_oscal/infra/model/base/oscal_inventory_item.py jedi_oscal/infra/model/base/oscal_leveraged_authorization.py
git commit -m "refactor(ssp): rename system_security_plans* tables to ssp(s)_*"
```
(Do NOT push / bump version / publish to Nexus yet — that's Phase 6.)

---

## Phase 2 — BE main project code

### Task 8: Fix FK string in `project_system_characteristic.py`

**Files:**
- Modify: `compliance-manager-be/infra/associations/model/project_system_characteristic.py:45,49`

- [ ] **Step 1: Replace FK string (line 45)**
`"oscal.system_security_plans_system_characteristics.id",` → `"oscal.ssp_system_characteristics.id",`

- [ ] **Step 2: Update comment (line 49)**
`comment="系統特性ID，關聯到system_security_plans_system_characteristics表"` → `comment="系統特性ID，關聯到ssp_system_characteristics表"`

Leave unchanged: the column `system_characteristic_id`, the imported class `OscalSystemSecurityPlanSystemCharacteristic` (class name, not table).

### Task 9: Fix the 4 raw-SQL references in `module_frame_template_copy_service.py` (RUNTIME-BREAKING)

**Files:**
- Modify: `compliance-manager-be/app/module_frame/service/module_frame_template_copy_service.py:75,321,327,347`

- [ ] **Step 1: Replace control-implementations table (line 75)**
`UPDATE oscal.system_security_plan_control_implementations ci` → `UPDATE oscal.ssp_control_implementations ci`

- [ ] **Step 2: Replace system-characteristics SELECT (line 321)**
`SELECT 1 FROM oscal.system_security_plans_system_characteristics` → `SELECT 1 FROM oscal.ssp_system_characteristics`

- [ ] **Step 3: Replace system-characteristics UPDATE (line 327)**
`UPDATE oscal.system_security_plans_system_characteristics sc` → `UPDATE oscal.ssp_system_characteristics sc`

- [ ] **Step 4: Replace system-characteristics INSERT (line 347)**
`INSERT INTO oscal.system_security_plans_system_characteristics` → `INSERT INTO oscal.ssp_system_characteristics`

(The column lists inside these statements — `system_security_plan_id`, etc. — are columns, NOT table names. Leave them.)

- [ ] **Step 5: Fix runtime SQL in the smoke script** (this IS live raw SQL, not docs — must move with the rename, not deferred):
`scripts/smoke_test_ssp_docx_parser.py:205,207`
- `FROM oscal.system_security_plan_control_implementations` → `FROM oscal.ssp_control_implementations`
- `SELECT id FROM oscal.system_security_plans WHERE uid = %s` → `SELECT id FROM oscal.ssps WHERE uid = %s`

### Task 10: BE breaking-change grep gate

- [ ] **Step 1: Confirm no remaining runtime table references in BE `.py` outside docs/tests**

Run:
```bash
grep -rn --include='*.py' -E 'oscal\.system_security_plans\b|oscal\.system_security_plan_control_implementations|oscal\.system_security_plans_system_characteristics' /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be/api /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be/app /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be/domain /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be/infra
```
Expected: **zero hits inside executable SQL / `text(...)` / `ForeignKey(...)` strings.** A handful of remaining hits will be PROSE ONLY (comments / docstrings) — these are acceptable here and cleaned in Phase 6 Task 19. Known prose-only survivors (verify each is a comment/docstring, not code): `app/oscal/service/ssp_excel_import_app_service.py:970,2168`, `app/oscal/service/ssp_versioning_service.py:4-5`, `domain/oscal/service/ssp_shell_service.py:124`, `infra/module_frame/models/module_frame_control_default.py:22`, `infra/grc/model/ssp_reference_document_mapping.py:15`. If a hit is anything other than a comment/docstring, STOP and fix it now.

- [ ] **Step 2: Do NOT commit BE code yet** — BE code + migration must land together (Task 14). Hold in working tree.

---

## Phase 3 — Migration script

### Task 11: Write the rename migration

**Files:**
- Create: `compliance-manager-be/scripts/sql/2026-06-10-ssp-table-rename.sql`

- [ ] **Step 1: Author the migration** with this content (date header per SQL-migration 鐵則; renames tables + sequences + pkeys + unique keys + indexes + old-name FK constraints; ends with `schema_migrations` insert):

```sql
-- Date: 2026-06-10
-- Purpose: Rename verbose SSP tables to short ssp(s)_* names (table-only; columns unchanged).
--   system_security_plans                          -> ssps
--   system_security_plan_control_implementations   -> ssp_control_implementations
--   system_security_plans_system_characteristics   -> ssp_system_characteristics
-- In-place ALTER ... RENAME: data preserved, FK integrity auto-followed by PostgreSQL.
-- Run with: psql --single-transaction -v ON_ERROR_STOP=1 -f <this file>  (account: cmmgr)

-- ===== 1. Tables =====
ALTER TABLE oscal.system_security_plans                        RENAME TO ssps;                      -- 2026-06-10
ALTER TABLE oscal.system_security_plan_control_implementations RENAME TO ssp_control_implementations; -- 2026-06-10
ALTER TABLE oscal.system_security_plans_system_characteristics RENAME TO ssp_system_characteristics;  -- 2026-06-10

-- ===== 2. Sequences (serial id owners) =====
ALTER SEQUENCE oscal.system_security_plans_id_seq                        RENAME TO ssps_id_seq;                      -- 2026-06-10
ALTER SEQUENCE oscal.system_security_plan_control_implementations_id_seq RENAME TO ssp_control_implementations_id_seq; -- 2026-06-10
ALTER SEQUENCE oscal.system_security_plans_system_characteristics_id_seq RENAME TO ssp_system_characteristics_id_seq;  -- 2026-06-10

-- ===== 3. Primary keys =====
ALTER TABLE oscal.ssps                       RENAME CONSTRAINT system_security_plans_pkey                        TO ssps_pkey;                       -- 2026-06-10
ALTER TABLE oscal.ssp_control_implementations RENAME CONSTRAINT system_security_plan_control_implementations_pkey TO ssp_control_implementations_pkey; -- 2026-06-10
ALTER TABLE oscal.ssp_system_characteristics RENAME CONSTRAINT system_security_plans_system_characteristics_pkey  TO ssp_system_characteristics_pkey;  -- 2026-06-10

-- ===== 4. Unique (uid) keys =====
ALTER TABLE oscal.ssps                       RENAME CONSTRAINT system_security_plans_uid_key                        TO ssps_uid_key;                       -- 2026-06-10
ALTER TABLE oscal.ssp_control_implementations RENAME CONSTRAINT system_security_plan_control_implementations_uid_key TO ssp_control_implementations_uid_key; -- 2026-06-10
ALTER TABLE oscal.ssp_system_characteristics RENAME CONSTRAINT system_security_plans_system_characteristics_uid_key  TO ssp_system_characteristics_uid_key;  -- 2026-06-10

-- ===== 5. Indexes on ssps (the only table whose ix_* embedded the old name) =====
ALTER INDEX oscal.ix_system_security_plans_uid         RENAME TO ix_ssps_uid;         -- 2026-06-10
ALTER INDEX oscal.ix_system_security_plans_profile_id  RENAME TO ix_ssps_profile_id;  -- 2026-06-10
ALTER INDEX oscal.ix_system_security_plans_status      RENAME TO ix_ssps_status;      -- 2026-06-10
ALTER INDEX oscal.ix_system_security_plans_metadata_id RENAME TO ix_ssps_metadata_id; -- 2026-06-10
ALTER INDEX oscal.ix_system_security_plans_document_id RENAME TO ix_ssps_document_id; -- 2026-06-10
ALTER INDEX oscal.ix_system_security_plans_group_id    RENAME TO ix_ssps_group_id;    -- 2026-06-10
-- (ix_ssp_ctrl_impl_* and ix_ssp_system_characteristics_* are already ssp-prefixed; no rename.)

-- ===== 6. FK constraints whose auto-generated name embedded the old table name =====
-- Outgoing FKs on ssps:
ALTER TABLE oscal.ssps RENAME CONSTRAINT system_security_plans_document_id_fkey TO ssps_document_id_fkey; -- 2026-06-10
ALTER TABLE oscal.ssps RENAME CONSTRAINT system_security_plans_metadata_id_fkey TO ssps_metadata_id_fkey; -- 2026-06-10
ALTER TABLE oscal.ssps RENAME CONSTRAINT system_security_plans_profile_id_fkey  TO ssps_profile_id_fkey;  -- 2026-06-10
-- Child->parent FKs (name was truncated from old child table name):
ALTER TABLE oscal.ssp_control_implementations RENAME CONSTRAINT system_security_plan_control_imple_system_security_plan_id_fkey TO ssp_control_implementations_system_security_plan_id_fkey; -- 2026-06-10
ALTER TABLE oscal.ssp_system_characteristics  RENAME CONSTRAINT system_security_plans_system_chara_system_security_plan_id_fkey  TO ssp_system_characteristics_system_security_plan_id_fkey;  -- 2026-06-10
-- (FKs on ssp_components / ssp_inventory_items / ssp_leveraged_authorizations / ssp_control_implementation_objectives
--  use column ssp_id / control_implementation_id in their auto-name, NOT the renamed parent table name → no rename.)

-- ===== 7. Cross-schema FK from compliance.project_system_characteristic_mapping =====
-- Auto-follows the rename (references by OID); its constraint name embeds the CHILD table + column,
-- not the renamed parent, so no rename needed. Verified in Step 3 below.

-- ===== 8. Record migration =====
INSERT INTO public.schema_migrations (version, description, applied_at)
VALUES ('2026-06-10-ssp-table-rename', 'Rename system_security_plans* tables to ssp(s)_*', now())
ON CONFLICT DO NOTHING; -- 2026-06-10
```
> NOTE: confirm the exact `schema_migrations` column signature before finalizing — `grep -rn "INSERT.*schema_migrations" scripts/sql/ | tail -3` and copy the latest pattern (it may be `(version)` only).

- [ ] **Step 2: Verify the auto-named FK/pkey/key strings match THIS DB's reality** (names can differ per-env if created at different times). Generate the truth from the catalog before trusting the hardcoded names above:
```bash
PGPASSWORD=<查.env> psql -h 192.168.50.188 -p 25432 -U cmmgr -d guidant_ai_dev -tA -c "
SELECT conname FROM pg_constraint
 WHERE conrelid IN ('oscal.system_security_plans'::regclass,
                    'oscal.system_security_plan_control_implementations'::regclass,
                    'oscal.system_security_plans_system_characteristics'::regclass)
 ORDER BY 1;"
```
Reconcile any mismatch between this output and the constraint names in the migration. If a name differs, fix the migration (NOT the DB).

### Task 12: Dry-run the migration in a rollback transaction (no commit)

- [ ] **Step 1: Run inside BEGIN…ROLLBACK to prove it applies cleanly without persisting**
```bash
PGPASSWORD=<查.env> psql -h 192.168.50.188 -p 25432 -U cmmgr -d guidant_ai_dev -v ON_ERROR_STOP=1 <<'EOF'
BEGIN;
\i /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be/scripts/sql/2026-06-10-ssp-table-rename.sql
-- confirm new names exist within the txn:
SELECT relname FROM pg_class WHERE relname IN ('ssps','ssp_control_implementations','ssp_system_characteristics') ORDER BY 1;
ROLLBACK;
EOF
```
Expected: every `ALTER` succeeds, the SELECT returns the three new names, then ROLLBACK leaves DB untouched. Any error → fix migration, re-run. Do NOT proceed to Phase 4 until this is clean.

---

## Phase 4 — DEV: apply + verify lockstep

### Task 13: Apply migration to DEV

- [ ] **Step 1: Apply for real** (single transaction, ON_ERROR_STOP):
```bash
PGPASSWORD=<查.env> psql -h 192.168.50.188 -p 25432 -U cmmgr -d guidant_ai_dev --single-transaction -v ON_ERROR_STOP=1 -f /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be/scripts/sql/2026-06-10-ssp-table-rename.sql
```
Expected: `COMMIT`, no errors.

- [ ] **Step 2: Verify new table names + preserved row counts**
```bash
PGPASSWORD=<查.env> psql -h 192.168.50.188 -p 25432 -U cmmgr -d guidant_ai_dev -tA -c "
SELECT 'ssps', count(*) FROM oscal.ssps
UNION ALL SELECT 'ssp_control_implementations', count(*) FROM oscal.ssp_control_implementations
UNION ALL SELECT 'ssp_system_characteristics', count(*) FROM oscal.ssp_system_characteristics;"
```
Expected: ~181 / ~2312 / ~180 (data preserved). Old names should now error if queried.

- [ ] **Step 3: Verify all FKs still VALID (rename didn't break integrity)**
```bash
PGPASSWORD=<查.env> psql -h 192.168.50.188 -p 25432 -U cmmgr -d guidant_ai_dev -tA -c "
SELECT conname, convalidated FROM pg_constraint
 WHERE contype='f' AND (confrelid='oscal.ssps'::regclass
    OR conrelid IN ('oscal.ssps'::regclass,'oscal.ssp_control_implementations'::regclass,'oscal.ssp_system_characteristics'::regclass));"
```
Expected: all `convalidated = t`.

### Task 14: Restart BE on renamed ORM + smoke

- [ ] **Step 1: Ask the user to restart BE** (per CLAUDE.md — Claude does not start services). Message: "請重啟 BE（吃 jedi-oscal path-dep 的新 ORM）". The BE now runs ORM that points at `ssps` etc.; the DB now has `ssps` etc. — lockstep complete.

- [ ] **Step 2: Boot check — no mapper errors in log**
Run: `tail -100 /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be/log/app.log | grep -iE "NoReferencedTable|Traceback|ERROR|system_security"`
Expected: no `NoReferencedTableError`, no mapper init failure.

- [ ] **Step 3: Run the SSP-touching test suite** (pick the files scoping flagged + general SSP):
```bash
cd /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be
pytest test/test_module_frame_template_copy_service.py tests/test_module_frame_template_copy_service_phase2.py test/test_oscal_project_service_template_copy_integration.py -v
```
Expected: PASS. Note: these tests have assertions matching the OLD table names (scoping flagged `test/test_module_frame_template_copy_service.py:73`, integration test lines 56/59/306/325/348/361, phase2 lines 41/55). They WILL fail until updated — update those assertions to the new names as part of this step, then re-run to green. (This is the "failing test → fix → pass" loop for the rename.)

  ⚠️ **Substring-collision trap when updating assertions** — do these replacements by exact full-string, NOT a blind `sed`:
  - `system_security_plans` (table) → `ssps`, BUT the column `system_security_plan_id` (singular `_plan_id`, no trailing `s`) must stay. A `s/system_security_plans/ssps/g` is safe (column has no `s` after `plan`); a `s/system_security_plan/ssp/g` would CORRUPT the column — never use the singular form.
  - `system_security_plans_system_characteristics` → `ssp_system_characteristics` must run BEFORE / be matched more-specifically than `system_security_plans` → `ssps` (the latter is a substring of the former). Replace the longest names first.
  - Same trap applies to `test_oscal_project_service_template_copy_integration.py` which mixes table strings (L59/325/361) and column strings (L134/140/473/485/518/523) in one file.

- [ ] **Step 4: Manual end-to-end smoke** — exercise a path that hits all three tables:
  Trigger an OSCAL project start / module-frame template copy (the `_copy_*` flow that runs the raw SQL in Task 9), then verify rows land in `oscal.ssp_control_implementations` and `oscal.ssp_system_characteristics`. Confirm via `tail -f log/app.log` shows no error and a follow-up SELECT shows new/updated rows.

- [ ] **Step 5: Commit BE code + migration together** (lockstep; explicit adds, no `-am`):
```bash
cd /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be
git add infra/associations/model/project_system_characteristic.py app/module_frame/service/module_frame_template_copy_service.py scripts/sql/2026-06-10-ssp-table-rename.sql test/test_module_frame_template_copy_service.py tests/test_module_frame_template_copy_service_phase2.py test/test_oscal_project_service_template_copy_integration.py
git commit -m "refactor(ssp): rename system_security_plans* tables to ssp(s)_* (BE code + migration)"
```
(Do NOT include the `pyproject.toml` path-dep change. Do NOT push — user pushes.)

**🛑 CHECKPOINT — STOP HERE.** Per CLAUDE.md, fix/change commits stop before closing. Give the user a one-line status + the manual smoke checklist. Do NOT auto-run Phase 5/6. Wait for explicit user command ("STG 套下去" / "發版" / "收尾").

---

## Phase 5 — STG / POC / PROD rollout (USER-GATED, one env at a time)

> Each env = apply migration + deploy code that runs the new ORM, in lockstep. STG & DEV share server 188 (different DB). POC = 189. PROD per deploy docs. ⚠️ App and DB MUST move together per env — old app + renamed table crashes (see `feedback_cross_schema_fk_must_qualify`, `feedback_rebuild_docker_image_after_schema_move`).

### Task 15: STG (guidant_ai_stg @ 188)
- [ ] Apply migration (same command, `-d guidant_ai_stg`). First re-run Task 11 Step 2's constraint-name verification against STG (names may differ).
- [ ] Confirm deployed STG app image/process runs jedi-oscal with the new ORM (pin the published version or matching build) BEFORE/with the migration.
- [ ] Verify row counts + FK validity.

### Task 16: POC (guidant_ai_poc @ 189)
- [ ] Same as STG, `-h 192.168.50.189 -d guidant_ai_poc`. Re-verify constraint names against POC first (189 was migrated separately).
- [ ] Ensure any POC-side container with bundled SQL is rebuilt if it references these tables.

### Task 17: PROD
- [ ] Schedule with user; apply per deploy runbook; same lockstep + verification.

---

## Phase 6 — Closing (USER-GATED — only after user says 收尾/發版)

### Task 18: Publish jedi-oscal + restore BE pin
- [ ] Bump jedi-oscal `pyproject.toml` version (patch), commit in jedi-oscal repo.
- [ ] Publish to Nexus: `cd .../jedi-oscal && yes | poetry publish --build -r nexus` (irreversible — confirm version first).
- [ ] In BE `pyproject.toml`: revert path-dep → pin the new published version. `poetry update jedi-oscal`. Commit the pin change.
- [ ] (git push of jedi-oscal to gitlab is user's — Claude lacks creds; Nexus publish works on separate auth.)

### Task 19: Batch doc + remaining-string cleanup
- [ ] Update remaining BE prose comments/docstrings (non-runtime, deferred from Task 10): `app/oscal/service/ssp_excel_import_app_service.py:970,2168`, `app/oscal/service/ssp_versioning_service.py:4-5`, `domain/oscal/service/ssp_shell_service.py:124`, `infra/module_frame/models/module_frame_control_default.py:22`, `infra/grc/model/ssp_reference_document_mapping.py:15`.
- [ ] Update doc-gen scripts + reference docs: `docs/system-design/scripts/generate_db_schema_docx.py`, `docs/system-design/database/scripts/gen_compliance_schema_overview.py`, `docs/api/oscal/generate_docx.py`, `scripts/gen_erd.py`, `docs/claude/database-schema.md`, and the `~1290` markdown mentions (use the longest-name-first substring rule from Task 14; do NOT rewrite historical changelog/conversation-history — only living reference docs). (NOTE: `scripts/smoke_test_ssp_docx_parser.py` is NOT here — its runtime SQL was already fixed in Task 9 Step 5.)
- [ ] Regenerate ERD / schema docs if those generators are part of the doc pipeline.

### Task 20: Changelog + closing docs (per closing-and-handoff skill)
- [ ] Write `docs/changelog/` entry (type=tweak/refactor): table rename, lockstep note, 4-env rollout status.
- [ ] Update `docs/claude/database-schema.md` table names.
- [ ] Notion issue tracker entry (per `feedback_closing_writes_notion_issue_tracker`).

---

## Risk Register

| Risk | Mitigation |
|------|-----------|
| Old app hits renamed table (or vice-versa) → 500 / NoReferencedTableError | Lockstep: migration + new-ORM deploy together per env (Task 14, Phase 5) |
| Hardcoded constraint/index names differ per env (created at different times) | Task 11 Step 2 + Task 15/16 re-verify names from catalog before each env |
| Missed raw-SQL reference (grep-invisible at runtime) | Task 10 grep gate scoped to api/app/domain/infra; manual e2e smoke (Task 14 Step 4) |
| `schema_migrations` insert shape wrong | Task 11 Step 1 NOTE: copy latest existing INSERT pattern |
| jedi-oscal used by an unknown downstream consumer | Scoping confirmed zero refs in the monorepo; if external consumers exist, they pin old version until they upgrade (table rename is a breaking schema change — call it out in the version bump notes) |
| Accidentally renaming a column | Scope Boundaries section: columns explicitly excluded; reviewer must check no column edits |
```