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.

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).

• 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.

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.

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.

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:

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):

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):

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.)

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:

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.

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):

-- 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:

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

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.

Task 13: Apply migration to DEV

• Step 1: Apply for real (single transaction, ON_ERROR_STOP):

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

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)

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):

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):

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 套下去" / "發版" / "收尾").

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.

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).