ApplicationInformation schema drift from ESPI 4.0 XSD across MySQL/PostgreSQL/H2 V1 migrations

[dfcoffin]

Summary

The Flyway V1_0_0__create_oauth2_schema.sql migrations for MySQL, PostgreSQL, and H2 all drift from the canonical ESPI 4.0 ApplicationInformation element defined in openespi-common/src/main/resources/schema/ESPI_4.0/espi.xsd (lines 53-249). V3's INSERT statement (V3_0_0__add_default_data_and_test_clients.sql) compounds the problem by referencing column names that exist in neither V1 schema nor the XSD.

Discovered during Phase 2.0 auth-server bring-up (#122). Workaround in place on feature/issue-122-auth-server-bringup branch: application-dev-mysql.yml sets spring.flyway.target: 2.0.0 to skip V3+ pending this repair.

Canonical schema per espi.xsd (35 fields + 5 inherited from IdentifiedObject)

XSD element	DB type	Cardinality
id (UUID5 from IdentifiedObject)	CHAR(36) PRIMARY KEY	1
description, selfLink, upLink, published, updated	from IdentifiedObject	0..1
dataCustodianId	VARCHAR(64)	1
dataCustodianApplicationStatus (enum)	VARCHAR(20)	1
thirdPartyApplicationDescription	VARCHAR(256)	0..1
thirdPartyApplicationStatus, thirdPartyApplicationType, thirdPartyApplicationUse (enums)	VARCHAR(20) each	0..1
thirdPartyPhone	VARCHAR(32)	0..1
authorizationServerUri	VARCHAR(2000)	0..1
thirdPartyNotifyUri	VARCHAR(2000)	1
authorizationServerAuthorizationEndpoint	VARCHAR(2000)	1
authorizationServerRegistrationEndpoint	VARCHAR(2000)	0..1
authorizationServerTokenEndpoint	VARCHAR(2000)	1
dataCustodianBulkRequestURI	VARCHAR(2000)	1
dataCustodianResourceEndpoint	VARCHAR(2000)	1
thirdPartyScopeSelectionScreenURI (DEPRECATED)	VARCHAR(2000)	0..1
thirdPartyUserPortalScreenURI	VARCHAR(2000)	0..1
client_secret	VARCHAR(512)	1
logo_uri	VARCHAR(2000)	0..1
client_name	VARCHAR(256)	1
client_uri	VARCHAR(2000)	0..1
redirect_uri	child table	1..n
client_id	VARCHAR(64) UNIQUE	1
tos_uri, policy_uri	VARCHAR(2000) each	0..1
software_id	VARCHAR(256)	1
software_version	VARCHAR(32)	1
client_id_issued_at, client_secret_expires_at	BIGINT (epoch seconds, NOT TIMESTAMP)	1
contacts	child table	0..n
token_endpoint_auth_method (enum)	VARCHAR(50)	1
scope	child table	1..n
grant_types (enum)	child table	2..n (XSD minOccurs=2)
response_types (enum)	VARCHAR(50)	1
registration_client_uri	VARCHAR(2000)	1
registration_access_token	VARCHAR(2000)	1
dataCustodianScopeSelectionScreenURI (DEPRECATED)	VARCHAR(2000)	0..1

Required child tables (cardinality > 1 in XSD):

• espi_application_info_redirect_uris (app_info_id, redirect_uri)
• espi_application_info_contacts (app_info_id, contact)
• espi_application_info_scopes (app_info_id, scope)
• espi_application_info_grant_types (app_info_id, grant_type)

Drift by vendor

MySQL V1 (db/vendor/mysql/V1_0_0)

• ❌ Missing core fields: scope, grant_types, response_types, contacts, authorizationServerAuthorizationEndpoint, authorizationServerRegistrationEndpoint, authorizationServerTokenEndpoint
• ❌ No uuid (UUID5) — uses bare id BIGINT AUTO_INCREMENT; loses ESPI Atom identity
• ❌ redirect_uri missing (XSD requires 1..n; needs child table)
• ❌ client_id_issued_at/client_secret_expires_at typed as TIMESTAMP instead of BIGINT (XSD TimeType is long epoch)
• ✓ Has ESPI-correct names where present (third_party_application_description, third_party_phone)

PostgreSQL V1 (db/vendor/postgresql/V1_0_0)

• ❌ Non-ESPI column names: client_description (XSD: thirdPartyApplicationDescription), contact_name/contact_email (XSD: contacts child table)
• ❌ scope/grant_types/response_types as single VARCHAR — wrong cardinality (XSD requires child tables)
• ❌ redirect_uri as single VARCHAR — wrong cardinality
• ❌ BIGSERIAL id + separate uuid VARCHAR(36) — should use uuid as PK per ESPI
• ✓ Has more OAuth fields than MySQL V1

H2 V1 (db/vendor/h2/V1_0_0)

• Not yet audited; almost certainly drifts from both above

V3 INSERT (V3_0_0__add_default_data_and_test_clients.sql)

• ❌ References non-ESPI columns: client_description, contact_name, contact_email
• ❌ Inserts into scope, grant_types, response_types as if they were scalar (XSD: child tables)
• ⚠️ Includes legitimate V2 admin extensions (espi_version, security_classification, etc.) mixed with the drift — keep these, but split from XSD-aligned data

V2 (V2_0_0__add_espi4_compliance_enhancements.sql)

• ✅ Legitimate scope — operational/security extension columns beyond the XSD (rate limits, certification status, audit). No change needed.

Scope of repair

1. Rewrite V1 for all three vendors to define the canonical 35-field espi_application_info schema + 4 child tables, with the XSD's exact field names and cardinalities. UUID5 PK.
2. Rewrite V3 INSERT to use ESPI-compliant column names and insert child-table rows for redirect_uri, contacts, scope, grant_types.
3. Verify V4/V5/V6 don't also drift (haven't been audited yet for this issue).
4. Update ApplicationInformationEntity JPA mapping in openespi-common if needed to match the new schema (UUID5 PK, child collections via @ElementCollection or @OneToMany).
5. Update EspiApplicationInfo mapping in openespi-authserver repository code.
6. Update MapStruct mapper between entity and DTO.

Estimated effort

4–6 hours for migration rewrite + regression test, plus follow-on JPA/mapper updates if column names changed.

Acceptance criteria

• MySQL, PostgreSQL, H2 V1 migrations produce identical logical schemas matching espi.xsd ApplicationInformation definition (35 fields + 4 child tables)
• All 6 V_*_0 migrations apply cleanly against a clean MySQL, PostgreSQL, and H2 (no target workaround needed)
• V3 INSERT statements use ESPI-compliant column names
• application-dev-mysql.yml flyway.target: 2.0.0 workaround removed
• ApplicationInformationEntity JPA mapping aligned with new schema
• One integration test mints a token, fetches ApplicationInformation as XML, validates against espi.xsd

Blocks

• #122 Phase 2.0 acceptance — bring-up demo can proceed with the target: 2.0.0 workaround, but Phase 2 close-out should not happen until this is repaired.

Related

• ESPI 4.0 spec: openespi-common/src/main/resources/schema/ESPI_4.0/espi.xsd lines 53-249
• Workaround branch: feature/issue-122-auth-server-bringup