# Integrations, identity and data quality Rules behind FLW01 to FLW11 in connection_map.py and KEY01 to KEY06 in key_match.py, how to test a flow so a green status is never the evidence, and how to check automation at the point of action. ## The connection register One row per directional transfer in [../assets/connections-template.csv](../assets/connections-template.csv). A two-way sync is two rows. - `source_id`, `destination_id`, `via_id`: register tool IDs. The via tool is the pipe (ELT, reverse ETL, iPaaS). An ID that is not in the register (for example X01 for an unregistered form tool) is allowed and raises FLW09. - `objects_or_events`: what moves, in plain words ("email unsubscribes", "contacts", "offline conversions and customer list"). - `method`: native, tag, cdp, elt, reverse_etl, ipaas, webhook, custom or manual_csv. - `match_key`: the identifier the destination matches on. Several keys are separated by semicolons. - `fields_written`: object.field names the connection writes in the destination, written the same way as `system_of_record_for`. - `field_authority`: source_wins, destination_wins, or a named rule. Blank or n/a means nobody decided. - `schedule`, `required_freshness_minutes`, `observed_delay_minutes`, `last_success`. - `feeds`: customer_action (sends, suppression, eligibility, tasks, audiences), billing, reporting or internal. This column sets the severity. - `failed_records_visible`, `alert_owner_role`, `replay_method`, `consent_or_suppression_rule`. - `credential_owner`: service_account, person:active, person:departed, manual or n/a. - `status`: configured (someone set it up), observed (a dated test or log shows records arriving), proposed (planned). Proposed rows are drawn dotted and not checked. Only an observed row with `evidence_ref` and `checked_at` is drawn solid. - `evidence_ref` and `checked_at`: required for observed. - `last_success` and `checked_at` are YYYY-MM-DD. A date the script cannot read stops the run with its row and column. A connection is critical when its source, destination or via tool is critical. A tool counts unless its status is retired or out_of_scope; any other word keeps it in scope and a Note lists the row. Unknown stays unknown: a judgement column holding unknown, tbc, tbd, ? or none counts as blank, so it raises the same findings. n/a stays a deliberate value, except in `field_authority` and `evidence_ref`, where it counts as blank. Each check prints NOT CHECKED when its columns are blank: FLW01 without `fields_written`, FLW03 without `match_key`, FLW04 and FLW07 without a connection to a critical tool, FLW05 without `credential_owner` or a critical connection, FLW06 without an observed row, FLW08 without `objects_or_events` and destination capabilities, FLW10 without a method on a critical connection, FLW11 without a critical tool. A clean connection prints PASS only when it is observed with `evidence_ref` and `checked_at`. A clean configured connection prints NOT CHECKED until a dated test exists. ## The four-case test For each connection on a traced workflow, cover four cases: - A new record. - An update to an existing record. - A rejected or exception record (invalid value, missing key). - A removal or unsubscribe, where the flow carries one. Prefer records that already went through each case: a recent sign-up, a recent update, a row in the error log, a recent unsubscribe. Read each at both ends. Write one evidence line per case: ```text 2026-10-30 | C09 | update | source cio-1042 -> destination hs-55817 | sent 09:02 UTC, arrived 09:03 UTC | observed: unsubscribed=true | expected: unsubscribed=true | PASS ``` A test that creates, updates, rejects or unsubscribes a record, sends a message or places an order runs only after the owner approves it, on agreed test records. List each approved test in the report with its test record IDs. Until then, mark the case "not tested" with the reason. A repair passes when its four-case test passes three times in a row. ## Field authority - One authority per field, or separate fields for separate concepts (for example `lifecycle_stage` from the CRM and `product_stage` from the warehouse). - Blanks, corrections and removals need explicit rules. Last write wins is not a rule. - FLW01 (FAIL): two or more writers of one destination field and one of these: a connection writer with no `field_authority`; two connections that both claim source_wins; a connection claiming source_wins on a field its destination is recorded as the system of record for, which makes two authorities. Only destination_wins or a named rule passes that last case. Writers are the connections listing the field, plus the destination tool itself when its `system_of_record_for` lists that field. A copy out of the system of record into another tool has one writer, so a CRM field copied into the warehouse or an email tool is not a finding. - Confirm with the field's property history: values alternating between two sources is the symptom. A lifecycle stage set by a warehouse sync and by a CRM workflow oscillates and re-triggers workflows. - FLW01 finds the conflict and asks whether an authority rule exists. It does not judge the rule: who decides and who repairs is in [handoffs.md](handoffs.md). ## Freshness - Required freshness comes from the consequence: a suppression or eligibility flow must arrive before the next send, so its requirement is shorter than the send interval. - FLW02 (FAIL when the flow feeds customer_action or billing, otherwise WARN): observed delay over the required freshness. - FLW02 staleness: on an observed flow, a last success older than the larger of `--stale-days` (default 2) and two schedule intervals (realtime 0, hourly, every N hours, daily 1 day, weekly 7 days, monthly 31 days). On-demand and manual flows are skipped. FAIL when the flow feeds customer_action or billing, otherwise WARN. ## Failures and replays - Incoming events can arrive twice and out of order; Stripe's webhook documentation says so directly. A replay must not repeat a customer action. - FLW04 (WARN): a critical connection with no alert owner or no replay method. - FLW07 (WARN): a critical connection whose `failed_records_visible` is anything other than yes or n/a: nobody can see the rejected records, or nobody recorded whether they can. - FLW10 (WARN): a manual CSV step on a critical connection. Record who runs it, when, and what happens when they are away. ## Automation at the point of action Most class 1 harm starts when a workflow acts on state that changed while a record waited. For each automation on a traced workflow that sends a message, creates a task or writes an audience, check four cases: - An unsubscribe or consent withdrawal during a delay. - A conversion, payment or goal reached during a delay. - The same source event delivered twice. - The destination down or rate-limited when the step runs. Test read-only. Read the workflow configuration first: does each send or task step re-check suppression, consent and goal state when it runs, or only at enrolment? Then read the enrolment history for past records that hit each case. A live run with test records needs the owner's approval, like the four-case test. - FAIL: a send or task step that does not re-check suppression, consent or goal state when it runs, or history showing a message after an unsubscribe. - WARN: re-entry and exit rules that nobody wrote down. - PASS: configuration and history both show the re-check. - Not tested: neither configuration nor history was readable; name what would close it. Evidence line: ```text 2026-10-30 | W2 | trial_onboarding day 3 | unsubscribe during delay | config: send step re-checks the suppression list | history: 4 unsubscribed mid-delay, 0 sent | PASS ``` Journey design, frequency caps, suppression design and deliverability go to lifecycle-marketing-audit. ## Credentials - Record the identity each connection runs on, not who created it. - A person's OAuth grant or legacy private app breaks when they leave. HubSpot documents that removing a legacy private app's creator, or their Super Admin permission, can break association calls with `USER_DOES_NOT_HAVE_PERMISSIONS`; verify the current wording and recovery steps live. - Service identities still need a named human owner. In Snowflake, `TYPE=SERVICE` users cannot sign in with a password or enrol in MFA. - FLW05: person:departed is a FAIL; person:active on a critical connection is a WARN; a blank credential owner, or one outside the values above, on a critical connection is a WARN. ## Consent and suppression on people flows - FLW08 (WARN): the destination is a messaging or ad tool (marketing_automation_email, lifecycle_messaging, sms, push, in_app_messaging, ad_platform), the objects include a people word, singular or plural (people, person, contact, user, lead, subscriber, member, customer, recipient, profile, identify, identifier, trait, audience, segment, email, phone, unsubscribe, customer list) and no consent or suppression rule is recorded. - FLW08 (NOT CHECKED on that connection): the same destination and no rule, but the objects are blank, or match neither the people words nor the non-person list (reporting tables, products, catalog, invoices). The connection cannot reach PASS. Record the rule, or n/a when no people data moves. - The privacy owner decides the rule. The audit only records whether one exists and whether the flow honours it. ## Register hygiene checks - FLW06 (WARN): marked observed without an evidence reference and a check date. Treat it as configured until a dated test is attached; the map draws it dotted. - FLW09 (FAIL): a system in the connection register is not in the tool register. - FLW11 (WARN): a critical tool appears in no mapped connection. ## Tracing identity through a workflow Trace five cases per workflow: a new prospect, an existing customer, a person in two accounts, a refund or cancellation, and a record with missing attribution. Write every identifier at every step and where each mapping is stored. A SaaS chain often looks like this: ```text anonymous browser ID -> app user ID -> account ID -> CRM contact ID -> CRM company ID -> billing customer ID ``` - Keep people, accounts, devices and contact details apart. One person can belong to two accounts; a shared device does not make two people one. - Email is not a durable key. Addresses change, and a shared domain does not prove that two records belong to one paying account. - Note where each join lives (CRM property, warehouse model, CDP identity graph) and who owns it. ## Data quality measures State the numerator, the denominator and the population for every rate. A zero denominator is n/a, never a pass. - Ready delivery: records that arrived usable on time / records expected in the window. - Required-field completeness: records with the field filled / records that must have it. - Expected-record coverage (KEY01): expected keys found in the destination / distinct expected keys. - Excess duplicate rate (KEY02): (keyed rows - distinct keys) / keyed rows. - Validity: values inside the allowed list or format / values present. - Reconciliation exceptions: records whose values differ between the two systems. Occurrences are not affected records. Fictional sample: 18 owner errors and 10 missing values, with 4 records in both groups, is 28 defect occurrences on 24 affected records. Adding the two percentages double counts the overlap. ## Reading key_match.py - Thresholds, unless `metrics.md` sets others: coverage 98% or more PASS, 95 to 98% WARN, below 95% FAIL; excess duplicates above 0.5% FAIL and any above zero WARN. These match the purchase-reconciliation skill's lines. - Under 200 distinct expected keys, KEY01 and KEY02 report counts only (KEY06). Extend the period or widen the population instead. Files with no data rows print NOT CHECKED for KEY03 to KEY05 as well. - Missing keys: expected records the destination never received. Trace two of them through the four-case test. - Unexpected keys are often trials or churned accounts. Explain them before calling them defects. - KEY03 counts blank and padded keys. Padded keys match after `--normalize`; fix the source rather than relying on it. - In the `--exceptions` file, keys with '@' are SHA-256 hashes of the trimmed, lowercased value. The same hash under missing and unexpected means the two systems differ only in case or spacing. - Run at least one key per traced workflow, for example paid billing accounts against CRM companies on account_id. ## Repair the cause and the copies - Fix the earliest controllable point where the bad value enters: the form, the import, the mapping, the territory table. - Then list every downstream copy. Correcting a warehouse row does not retract an audience member, reverse a CRM task or repair a historical report. Decide per copy: recompute, remove, annotate or leave. - Automated tests catch recurrence, but they prove only what they assert. dbt's uniqueness, not-null, accepted-values and relationship tests are a good start, not proof that values match reality.