myep/docs/bpn-connect-query-reference.md

bpn-connect Query Reference

Scope: endpoint design and the XML→SQL migration plan for the bpn-connect Go proxy. For schema semantics, conventions and confidence marks see docs/buspro-database-reference.md, which is authoritative and wins on any conflict. The table descriptions below are kept for endpoint context.

This document summarizes the BusProNet schema exports in docs/buspronet-schema for future bpn-connect endpoint/query development.

Generated from:

• docs/buspronet-schema/BusPronet_20260601-1517.csv: table columns
• docs/buspronet-schema/BusPronet_20260601-1518.csv: foreign keys

Current limitation: the visible exports do not include primary-key or index catalog data, even though those were expected. Identity flags are not a reliable primary-key substitute in this database because many ID... columns are not marked as identity. Add PK/index exports before finalizing performance-sensitive queries.

Naming Conventions

• Primary identifiers usually follow ID<TableName>, for example Buchung.IDBuchung, Adresse.IDAdresse, AdressePerson.IDAdressePerson.
• Foreign key columns usually include _FS, for example BuchungTN.IDBuchung_FS.
• Address data is split into:
  • Adresse: address/customer account root.
  • AdressePerson: person attached to an address.
  • AdresseAnschrift: postal address.
  • AdresseKommunikation: communication values such as email/phone.
• Booking data is split into:
  • Buchung: booking header.
  • BuchungTN: booking participants.
  • BuchungTNFerienziel, BuchungTNLeistung: participant travel/service rows.
• Public booking number appears to be Buchung.Vorgang.
• Internal booking id is Buchung.IDBuchung.
• Many tables have Status fields. Based on observed data, Status = 'L' appears to be a soft-delete marker and should be excluded from portal lookup queries. Other status values still need table-specific business validation.

Core Tables

Buchung

Booking header. Important columns:

Column	Type	Nullable	Notes
IDBuchung	bigint	no	Internal booking id.
Vorgang	nvarchar(100)	no	Public booking number/reference.
IDReise_FS	bigint	yes	Links to Reise.IDReise; no FK was present in the export.
IDAdresse_FS_Anmelder	bigint	yes	Applicant address id; no FK was present in the export.
IDAdresseAnschrift_FS_Anmelder	bigint	yes	Applicant postal address id; no FK was present in the export.
IdAdressePerson_FS_Anmelder	bigint	yes	Applicant person id; FK exists.
IDAdresse_FS_Agentur	bigint	no	Agency address; FK exists.
IDAdresse_FS_Mandant	bigint	no	Tenant/client address; FK exists.
LoginWert1	nvarchar(100)	no	Possible login-related value; semantics unknown.
LoginWert2	nvarchar(100)	no	Possible login-related value; semantics unknown.
LoginWert3	nvarchar(100)	no	Possible login-related value; semantics unknown.

Known exported foreign keys:

• Buchung.IdAdressePerson_FS_Anmelder -> AdressePerson.IDAdressePerson
• Buchung.IDAdresse_FS_Agentur -> Adresse.IDAdresse
• Buchung.IDAdresse_FS_ErsterSachb -> Adresse.IDAdresse
• Buchung.IDAdresse_FS_LetzterSachb -> Adresse.IDAdresse
• Buchung.IDAdresse_FS_Mandant -> Adresse.IDAdresse

Notable missing FKs in the export:

• Buchung.IDAdresse_FS_Anmelder
• Buchung.IDAdresseAnschrift_FS_Anmelder
• Buchung.IDReise_FS

Treat those as likely relationships but verify against real data.

BuchungTN

Booking participant.

Column	Type	Nullable	Notes
IDBuchungTN	bigint	no	Participant row id.
IDBuchung_FS	bigint	no	Booking header id.
IDAdressePerson_FS	bigint	no	Participant person id.
IDAdresseAnschrift_FS	bigint	yes	Participant postal address id.
Status	nvarchar(2)	no	Participant status; values need business validation.
BuchungsDatum	datetime	no	Booking date.
StornoStatusKV	nvarchar(2)	yes	Cancellation-related status.
StornoDatum	datetime	yes	Cancellation date.
Position	smallint	no	Participant position within booking.

Known exported foreign keys:

• BuchungTN.IDBuchung_FS -> Buchung.IDBuchung
• BuchungTN.IDAdressePerson_FS -> AdressePerson.IDAdressePerson

Adresse

Customer/address root.

Column	Type	Nullable	Notes
IDAdresse	bigint	no	Address/customer id needed by the local user record.
SuchName	nvarchar(402)	no	Search/display name.
Status	nvarchar(2)	no	Address status; active values need validation.
Art	tinyint	no	Address type; values need validation.
IDAdresseAnschrift_FS_Versandanschrift	bigint	no	Default shipping/postal address.
IDAdressePerson_FS_ReferenzM	bigint	no	Reference person.
DatumLöschungDSGVO	datetime	yes	GDPR deletion marker. Exclude rows where this is not null for portal access.

AdressePerson

Person attached to an address.

Column	Type	Nullable	Notes
IDAdressePerson	bigint	no	Person id needed by the local user record.
IDAdresse_FS	bigint	no	Parent address id.
Name	nvarchar(200)	no	Last name.
Vorname	nvarchar(200)	yes	First name.
Geschlecht	nvarchar(2)	no	Gender/salutation code.
Status	nvarchar(2)	no	Person status; active values need validation.
Passwort	nvarchar(200)	no	Legacy BusProNet password field. Do not use from bpn-connect; password migration remains on the BusProNet XML API until the algorithm is known.
Registrierung	bit	no	Registration flag; semantics need validation.
IDFremd	nvarchar(100)	no	External id field.

Known exported foreign key:

• AdressePerson.IDAdresse_FS -> Adresse.IDAdresse

AdresseKommunikation

Communication values for an address/person.

Column	Type	Nullable	Notes
IDAdresseKommunikation	bigint	no	Communication row id.
IDAdresse_FS	bigint	no	Address id.
IDAdressePerson_FS	bigint	no	Person id. No FK was present in the export, but the name strongly implies AdressePerson.IDAdressePerson.
Wert	nvarchar(512)	no	Communication value, likely email/phone depending on type.
WertTelefonie	nvarchar(100)	yes	Phone-specific normalized value.
IDArtKommunikation_FS	bigint	no	Communication type.
Status	nvarchar(2)	no	Communication status; active values need validation.
Newsletter	bit	no	Newsletter flag.
DoubleOptIn	bit	no	Double opt-in flag.

Known exported foreign keys:

• AdresseKommunikation.IDAdresse_FS -> Adresse.IDAdresse
• AdresseKommunikation.IDArtKommunikation_FS -> ArtKommunikation.IDArtKommunikation

Important missing FK:

• AdresseKommunikation.IDAdressePerson_FS is not exported as a FK. Join it to AdressePerson.IDAdressePerson only after validating with real data.

ArtKommunikation

Communication type lookup.

Column	Type	Nullable	Notes
IDArtKommunikation	bigint	no	Type id.
Langtext	nvarchar(200)	yes	Long label.
Kurztext	nvarchar(40)	yes	Short label.
Code	nvarchar(20)	yes	Type code. Find the email code/id here.
Status	nvarchar(2)	yes	Type status.

Before relying on email lookup queries, run:

SELECT IDArtKommunikation, Langtext, Kurztext, Code, Status
FROM dbo.ArtKommunikation
ORDER BY IDArtKommunikation;

Then hard-code or configure the email type ids/codes in bpn-connect.

AdresseAnschrift

Postal address.

Column	Type	Nullable	Notes
IDAdresseAnschrift	bigint	no	Postal address id.
IDAdresse_FS	bigint	no	Address id.
IDAdressePerson_FS	bigint	yes	Person id.
Anrede	nvarchar(100)	yes	Salutation.
Name	nvarchar(400)	yes	Postal name.
Strasse	nvarchar(200)	no	Street.
PLZ	nvarchar(20)	no	Postal code.
Ort	nvarchar(200)	no	City.
Status	nvarchar(2)	no	Postal address status.

No direct FK from AdresseAnschrift to Adresse or AdressePerson was present in the export subset shown by the current FK file. Treat these as likely relationships and validate them with data.

Reise and Produkt

Travel/product metadata.

Reise important columns:

• IDReise
• IDProdukt_FS
• Bezeichnung
• Code
• TerminVon
• TerminBis
• Status
• StornoDatum

Produkt important columns:

• IDProdukt
• Bezeichnung
• Code
• Suchfeld
• Status

Known exported foreign key:

• Reise.IDProdukt_FS -> Produkt.IDProdukt

Relationship Map

Most relevant joins for portal access:

Buchung.IDBuchung
  -> BuchungTN.IDBuchung_FS
  -> BuchungTN.IDAdressePerson_FS
  -> AdressePerson.IDAdressePerson
  -> AdressePerson.IDAdresse_FS
  -> Adresse.IDAdresse

Applicant joins:

Buchung.IdAdressePerson_FS_Anmelder
  -> AdressePerson.IDAdressePerson
  -> AdressePerson.IDAdresse_FS
  -> Adresse.IDAdresse

Email joins to validate:

AdresseKommunikation.IDAdressePerson_FS
  -> AdressePerson.IDAdressePerson

AdresseKommunikation.IDAdresse_FS
  -> Adresse.IDAdresse

AdresseKommunikation.IDArtKommunikation_FS
  -> ArtKommunikation.IDArtKommunikation

Required bpn-connect Endpoints

All endpoints are private and authenticated by API key:

X-API-Key: {apiKey}
Accept: application/json

Keep responses narrow. bpn-connect should return only fields needed by the Symfony portal and should never return BusProNet passwords, bank data, credit card data, or broad customer records by default.

Authentication rule: Symfony owns portal passwords after a user has been bootstrapped locally. The BusProNet legacy password algorithm is currently unknown, so bpn-connect must not verify passwords from the database. Keep the existing BusProNet XML password check only for first-time legacy migration. Once the local Symfony password hash exists, all subsequent login/profile refresh logic should use local authentication plus bpn-connect read endpoints.

GET /api/bookings/applicant-lookup

Purpose: resolve an applicant by email and public booking number. This is the core endpoint for local user creation after a booking and for the portal access request flow.

Request:

GET /api/bookings/applicant-lookup?email={email}&bookingNumber={vorgang}

Response 200:

{
  "bookingNumber": "111386",
  "bookingId": 123456,
  "addressId": 98765,
  "personId": 54321,
  "email": "[email protected]",
  "firstName": "Example",
  "lastName": "Customer"
}

Response rules:

• Return 200 when email belongs to Buchung.IdAdressePerson_FS_Anmelder for the given Buchung.Vorgang.
• Return 404 when there is no matching non-deleted applicant.
• Return 409 if the query returns more than one applicant row after deduplication. This should be rare and indicates inconsistent BusProNet data.
• Normalize input email by trimming and lowercasing before querying.

SQL:

DECLARE @Email nvarchar(512) = LOWER(LTRIM(RTRIM(@email)));
DECLARE @Vorgang nvarchar(100) = LTRIM(RTRIM(@bookingNumber));

SELECT TOP (2)
    b.Vorgang AS bookingNumber,
    b.IDBuchung AS bookingId,
    a.IDAdresse AS addressId,
    ap.IDAdressePerson AS personId,
    @Email AS email,
    ap.Vorname AS firstName,
    ap.Name AS lastName
FROM dbo.Buchung b
INNER JOIN dbo.AdressePerson ap
    ON ap.IDAdressePerson = b.IdAdressePerson_FS_Anmelder
INNER JOIN dbo.Adresse a
    ON a.IDAdresse = ap.IDAdresse_FS
WHERE b.Vorgang = @Vorgang
  AND a.DatumLöschungDSGVO IS NULL
  AND a.Status <> 'L'
  AND ap.Status <> 'L'
  AND EXISTS (
      SELECT 1
      FROM dbo.AdresseKommunikation ak
      WHERE ak.IDAdresse_FS = ap.IDAdresse_FS
        AND ak.IDAdressePerson_FS = ap.IDAdressePerson
        AND LOWER(LTRIM(RTRIM(ak.Wert))) = @Email
        AND ak.Status <> 'L'
        -- TODO: restrict to validated email communication type.
        -- AND ak.IDArtKommunikation_FS IN (...)
  )
ORDER BY ap.IDAdressePerson;

GET /api/customers/{addressId}/persons/{personId}

Purpose: fetch the personal data for a known BusProNet person. Use this after a local user exists and already stores addressId and personId. Do not use email as the identifier because multiple Adresse rows can share an email address.

Request:

GET /api/customers/{addressId}/persons/{personId}

Response 200:

{
  "addressId": 98765,
  "personId": 54321,
  "firstName": "Example",
  "lastName": "Customer",
  "email": "[email protected]",
  "genderCode": "F",
  "title": null,
  "birthDate": "1980-01-01T00:00:00Z",
  "nationality": "deutsch",
  "nationalityCountryName": "Deutschland",
  "street": "Example Street 1",
  "postalCode": "12345",
  "city": "Example City",
  "addressCountryName": "Deutschland"
}

Response rules:

• Return 200 for a non-deleted address/person pair.
• Return 404 if the address/person pair does not exist or has Status = 'L'.
• Return only portal-editable or portal-visible fields.

SQL:

DECLARE @AddressId bigint = @addressId;
DECLARE @PersonId bigint = @personId;

SELECT
    a.IDAdresse AS addressId,
    ap.IDAdressePerson AS personId,
    ap.Name AS lastName,
    ap.Vorname AS firstName,
    ap.Geschlecht AS genderCode,
    ap.Titel AS title,
    ap.Geburtsdatum AS birthDate,
    ap.Briefanrede AS letterSalutation,
    ap.Anrede AS salutation,
    ap.Newsletter AS newsletter,
    email.Wert AS email,

    manifest.IDArtNationalitaet_FS AS nationalityId,
    person_nat.Langtext AS nationalityCountryName,
    person_nat.Kurztext AS nationality,
    person_nat.Code AS nationalityCode,

    aa.IDAdresseAnschrift AS postalAddressId,
    aa.Anrede AS postalSalutation,
    aa.Name AS postalName,
    aa.Strasse AS street,
    aa.PLZ AS postalCode,
    aa.Ort AS city,
    aa.Ortsteil AS district,
    aa.IDArtNationalitaet_FS_Land AS addressCountryId,
    address_country.Langtext AS addressCountryName,
    address_country.Kurztext AS addressCountryNationality,
    address_country.Code AS addressCountryCode,

    ad.TelefonOK AS phoneAllowed,
    ad.MailOK AS mailAllowed,
    ad.PostOK AS postAllowed
FROM dbo.AdressePerson ap
INNER JOIN dbo.Adresse a
    ON a.IDAdresse = ap.IDAdresse_FS
OUTER APPLY (
    SELECT TOP (1)
        ak.Wert
    FROM dbo.AdresseKommunikation ak
    WHERE ak.IDAdresse_FS = ap.IDAdresse_FS
      AND ak.IDAdressePerson_FS = ap.IDAdressePerson
      AND ak.Status <> 'L'
      -- TODO: restrict to validated email communication type.
      -- AND ak.IDArtKommunikation_FS IN (...)
    ORDER BY ak.IDAdresseKommunikation DESC
) email
LEFT JOIN dbo.AdresseAnschrift aa
    ON aa.IDAdresseAnschrift = ap.IDAdresseAnschrift_FS_Versandanschrift
   AND aa.Status <> 'L'
LEFT JOIN dbo.ArtNationalitaet address_country
    ON address_country.IDArtNationalitaet = aa.IDArtNationalitaet_FS_Land
   AND COALESCE(address_country.Status, '') <> 'L'
OUTER APPLY (
    SELECT TOP (1)
        m.IDArtNationalitaet_FS
    FROM dbo.AdressePersonManifest m
    WHERE m.IDAdressePerson_FS = ap.IDAdressePerson
      AND m.IDAdresse_FS = ap.IDAdresse_FS
      AND COALESCE(m.Status, '') <> 'L'
    ORDER BY m.IDAdressePersonManifest DESC
) manifest
LEFT JOIN dbo.ArtNationalitaet person_nat
    ON person_nat.IDArtNationalitaet = manifest.IDArtNationalitaet_FS
   AND COALESCE(person_nat.Status, '') <> 'L'
LEFT JOIN dbo.AdresseDatenschutz ad
    ON ad.IdAdresse_FS = a.IDAdresse
WHERE a.IDAdresse = @AddressId
  AND ap.IDAdressePerson = @PersonId
  AND a.DatumLöschungDSGVO IS NULL
  AND a.Status <> 'L'
  AND ap.Status <> 'L';

Optional Debug Endpoint: GET /api/customers/by-email

Purpose: manual investigation only. Email is not unique across Adresse, so this endpoint should return a list and should not be used as a local user identifier.

Request:

GET /api/customers/by-email?email={email}

Use the same joins as GET /api/customers/{addressId}/persons/{personId}, but start from AdresseKommunikation and return all non-deleted matching persons. Do not use this for account creation unless the user also provides a booking number or another disambiguating key.

Symfony Login And Bootstrap Replacement Plan

Current Symfony login behavior:

• Existing local users authenticate against the local Symfony password hash.
• Existing legacy local users may still have an encrypted legacy password in the local User.password column. Symfony validates it locally once, then replaces it with a Symfony password hash.
• If no local user exists, BpnAuthenticator::bootstrapUser() currently calls the BusProNet XML API with email and password:
  • getPersonalDataWithPassword(email, password)
  • getCrmAttributesWithPassword(email, password)
• After any successful local login, BpnAuthenticator::refreshBpnSnapshot() calls the BusProNet XML API by local addressId and personId:
  • getPersonalData(email, addressId, personId)
  • getCrmAttributes(email, addressId, personId)

Replacement rule:

• Keep login password validation local to Symfony.
• Keep the current XML password-based bootstrap for users that exist in BusProNet but do not yet have a local Symfony user.
• On successful XML legacy verification, Symfony creates a local user with a Symfony password hash from the submitted password.
• Future logins use only the local hash and must not call XML for password verification.
• Replace post-login snapshot refresh with bpn-connect id-based read endpoints.

Login Endpoint Usage

No bpn-connect endpoint is needed to authenticate a password.

Existing local user login should do:

1. Find local User by normalized email.
2. Validate local Symfony password hash.
3. If the local row still has a legacy encrypted local password, migrate it to a Symfony hash without contacting BusProNet.
4. Call GET /api/customers/{addressId}/persons/{personId} to refresh profile completeness.
5. Call GET /api/customers/{addressId}/crm-attributes to refresh roles and hotel codes.

Missing local user login should do:

1. Call the existing BusProNet XML password-based bootstrap.
2. If valid, create a local User with a Symfony hash of the submitted password.
3. Store returned addressId, personId, roles, hotelCodes, and profileComplete.
4. Persist and continue login.
5. If invalid, fail with the generic invalid-login message.

Do not add a bpn-connect password verification endpoint until the BusProNet password algorithm is known and validated against real examples. The observed database values do not currently match simple MD5/SHA/Base64 derivations.

GET /api/customers/{addressId}/crm-attributes

Purpose: replace XML SelektionCRM reads used during login refresh and by the OAuth CRM attributes endpoint.

Request:

GET /api/customers/{addressId}/crm-attributes

Minimal response:

{
  "roles": ["ROLE_CUSTOMER"],
  "hotelCodes": []
}

Detailed response may also include selection groups and CRM actions if the current /api/crm-attributes response must remain compatible.

Known role mapping from the XML parser:

• Selektionsstamm.IDSelektionsstamm = 1292 selected: ROLE_ADMIN and ROLE_HOUSE_MANAGER
• Selektionsstamm.IDSelektionsstamm = 1293 selected: ROLE_MANAGER
• Selektionsstamm.IDSelektionsstamm = 1070 selected: ROLE_TEAMER
• selected Selektionsstamm.Selektion matching Hausleitung {CODE}: ROLE_HOUSE_MANAGER and hotel code {CODE}
• admin users also receive default hotel code SSL
• no matched role means ROLE_CUSTOMER

Candidate SQL for selected customer selections:

DECLARE @AddressId bigint = @addressId;

SELECT
    sg.IDSelektionsgruppenstamm AS groupId,
    sg.Selektionsgruppe AS groupLabel,
    s.IDSelektionsstamm AS selectionId,
    s.Selektion AS selectionLabel,
    CAST(1 AS bit) AS selected
FROM dbo.Selektionen sel
INNER JOIN dbo.Selektionsstamm s
    ON s.IDSelektionsstamm = sel.IDSelektionsstamm_FS
LEFT JOIN dbo.Selektionsgruppe sg_map
    ON sg_map.IDSelektionsstamm_FS = s.IDSelektionsstamm
LEFT JOIN dbo.Selektionsgruppenstamm sg
    ON sg.IDSelektionsgruppenstamm = sg_map.IDSelektionsgruppenstamm_FS
WHERE sel.IDAdresse_FS = @AddressId
  AND COALESCE(s.Status, '') <> 'L'
  AND COALESCE(sg.Status, '') <> 'L'
ORDER BY sg.Position, sg.Selektionsgruppe, sg_map.Position, s.Selektion;

The Go service can derive roles and hotelCodes from this result with the mapping above. Before shipping, verify whether XML SelektionCRM includes unselected mutable options; if the portal only needs roles/hotel codes, selected rows are enough.

Candidate SQL for CRM actions if compatibility is required:

DECLARE @AddressId bigint = @addressId;

SELECT
    ca.IDCRMAktion AS id,
    ca.Code AS code,
    ca.Bezeichnung AS label,
    ca.Aktiv AS selected
FROM dbo.CRMZuweisung cz
INNER JOIN dbo.CRMAktion ca
    ON ca.IDCRMAktion = cz.IDCRMAktion_FS
WHERE cz.IDAdresse_FS = @AddressId
  AND ca.Status <> 'L'
ORDER BY ca.Bezeichnung;

This needs data validation against the XML response before compatibility is guaranteed.

XML Calls To Replace For Local User Processes

Current Symfony call	Current XML purpose	Replacement
getPersonalData(email, addressId, personId)	Load profile by known user ids.	GET /api/customers/{addressId}/persons/{personId}
updatePersonalData(email, personalData)	Persist profile edits.	Future PATCH /api/customers/{addressId}/persons/{personId} with transaction-safe updates.
getCrmAttributes(email, addressId, personId)	Refresh roles/hotel codes and expose CRM attributes.	GET /api/customers/{addressId}/crm-attributes
getPersonalDataWithPassword(email, password)	Missing-local-user bootstrap by BPN password.	Keep XML until the local user has been created/migrated.
getCrmAttributesWithPassword(email, password)	Roles for missing-local-user bootstrap by BPN password.	Keep XML until the local user has been created/migrated.
register(registrationData)	Create BusProNet address/person and local user.	Future POST /api/customers if direct DB writes are allowed; otherwise keep XML until write rules are known.
createAddress(personalData)	Create BusProNet address/person without mail.	Future POST /api/customers if direct DB writes are allowed; otherwise keep XML until write rules are known.

Recommended migration sequence:

1. Keep XML for legacy password bootstrap only.
2. Replace booking applicant lookup for guest provisioning and portal access.
3. Replace post-login profile/CRM refresh reads.
4. Replace personal-data page reads.
5. Replace OAuth/userinfo and CRM API reads.
6. Replace booking form prefill reads.
7. Defer personal-data writes until update rules are validated.
8. Defer registration/create-address until the insert graph is known.

Phase 1: Keep XML For Legacy Password Bootstrap

Keep these XML calls temporarily:

• getPersonalDataWithPassword(email, password)
• getCrmAttributesWithPassword(email, password)

Scope:

• Only used when no local Symfony user exists yet.
• On success, create the local user with a Symfony password hash from the submitted password.
• Store addressId, personId, roles, hotelCodes, and profileComplete.
• Future logins for that user use only the local Symfony password hash.

Do not implement a bpn-connect password verification endpoint until the BusProNet password algorithm is known and verified.

Phase 2: Booking Applicant Lookup

Replace XML/fragile booking lookup plumbing with:

GET /api/bookings/applicant-lookup?email={email}&bookingNumber={vorgang}

Symfony integration points:

• BookingGuestAccountManager::provisionGuestAccountForBooking()
• BookingGuestAccountManager::requestPortalAccess()
• booking-created welcome account provisioning

This endpoint returns only the fields needed for local user creation:

• bookingNumber
• bookingId
• addressId
• personId
• email
• firstName
• lastName

Phase 3: Post-login Snapshot Refresh

Replace XML reads in BpnAuthenticator::refreshBpnSnapshot() with:

GET /api/customers/{addressId}/persons/{personId}
GET /api/customers/{addressId}/crm-attributes

Symfony keeps local password validation. bpn-connect only refreshes ids, profile completeness, roles, and hotel codes after the password has already been validated locally.

Phase 4: Personal-data Page Reads

Replace XML read in PersonalDataController::loadPersonalData() with:

GET /api/customers/{addressId}/persons/{personId}

Keep updatePersonalData() on XML for now. Reads are low-risk; writes require validated BusProNet update semantics.

Phase 5: OAuth/Userinfo And CRM Reads

Replace XML reads in:

• UserinfoController
• CrmAttributeController

with:

GET /api/customers/{addressId}/persons/{personId}
GET /api/customers/{addressId}/crm-attributes

Phase 6: Booking Form Prefill Reads

Replace XML read in ParticipantDataPrefiller::prefillApplicantFromUser() with:

GET /api/customers/{addressId}/persons/{personId}

Phase 7: Defer Personal-data Writes

Keep XML call:

• updatePersonalData(email, personalData)

Future replacement:

PATCH /api/customers/{addressId}/persons/{personId}

Do this only after write rules are validated for:

• AdressePerson
• AdresseAnschrift
• AdresseKommunikation
• AdressePersonManifest
• AdresseZusatzfeld or other tables holding height, weight, shoe size, and remarks if those are not in the core person/address tables.

Phase 8: Defer Registration And Create-address

Keep XML calls:

• register(registrationData)
• createAddress(personalData)

Future replacement:

POST /api/customers

This is higher risk than read replacement because it requires the full BusProNet insert graph, required defaults, and side effects.

Candidate SQL: Find Email Communication Type

Run once during bpn-connect development:

SELECT
    IDArtKommunikation,
    Langtext,
    Kurztext,
    Code,
    Status
FROM dbo.ArtKommunikation
WHERE
    LOWER(COALESCE(Langtext, '')) LIKE '%mail%'
    OR LOWER(COALESCE(Kurztext, '')) LIKE '%mail%'
    OR LOWER(COALESCE(Code, '')) LIKE '%mail%'
ORDER BY IDArtKommunikation;

If the data uses German labels, also inspect all rows manually. The schema alone cannot prove the canonical email type.

Candidate SQL: Validate Missing Relationships

Use these checks before shipping lookup logic:

-- Check whether AdresseKommunikation.IDAdressePerson_FS is a real person link.
SELECT TOP (20)
    ak.IDAdresseKommunikation,
    ak.IDAdresse_FS,
    ak.IDAdressePerson_FS,
    ap.IDAdressePerson,
    ap.IDAdresse_FS AS PersonAddressId,
    ak.Wert
FROM dbo.AdresseKommunikation ak
LEFT JOIN dbo.AdressePerson ap
    ON ap.IDAdressePerson = ak.IDAdressePerson_FS
WHERE ak.IDAdressePerson_FS <> 0
  AND ap.IDAdressePerson IS NULL;

-- Check whether Buchung.IDAdresse_FS_Anmelder matches the applicant person's address.
SELECT TOP (100)
    b.IDBuchung,
    b.Vorgang,
    b.IDAdresse_FS_Anmelder,
    b.IdAdressePerson_FS_Anmelder,
    ap.IDAdresse_FS AS ApplicantPersonAddressId
FROM dbo.Buchung b
LEFT JOIN dbo.AdressePerson ap
    ON ap.IDAdressePerson = b.IdAdressePerson_FS_Anmelder
WHERE b.IDAdresse_FS_Anmelder IS NOT NULL
  AND ap.IDAdresse_FS <> b.IDAdresse_FS_Anmelder;

-- Inspect status values before adding active-only predicates.
SELECT 'Adresse' AS table_name, Status, COUNT(*) AS rows
FROM dbo.Adresse
GROUP BY Status
UNION ALL
SELECT 'AdressePerson', Status, COUNT(*)
FROM dbo.AdressePerson
GROUP BY Status
UNION ALL
SELECT 'AdresseKommunikation', Status, COUNT(*)
FROM dbo.AdresseKommunikation
GROUP BY Status
UNION ALL
SELECT 'BuchungTN', Status, COUNT(*)
FROM dbo.BuchungTN
GROUP BY Status
ORDER BY table_name, Status;

Indexes To Confirm Or Add

The current CSV files do not include index definitions. For the lookup endpoint, confirm whether these access paths already exist:

• Buchung(Vorgang) including IDBuchung, IdAdressePerson_FS_Anmelder
• BuchungTN(IDBuchung_FS) including IDAdressePerson_FS, Status
• AdressePerson(IDAdressePerson) including IDAdresse_FS, Vorname, Name, Status
• AdresseKommunikation(IDAdressePerson_FS, Wert) including IDAdresse_FS, IDArtKommunikation_FS, Status
• AdresseKommunikation(IDAdresse_FS, Wert) including IDAdressePerson_FS, IDArtKommunikation_FS, Status

If the database collation is case-sensitive and email lookup must be case-insensitive, avoid LOWER(Wert) on large tables unless a computed normalized-email column or suitable index exists.

Future Endpoint Candidates

The XML plumbing can be replaced incrementally by small read endpoints backed by targeted SQL:

Endpoint	Purpose	Core tables
GET /api/bookings/lookup	Resolve email + public booking number to booking/person/address ids.	Buchung, BuchungTN, AdressePerson, AdresseKommunikation, Adresse
GET /api/bookings/{bookingId}	Fetch booking header/detail by internal id.	Buchung, Reise, Produkt, BuchungTN
GET /api/bookings/{bookingId}/participants	Fetch participants for a booking.	BuchungTN, AdressePerson, AdresseAnschrift
GET /api/customers/{addressId}/persons/{personId}	Fetch a minimal customer/person profile.	Adresse, AdressePerson, AdresseKommunikation, AdresseAnschrift

Keep endpoint responses narrow. Return only ids and fields needed by the Symfony portal so the Go service does not become a broad BusProNet data mirror.

Regeneration Notes

When new catalog exports are added, regenerate this document with:

1. Columns export from sys.tables, sys.schemas, sys.columns, sys.types.
2. Foreign key export from sys.foreign_keys and related FK columns.
3. Primary-key export from sys.key_constraints.
4. Index export from sys.indexes, sys.index_columns, and sys.columns.

Primary keys and indexes should be added to the table sections and the lookup query should be revisited once the real access paths are known.