Complete #123 schema-repair sync: align AS V1 schema with application code column names

[dfcoffin]

Summary

Follow-up to #123 (AS schema repair). The Java-side rename appears to have landed but the Flyway V1 schema was never updated to match, leaving a permanent column-name disagreement between the application code and the actually-applied schema. This blocks V3+ Flyway migrations and breaks every AS admin endpoint that touches espi_application_info and related ESPI metadata tables.

How this was discovered

Bringing up AS against MySQL with target: "7.0.0" (needed for the new V7 handoff_nonces migration in PR #122 PR C4) caused V3 to fail:

SQL State  : 42S22
Error Code : 1054
Message    : Unknown column 'client_description' in 'field list'
Location   : db/vendor/mysql/V3_0_0__add_default_data_and_test_clients.sql
Line       : 77

Investigating turned up a three-way disagreement around the espi_application_info table:

View	Column name
V1_0_0__create_oauth2_schema.sql (line ~140)	third_party_application_description
V3_0_0__add_default_data_and_test_clients.sql (line 77) INSERT	client_description
OAuth2ClientManagementController.java INSERT	client_description
ConsentService.java, ClientCertificateService.java, ClientMetricsService.java JdbcTemplate code	(similar drift, not yet enumerated)

The Java code and V3 agree on the intended post-repair column names. V1 is the outlier — the schema repair work flagged in #123 updated the Java side but never landed the corresponding rename in V1 (or didn't ship a V8 to rename the columns in-place).

Why it stayed hidden

application-dev-mysql.yml capped Flyway at target: "2.0.0" with the comment "Skip V3+ pending ESPI 4.0 XSD-aligned schema repair (see issue #123). V1+V2 provide enough for OAuth2 grant + introspection; V3 onwards is seed/demo data." That cap hid the V3 failure from local dev and from CI's MySQL integration tests. The runtime impact (admin endpoints failing on client_description SELECT/INSERT) only triggers when an operator hits those endpoints.

Impact

• Local bring-up of AS requires either keeping the V2 cap (which blocks PR C4's structurally-required V7 handoff_nonces table) or disabling V3-V6 (current workaround in PR C4).
• Admin / management endpoints unusable — any caller hitting OAuth2ClientManagementController's INSERT INTO espi_application_info, ConsentService's consent queries, etc. gets a SQLSyntaxErrorException for the renamed columns.
• V3 seed data (sample clients like green_button_connect_mydata, utility_admin_portal, audit log examples, metrics seed rows) is never applied. Required clients (third_party, datacustodian_admin, third_party_admin) are unaffected because the programmatic seeder in AuthorizationServerConfig.initializeDefaultClients handles them.
• V4-V6 are likely affected by the same kind of drift but haven't been confirmed since V3 fails first. Per PR C4 they're also disabled for now.

Tables that need audit

V3-V6 reference these tables; each needs Entity-class (where present) ↔ V1+V2 schema ↔ application-code-SQL alignment:

• espi_application_info (V1, V3 INSERT) — column drift confirmed
• oauth2_audit_log (V1 or V2, V3 INSERTs) — not yet audited
• oauth2_consent_details (V1 or V2, V3 INSERTs) — not yet audited
• oauth2_client_metrics (V1 or V2, V3 INSERTs) — not yet audited
• datacustodian_integration_mapping (V4 CREATE) — table itself never used; can it be removed?
• oauth2_user_info (V5 CREATE) — OIDC support; commit e31a617 ("strip unused OIDC/JWK/JWT") stripped the consuming code. Probably delete.
• oauth2_client_certificates (V6 CREATE) — cert auth; ClientCertificateService references it but the flow isn't wired into the customer OAuth path. Decide: revive or delete.

Recommended fix shape

Either:

Option A — write a V8 migration that renames V1's stale columns to the application's expected names.

• Cleanest for production deployments that already have V1 applied with the old names — Flyway can apply V8 forward without breaking schema_history.
• V3-V6 can then be re-enabled after fixing their other issues (column-count mismatches, JSON serialization quirks).

Option B — edit V1 in place + revoke V3-V6 + restart from baseline.

• Cleaner-looking but breaks every existing deployment that already ran V1. Only viable if no environment has V1 applied yet (unknown).

Strongly recommend Option A plus a focused rewrite of V3 to insert sample data that the current application code can actually read. V4-V6 should be evaluated for relevance and either deleted (if their features are out of scope per the OIDC strip) or fixed and revived.

Out of scope for this issue

• The C4 work itself (Phase 2.0 — Stand up auth-server in dev with opaque tokens + subscription-bound introspection (Phase 2 precursor) #122). PR C4 ships a workaround (.disabled extensions on V3-V6, target bumped to 7.0.0) so the customer-flow testing can proceed; that workaround should be removed once this issue lands.
• The original ApplicationInformation schema drift from ESPI 4.0 XSD across MySQL/PostgreSQL/H2 V1 migrations #123 ESPI 4.0 XSD-alignment work that triggered the partial repair.

Acceptance

• V1 schema columns match the names application code expects (or a V8 rename migration is in place).
• V3 sample-data INSERTs succeed against the aligned schema, OR V3 is replaced with seed data that reflects current ESPI 4.0 design.
• V4-V6 are reviewed and either repaired + re-enabled or deleted with rationale.
• application-dev-mysql.yml Flyway target line is removed (no cap needed).
• PR C4's .disabled extensions on V3-V6 are removed (or those files are deleted).
• OAuth2ClientManagementController admin endpoints round-trip cleanly against MySQL.