myep/docs/buspronet-schema/crm-selection-queries.md

SelektionCRM — how the XML is assembled, and how to debug a missing selection

Authority: docs/buspro-database-reference.md. Schema semantics and the falsified-theory record live there and win on any conflict. This file is retained as the full investigation record for the missing-selection / aenderbar problem, including the queries that closed each theory.

Companion to travel-queries.md (static catalog) and live-api-queries.md (live socket endpoints). This one covers the KUNDENKONTO / art=SelektionCRM response — the document that drives the portal's entire authorization model.

Request side: ApiClient::getCrmAttributes() (src/BusProNet/ApiClient.php) sends satz typ="KUNDENKONTO" + art=SelektionCRM + the customer's email/password. Response side: CrmAttributesResponseParser (src/BusProNet/XmlParser/CrmAttributesResponseParser.php) turns selection ids into Symfony roles:

IDSelektionsstamm	Role
1070	ROLE_TEAMER
1292	ROLE_ADMIN (+ ROLE_HOUSE_MANAGER, + hotel code SSL)
1293	ROLE_MANAGER
1477	ROLE_GROUPS_MANAGER
1478	ROLE_GROUPS_ADMIN
label Hausleitung {CODE}	ROLE_HOUSE_MANAGER + hotel code {CODE}
nothing matched	ROLE_CUSTOMER

Only selections with auswahl="True" count. Every Admin/* controller is gated on one of these roles.

---

The four tables

From BusPronet_20260601-1517.csv (columns) and BusPronet_20260601-1518.csv (FKs):

Table	Columns	Role
dbo.Selektionsstamm	IDSelektionsstamm, Selektion, Status nvarchar(2) null, System tinyint	the selection master — that's all four columns
dbo.Selektionsgruppenstamm	IDSelektionsgruppenstamm, Selektionsgruppe, Status, System, Ausgrenzung, Position	the group master
dbo.Selektionsgruppe	IDSelektionsgruppenstamm_FS, IDSelektionsstamm_FS, Position	membership — which selection sits in which group, and in what order
dbo.Selektionen	IDAdresse_FS, IDSelektionsstamm_FS	assignment — which address holds which selection

<crmaktionen> comes from a parallel pair: dbo.CRMAktion (master: Code, Bezeichnung, Aktiv, Status, AktivDatumVon/Bis, …) and dbo.CRMZuweisung (assignment via IDAdresse_FS + IDCRMAktion_FS).

XML → column mapping

XML	Source
<idadresse>	Adresse.IDAdresse (the authenticated account)
<idperson>	AdressePerson.IDAdressePerson
<selektionsgruppe id> / bezeichnung	Selektionsgruppenstamm.IDSelektionsgruppenstamm / .Selektionsgruppe
<selektion id> / bezeichnung	Selektionsstamm.IDSelektionsstamm / .Selektion
selektion@auswahl	EXISTS (Selektionen WHERE IDAdresse_FS = <account> AND IDSelektionsstamm_FS = <id>) — confirmed
selektion@aenderbar	computed, not stored — no such column exists anywhere (see below)
group order	Selektionsgruppenstamm.Position, <ohne Gruppe> first
selection order within a group	Selektionsgruppe.Position
<crmaktion …>	CRMAktion + CRMZuweisung (auswahl = assignment exists)

aenderbar is not a stored flag

A case-insensitive search for aenderbar / auswahl across every column name in the database (1023 tables, ABOBewegungsdaten … ZustiegsPlanungPersonStelle, so the export is complete and untruncated) returns no hit on any Selektion* table. The only *Aenderbar* column in the entire schema is AdresseAgenturZusatz.ZahlungsartAenderbar, which is unrelated. And the three core tables have no spare bit to hide it in: Selektionsstamm has exactly four columns, Selektionsgruppe three, Selektionen two.

So aenderbar is derived at response time. Don't go looking for the column again — this is what Query D and Query E are for.

The <crmaktionen> half of the document hints at the intended semantics: CRMAktion does carry real flags (Aktiv, Anzeige, AktivDatumVon/Bis), and in the reference sample the long-expired "Deal Der Woche KW40-2018" is aenderbar="False" while the standing "NOMAIL" opt-out is "True". aenderbar reads as "the customer may still toggle this" — a validity/whitelist question, not a static per-row property.

Two orderings worth noting, because they rule out the obvious alternatives:

• Inside TEAM the ids run … 1374, 1375, 1376, 1373, 1377 … — neither id order nor label order. That is Selektionsgruppe.Position.
• Within Interessen the labels are alphabetical, which is consistent with Position having been maintained alphabetically there. Don't read it as a different rule.

The decisive observation: the catalog is group-driven

Every Hausleitung * row is emitted with auswahl="False" for an account that holds none of them. So the endpoint does not list "the selections this address has" — it lists the whole published catalog and then flags which entries the address holds.

<selektionsgruppe id="0" bezeichnung="&lt;ohne Gruppe&gt;"> is the bucket for selections with no (or a 0) Selektionsgruppe membership row.

The practical consequence: inserting a row into dbo.Selektionen can never make a selection appear. It can only flip auswahl from False to True on an entry that the catalog already emits. A selection that is absent from the XML is absent from the catalog, and the assignment is irrelevant until that is fixed.

---

Query A — reconstruct the response

Run this for the account in question and diff its rows against the actual XML. A clean match proves the reconstruction above; any mismatch belongs in the caveats section.

DECLARE @AddressId bigint = 141747;

SELECT COALESCE(sg.IDSelektionsgruppenstamm, 0)       AS groupId,
       COALESCE(sg.Selektionsgruppe, '<ohne Gruppe>') AS groupLabel,
       s.IDSelektionsstamm                            AS selectionId,
       s.Selektion                                    AS selectionLabel,
       CASE WHEN s.System = 0 THEN 'True' ELSE 'False' END          AS aenderbar,
       CASE WHEN sel.IDAdresse_FS IS NULL THEN 'False' ELSE 'True' END AS auswahl
FROM dbo.Selektionsstamm s
LEFT JOIN dbo.Selektionsgruppe sgm
       ON sgm.IDSelektionsstamm_FS = s.IDSelektionsstamm
LEFT JOIN dbo.Selektionsgruppenstamm sg
       ON sg.IDSelektionsgruppenstamm = sgm.IDSelektionsgruppenstamm_FS
LEFT JOIN dbo.Selektionen sel
       ON sel.IDSelektionsstamm_FS = s.IDSelektionsstamm
      AND sel.IDAdresse_FS = @AddressId
WHERE COALESCE(s.Status, '') <> 'L'
  AND COALESCE(sg.Status, '') <> 'L'
ORDER BY COALESCE(sg.Position, -1), sgm.Position, s.Selektion;

(Status = 'L' is this database's soft-delete marker — see ../bpn-connect-query-reference.md.)

Note the join fans out: a selection can belong to several groups (confirmed — 1292 is in both 10 and 91), so this query emits one row per membership. The XML shows a selection once per group it is published in.

Selektionsgruppenstamm.Ausgrenzung is exclusion semantics (mutually exclusive group), not visibility — don't mistake it for a publish flag.

---

Query B — diagnose a selection that never appears

Compares the suspect row against known-good neighbours: 1292/1293 (long-standing, emitted, aenderbar=False) and 1462 (most recent one that is emitted, aenderbar=True). Whatever column differs is the answer.

SELECT s.IDSelektionsstamm,
       s.Selektion,
       s.Status,
       s.System,
       sgm.IDSelektionsgruppenstamm_FS AS groupId,
       sgm.Position                    AS positionInGroup,
       (SELECT COUNT(*) FROM dbo.Selektionen x
         WHERE x.IDSelektionsstamm_FS = s.IDSelektionsstamm
           AND x.IDAdresse_FS = 141747) AS assignedToAddress
FROM dbo.Selektionsstamm s
LEFT JOIN dbo.Selektionsgruppe sgm
       ON sgm.IDSelektionsstamm_FS = s.IDSelektionsstamm
WHERE s.IDSelektionsstamm IN (1477, 1478, 1292, 1293, 1462)
ORDER BY s.IDSelektionsstamm;

What Query B ruled out (address 141747, 2026-08-04)

id	Selektion	Status	System	groupId	posInGroup	assigned
1292	Admin	(blank)	1	10	33	1
1292	Admin	(blank)	1	91	1	1
1293	Manager	(blank)	1	92	1	1
1293	Manager	(blank)	1	10	34	1
1462	Hausleitung AGR	(blank)	1	10	74	0
1477	Preisrechner	(blank)	1	10	75	0
1478	Preisrechner Admin	(blank)	1	10	76	1

Three conclusions:

1. The missing-membership theory is dead. 1477/1478 are in group 10 (TEAM), at positions 75/76, and 1478 is assigned to the address. On every column available in these three tables, 1478 is indistinguishable from 1462 — which the XML does emit. Whatever excludes them lives outside Selektionsstamm / Selektionsgruppe / Selektionen.
2. aenderbar is not derived from System. 1292 (aenderbar="False") and 1462 (aenderbar="True") both have System = 1. Selektionsstamm has no other column, so aenderbar comes from somewhere else entirely.
3. A selection can belong to several groups, and some groups are not published. 1292 is in 10 + 91, 1293 is in 10 + 92; groups 91/92 appear nowhere in the XML, which only emits 0, 10, 83. So there is a group-level publish filter — and, suggestively, the two rows with a second (unpublished) group membership are exactly the two that come back aenderbar="False".

---

Query D — the agency whitelist (run this first)

dbo.AgenturSelektionenKunde is a bare two-column link table, (IDAdresse_FS, IDSelektionsstamm_FS) — literally "agency's customer selections". The portal authenticates against BusPro as an agency account (bpn_username in ApiClient), so a per-agency whitelist of which customer selections that agency may see and/or edit is exactly the right shape. It is the only candidate found so far that could explain the 1478 omission and aenderbar through one mechanism.

SELECT ask.IDAdresse_FS         AS agencyAddressId,
       a.SuchName               AS agencyName,
       ask.IDSelektionsstamm_FS AS selectionId,
       s.Selektion              AS selectionLabel
FROM dbo.AgenturSelektionenKunde ask
JOIN dbo.Selektionsstamm s ON s.IDSelektionsstamm = ask.IDSelektionsstamm_FS
LEFT JOIN dbo.Adresse a    ON a.IDAdresse = ask.IDAdresse_FS
ORDER BY ask.IDAdresse_FS, s.Selektion;

-- same shape, same question:
SELECT * FROM dbo.PartnerSelektionenKunde;

Compare the row set for the portal's agency address against a freshly captured response:

Result	Meaning
set == the 32 emitted entries	this is the publish gate — 1477/1478 just need rows here
set == the aenderbar="True" subset only	this is the aenderbar source
set is some third thing	note it in this doc and continue to C1
table empty	both hypotheses dead — fall through to C1/C2/C3

Result (2026-08-04): dbo.AgenturSelektionenKunde is empty. The agency-whitelist theory is dead. Do not re-run.

---

Query E — group-count cross-check (the secondary aenderbar theory)

Query B hinted that the two aenderbar="False" rows were the two with a second, unpublished group membership (1292→{10, 91}, 1293→{10, 92}) while aenderbar="True" 1462 sat in group 10 alone. Two data points is not a rule — this settles it in one run:

SELECT s.IDSelektionsstamm,
       s.Selektion,
       COUNT(*) AS groupCount,
       STRING_AGG(CAST(sgm.IDSelektionsgruppenstamm_FS AS varchar(10)), ',') AS groupIds
FROM dbo.Selektionsstamm s
JOIN dbo.Selektionsgruppe sgm ON sgm.IDSelektionsstamm_FS = s.IDSelektionsstamm
WHERE s.IDSelektionsstamm IN (1064,1065,1066,1067,1068,1069,1070,1156,1292,1293,
                              1299,1304,1377,1459,1461,1462,1477,1478)
GROUP BY s.IDSelektionsstamm, s.Selektion
ORDER BY s.IDSelektionsstamm;

Read against the known aenderbar values from the reference response:

• aenderbar="False": 1070, 1156, 1292, 1293, 1299, 1304, 1377
• aenderbar="True": 1064–1069, 1459, 1461, 1462

If every False row has groupCount > 1 and every True row has groupCount = 1, the theory holds. Note it still would not explain 1478 (group 10 only, yet omitted), so Query D remains the priority.

Result (2026-08-04): the theory is dead. Group membership does not correlate with aenderbar at all:

id	Selektion	groupCount	groupIds	aenderbar
1064–1069	Interessen	1	83	True
1070	E&P Teamer - allg. Merkmal	1	10	False
1292	Admin	2	91, 10	False
1293	Manager	2	92, 10	False
1299 / 1304 / 1377	Hausleitung SSL / DKS / KHH	1	10	False
1459 / 1461 / 1462	Hausleitung SVS / SHM / AGR	1	10	True
1477 / 1478	Preisrechner, Preisrechner Admin	1	10	(not emitted)

1299/1304/1377 and 1459/1461/1462 have identical group membership, identical Status, identical System — and opposite aenderbar. The 1292/1293 double membership was a coincidence.

Confirmed as a side effect: 1156 is absent from this result because the inner join found no Selektionsgruppe row for it. <ohne Gruppe> really does mean "no membership row", as modelled at the top of this document.

---

Query F — exhaust every remaining link to Selektionsstamm

After D and E, the schema export lists exactly these tables with an FK into Selektionsstamm. This counts all of them at once for the rows whose behaviour differs, which is the last thing the database can tell us:

SELECT s.IDSelektionsstamm, s.Selektion,
       (SELECT COUNT(*) FROM dbo.Selektionen              x WHERE x.IDSelektionsstamm_FS = s.IDSelektionsstamm) AS addressAssignments,
       (SELECT COUNT(*) FROM dbo.PartnerSelektionenKunde  x WHERE x.IDSelektionsstamm_FS = s.IDSelektionsstamm) AS partnerLinks,
       (SELECT COUNT(*) FROM dbo.LeistungSelektionenKunde x WHERE x.IDSelektionsstamm_FS = s.IDSelektionsstamm) AS serviceLinks,
       (SELECT COUNT(*) FROM dbo.ReiseleiterSelektionen   x WHERE x.IDSelektionsstamm_FS = s.IDSelektionsstamm) AS guideLinks,
       (SELECT COUNT(*) FROM dbo.ReiseSelektionen         x WHERE x.IDSelektionsstamm_FS = s.IDSelektionsstamm) AS travelLinks,
       (SELECT COUNT(*) FROM dbo.ReisezielSelektionsstamm x WHERE x.IDSelektionsstamm_FS = s.IDSelektionsstamm) AS destinationLinks
FROM dbo.Selektionsstamm s
WHERE s.IDSelektionsstamm IN (1070,1156,1292,1293,1299,1304,1377,1459,1461,1462,1477,1478,1064)
ORDER BY s.IDSelektionsstamm;

The decisive comparison is 1377 (aenderbar="False") vs 1462 ("True") vs 1478 (omitted). If all three rows are identical here too, then no per-selection property in this database distinguishes them, and the rule is not in the schema at all — go to "Where this leaves the investigation" below.

Result (2026-08-04): all link counts are zero for every row queried, except travelLinks on 1070 (11) and 1156 (3) via ReiseSelektionen — neither of which correlates with aenderbar or with being emitted. Assignment counts don't explain anything either:

id	assignments	emitted?	aenderbar
1377 Hausleitung KHH	0	yes	False
1462 Hausleitung AGR	1	yes	True
1478 Preisrechner Admin	3	no	—

1377 is published with zero assignments; 1478 has three and is not published. This exhausts every FK path into Selektionsstamm in the schema. Stop querying the database — the answer is not in it.

---

Query C — locate the publish gate

Point (1) means some table or config outside the three core tables decides what gets emitted. Run these in order; the first hit explains everything.

C1 — what distinguishes a published group from an unpublished one. Groups 0, 10, 83 are emitted; 91, 92 are not:

SELECT IDSelektionsgruppenstamm, Selektionsgruppe, Status, System, Ausgrenzung, Position
FROM dbo.Selektionsgruppenstamm
ORDER BY Position;

If exactly the emitted groups share a Status/System/Position value that 91/92 lack, that's the group gate. It still won't explain 1478 (same group as 1462), but it pins down half the rule — and tells you whether aenderbar tracks membership in one of these system groups.

C2 — hunt for a published-selection whitelist in the config stores. This is the leading theory for 1478: a config value enumerating exposed selection ids that was maintained up to 1462 and never extended. This database does store id lists in delimited string columns (CRMAktion.IDsAgenturEingrenzung, ImportAdressen.ZuweisungSelektionIDs, AdresseHistorie.SelektionsIDs), so the pattern exists. Search for a known-published id:

SELECT * FROM dbo.Einstellung
WHERE Wert LIKE '%1462%' OR Wert LIKE '%1292%'
   OR Bezeichnung LIKE '%elektion%' OR Beschreibung LIKE '%elektion%';

SELECT * FROM dbo.ProgrammEinstellung
WHERE Wert LIKE '%1462%' OR Wert LIKE '%1292%' OR Variable LIKE '%elektion%';

SELECT * FROM dbo.ConnectEinstellung
WHERE WertString LIKE '%1462%' OR WertString LIKE '%1292%';

C3 — brute-force the whole schema if C1/C2 come up empty. Find every wide text column that mentions a published selection id:

DECLARE @needle nvarchar(20) = '1462';
DECLARE @sql nvarchar(max) = '';

SELECT @sql = @sql + 'SELECT ''' + QUOTENAME(t.name) + ''' AS tbl, ''' + QUOTENAME(c.name)
            + ''' AS col, COUNT(*) AS hits FROM dbo.' + QUOTENAME(t.name)
            + ' WHERE CAST(' + QUOTENAME(c.name) + ' AS nvarchar(max)) LIKE ''%'
            + @needle + '%'' HAVING COUNT(*) > 0;' + CHAR(10)
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.types  ty ON ty.user_type_id = c.user_type_id
WHERE ty.name IN ('nvarchar', 'varchar', 'nchar', 'char')
  AND (c.max_length = -1 OR c.max_length >= 100)
  AND t.name NOT LIKE 'Selektion%';

EXEC sp_executesql @sql;   -- read-only; slow, run off-peak

C4 — rule out staleness/caching first, it's free. 1478 is structurally identical to an emitted row, which is also what a cached catalog looks like. Re-fetch the XML now and confirm the sample postdates the insert:

// bin/console or a scratch script
$client->getCrmAttributes($email, $password);

If BusPro caches the catalog in-process, an application-pool/service restart on the BusProNet host is the fix, and no schema change is needed.

---

Portal side: a correct XML is not enough

BpnAuthenticator::createOrUpdateLocalUser() snapshots the roles onto the local User entity at login (setRoles($crmAttributes->roles) / setHotelCodes($crmAttributes->hotelCodes)). Roles are not re-read per request. So after fixing BusPro the affected user must log out and log in again before ROLE_GROUPS_ADMIN takes effect — an active session keeps the stale role set.

---

Where this leaves the investigation

Conclusion as of 2026-08-04, after Queries A–F: the schema search is exhausted.

• 1478 and 1462 are identical on every column and every link the schema exposes — same blank Status, same System = 1, same single membership in group 10, adjacent positions (76 vs 74), zero links in all five side tables. 1462 is emitted, 1478 is not.
• 1377 (aenderbar="False") and 1462 ("True") are identical by the same measure. Opposite flag.
• Assignment count is not it either: 1377 is published with zero assignments, 1478 has three and is not published.

Rows that are indistinguishable in the database behave differently in the response. Every column of the three core tables, every FK path into Selektionsstamm, and a whole-schema column-name search have now been checked. So the publish set and the aenderbar flag are not derived from this database — they live in the BusProNet service layer: its own configuration, or an in-process cache of the catalog.

Only C2/C3 (a text search of the config stores, then of every wide text column) remain, and they are long shots. Prefer the escalation path below.

Escalation path, cheapest first:

1. Re-fetch and confirm staleness. Our side does not cache this call — the xml-sync cache tag covers the XML export sync, and ApiClient::getCrmAttributes() has no cache layer — so a fresh login hits BusPro directly. If 1478 is still absent from a response captured after the insert, the staleness is on the BusProNet host and an application-pool/service restart there is the test.
2. The UI experiment. Create a throwaway selection in the TEAM group through the BusPro UI, then diff its rows against 1478 across Selektionsstamm / Selektionsgruppe / Selektionen. If it appears in the XML, the diff names exactly what a manual INSERT misses. If it does not, the gate is provably config/cache and not per-row data.
3. Ask the vendor. "What determines which Selektionsstamm entries SelektionCRM publishes, given group membership is not sufficient — and what drives aenderbar?" Both are one-sentence answers for them and may not be discoverable from the schema at all. Worth bundling into the open BPN thread.

Caveats

• aenderbar has no known source. No such column exists in any of the 1023 tables; Selektionsstamm.System (Query B), the agency whitelist (Query D) and group membership (Query E) are all falsified. Treat it as unmodellable in a bpn-connect endpoint until the vendor explains it — if bpn-connect must serve this response, the safe move is to keep SelektionCRM on the socket API rather than guess, exactly as live-api-queries.md recommends for getMutableData().
• Groups 91 and 92 are known to exist and are never emitted; what they are is still unexplained (Query C1). Record the answer here when you have it.
• The publish gate is not identified. Group membership is necessary but not sufficient: 1478 sits in the published TEAM group with an assignment and is still omitted. Query C is the open investigation.
• Consequently Query A over-reports: it will return 1477/1478 (and anything in groups 91/92) that the real XML omits. It reconstructs the shape of the response, not yet its exact membership.
• dbo.Selektionen has no status column and no exported primary key, so a duplicate assignment row may well be insertable. Check before inserting.
• The schema exports carry no PK/index catalog (noted in ../bpn-connect-query-reference.md); none of the queries here are performance-sensitive, but a bpn-connect endpoint built on them would want indexes on Selektionen.IDAdresse_FS and Selektionsgruppe.IDSelektionsstamm_FS confirmed.
• Adresse vs. AdressePerson: assignments hang off IDAdresse, not IDPerson.