# WhatsApp LID and E.164 Authorization Plan

> This is a code-review and illustrative operations plan. Do not deploy, stop services, access a live database, apply SQL, or use real credentials without separate explicit authorization.

## Goal and security contract

Channel intake may accept either of these WhatsApp sender forms:

- E.164: the existing contract remains unchanged, including trimming surrounding whitespace before validation.
- LID: exactly 9-20 ASCII digits followed by the lowercase suffix `@lid`.

LID input is byte-for-byte strict. It is not trimmed, case-folded, or otherwise canonicalized. Spaces, tabs, CRLF, NBSP, mixed-case suffixes, non-ASCII digits, device suffixes, and other JID domains are invalid.

Syntax never grants authorization. The exact normalized identifier must belong to an enabled `UserChannels` row whose `UserProfile` is active. E.164 and LID aliases require separate allowlist rows.

All examples in this document are synthetic. Production identifiers, message IDs, candidate keys, cache paths, file sizes, hashes, and mapping evidence belong only in access-controlled operational evidence.

## Code change

Files:

- `src/Sibyla.Infrastructure/Intake/ChannelIntakeService.cs`
- `tests/Sibyla.Tests/Intake/ChannelIntakeServiceTests.cs`

The implementation keeps using the trimmed value for E.164. A LID is accepted only when the original input is ordinal-equal to the validated value:

```csharp
ChannelType.WhatsApp when IsE164WhatsappSender(normalized) => normalized,
ChannelType.WhatsApp when string.Equals(value, normalized, StringComparison.Ordinal)
    && IsWhatsappLidSender(normalized) => normalized,
```

`IsWhatsappLidSender` must require an exact lowercase `@lid` suffix, 9-20 ASCII digits, and no other characters.

## TDD and validation

Use only synthetic identifiers, such as `123456789@lid`, `12345678901234567890@lid`, and the reserved fictional E.164 example `+15550102030`.

Required tests cover:

- a whitespace-wrapped, otherwise allowlisted LID is invalid;
- exact 9-digit and 20-digit LIDs are accepted when allowlisted;
- mixed-case `@Lid` is invalid;
- wrapping with spaces, tabs, CRLF, or NBSP is invalid;
- an enabled LID linked to an inactive `UserProfile` returns `channel_intake_sender_not_authorized`;
- unknown and disabled LIDs remain unauthorized;
- trimmed E.164 remains accepted.

Start from `origin/main`. First add the authorized, valid, unwrapped LID test and run it before editing production source. Do not use `--no-build`:

```powershell
dotnet test tests/Sibyla.Tests/Sibyla.Tests.csproj --filter "FullyQualifiedName~RegisterAsync_ExactBoundaryWhatsappLidSenderRegisters"
```

Expected RED: the valid LID is rejected with `channel_intake_sender_invalid`.

The whitespace-invalid test already passes on the `origin/main` parent because that implementation rejects every LID, so it is not a legitimate RED test there. After adding minimal LID syntax support, add and run the whitespace-invalid test:

```powershell
dotnet test tests/Sibyla.Tests/Sibyla.Tests.csproj --filter "FullyQualifiedName~RegisterAsync_WhitespaceWrappedWhatsappLidFailsAsInvalid"
```

With minimal support that validates the trimmed value, expected RED is that no exception is thrown. Then require raw input to be ordinal-equal to the validated LID and rerun both focused commands for GREEN.

Then run:

```powershell
dotnet test tests/Sibyla.Tests/Sibyla.Tests.csproj --filter "FullyQualifiedName~ChannelIntakeServiceTests"
dotnet test tests/Sibyla.Tests/Sibyla.Tests.csproj --filter "FullyQualifiedName~ChannelIntake|FullyQualifiedName~Intake"
dotnet build GOTT.Sibyla.slnx --no-restore
git diff --check
```

## Illustrative deny-by-default SQL

The SQL below is a review artifact only. Do not execute it as part of the code hotfix. At a separately approved maintenance window, pass `trusted_e164` and `trusted_lid` as safely quoted `psql` variables, referenced only as `:'trusted_e164'` and `:'trusted_lid'`. Invoke `psql` with `-w` so it never prompts for a password. Values and any credential material must come from an access-controlled temporary mechanism that does not print them; do not use raw string substitution, shell tracing, command-line literals, source control, tickets, or ordinary command output.

The example deliberately:

- locks the relevant tables for a stable ownership decision;
- validates both inputs before any identifier lookup or mutation;
- resolves exactly one active target profile solely through the exact pre-existing enabled trusted E.164 row;
- treats that E.164 row as an immutable prerequisite;
- aborts if the LID belongs to another profile;
- never updates `UserProfileId` on conflict;
- inserts or enables only the exact LID and only for the E.164 row's profile;
- uses the EF-mapped enum text storage type and native `jsonb`;
- verifies exactly one enabled E.164 row and one enabled LID row for the same active profile;
- remains safe to rerun idempotently.

```sql
BEGIN;

CREATE TEMP TABLE requested_whatsapp_aliases (
    trusted_e164 text NOT NULL,
    trusted_lid text NOT NULL
) ON COMMIT DROP;

INSERT INTO requested_whatsapp_aliases (trusted_e164, trusted_lid)
VALUES (:'trusted_e164', :'trusted_lid');

DO $validation$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM requested_whatsapp_aliases
        WHERE trusted_e164 !~ '\A[+][1-9][0-9]{7,14}\Z'
           OR char_length(trusted_e164) NOT BETWEEN 9 AND 16
           OR octet_length(trusted_e164) <> char_length(trusted_e164)
    ) THEN
        RAISE EXCEPTION 'deny: trusted E.164 fails the exact ASCII 9-16 character contract';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM requested_whatsapp_aliases
        WHERE trusted_lid !~ '\A[0-9]{9,20}@lid\Z'
           OR octet_length(trusted_lid) <> char_length(trusted_lid)
    ) THEN
        RAISE EXCEPTION 'deny: trusted LID fails the exact ASCII grammar';
    END IF;
END
$validation$;

LOCK TABLE "UserProfiles" IN SHARE MODE;
LOCK TABLE "UserChannels" IN SHARE ROW EXCLUSIVE MODE;

CREATE TEMP TABLE target_profile ON COMMIT DROP AS
SELECT
    profile."Id" AS profile_id,
    e164."Id" AS trusted_e164_channel_id
FROM requested_whatsapp_aliases AS requested
JOIN "UserChannels" AS e164
  ON e164."ChannelType" = 'WhatsApp'
 AND e164."ExternalIdentifier" = requested.trusted_e164
 AND e164."NormalizedIdentifier" = requested.trusted_e164
 AND e164."IsEnabled" = true
JOIN "UserProfiles" AS profile
  ON profile."Id" = e164."UserProfileId"
 AND profile."IsActive" = true;

DO $ownership$
BEGIN
    IF (SELECT count(*) FROM target_profile) <> 1 THEN
        RAISE EXCEPTION 'deny: expected exactly one enabled trusted E.164 row joined to one active target profile';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM "UserChannels" AS uc
        CROSS JOIN requested_whatsapp_aliases AS requested
        WHERE uc."ChannelType" = 'WhatsApp'
          AND uc."NormalizedIdentifier" = requested.trusted_lid
          AND uc."UserProfileId" <> (SELECT profile_id FROM target_profile)
    ) THEN
        RAISE EXCEPTION 'deny: trusted LID belongs to another profile';
    END IF;
END
$ownership$;

-- Export this complete pre-change snapshot, the later LID mutation result,
-- and the complete post-change snapshot to access-controlled operational
-- evidence before any approved COMMIT.
CREATE TEMP TABLE whatsapp_channels_before ON COMMIT DROP AS
SELECT uc.*
FROM "UserChannels" AS uc
JOIN requested_whatsapp_aliases AS requested
  ON uc."ChannelType" = 'WhatsApp'
 AND uc."NormalizedIdentifier" IN (requested.trusted_e164, requested.trusted_lid);

CREATE TEMP TABLE whatsapp_channels_mutated (
    "Id" uuid,
    "UserProfileId" uuid,
    "ChannelType" character varying(32),
    "ExternalIdentifier" character varying(320),
    "NormalizedIdentifier" character varying(320),
    "IsEnabled" boolean,
    "MetadataJson" jsonb,
    "CreatedAt" timestamptz
) ON COMMIT DROP;

WITH changed AS (
    INSERT INTO "UserChannels" (
        "Id",
        "UserProfileId",
        "ChannelType",
        "ExternalIdentifier",
        "NormalizedIdentifier",
        "IsEnabled",
        "MetadataJson",
        "CreatedAt"
    )
    SELECT
        gen_random_uuid(),
        target.profile_id,
        'WhatsApp'::character varying(32),
        requested.trusted_lid,
        requested.trusted_lid,
        true,
        jsonb_build_object(
            'source', 'approved_manual_allowlist',
            'reason', 'whatsapp_lid_and_e164_authorized',
            'aliasKind', 'whatsapp_lid'
        ),
        now()
    FROM requested_whatsapp_aliases AS requested
    CROSS JOIN target_profile AS target
    ON CONFLICT ("ChannelType", "NormalizedIdentifier") DO UPDATE
        SET "ExternalIdentifier" = EXCLUDED."ExternalIdentifier",
            "IsEnabled" = EXCLUDED."IsEnabled"
        WHERE "UserChannels"."UserProfileId" = EXCLUDED."UserProfileId"
    RETURNING
        "Id",
        "UserProfileId",
        "ChannelType",
        "ExternalIdentifier",
        "NormalizedIdentifier",
        "IsEnabled",
        "MetadataJson",
        "CreatedAt"
)
INSERT INTO whatsapp_channels_mutated
SELECT * FROM changed;

DO $verification$
BEGIN
    IF (SELECT count(*) FROM whatsapp_channels_mutated) <> 1
       OR EXISTS (
           SELECT 1
           FROM whatsapp_channels_mutated AS mutated
           CROSS JOIN requested_whatsapp_aliases AS requested
           WHERE mutated."ChannelType" <> 'WhatsApp'
              OR mutated."ExternalIdentifier" <> requested.trusted_lid
              OR mutated."NormalizedIdentifier" <> requested.trusted_lid
              OR mutated."UserProfileId" <> (SELECT profile_id FROM target_profile)
              OR mutated."IsEnabled" IS NOT TRUE
       ) THEN
        RAISE EXCEPTION 'deny: mutation did not return exactly the intended LID row';
    END IF;

    IF (SELECT count(*)
        FROM "UserChannels" AS uc
        JOIN "UserProfiles" AS profile
          ON profile."Id" = uc."UserProfileId"
         AND profile."IsActive" = true
        CROSS JOIN requested_whatsapp_aliases AS requested
        WHERE uc."ChannelType" = 'WhatsApp'
          AND uc."ExternalIdentifier" = requested.trusted_e164
          AND uc."NormalizedIdentifier" = requested.trusted_e164
          AND uc."IsEnabled" = true
          AND uc."Id" = (SELECT trusted_e164_channel_id FROM target_profile)
          AND uc."UserProfileId" = (SELECT profile_id FROM target_profile)) <> 1
       OR (SELECT count(*)
           FROM "UserChannels" AS uc
           JOIN "UserProfiles" AS profile
             ON profile."Id" = uc."UserProfileId"
            AND profile."IsActive" = true
           CROSS JOIN requested_whatsapp_aliases AS requested
           WHERE uc."ChannelType" = 'WhatsApp'
             AND uc."ExternalIdentifier" = requested.trusted_lid
             AND uc."NormalizedIdentifier" = requested.trusted_lid
             AND uc."IsEnabled" = true
             AND uc."UserProfileId" = (SELECT profile_id FROM target_profile)) <> 1
       OR (SELECT "UserProfileId"
           FROM "UserChannels"
           WHERE "Id" = (SELECT trusted_e164_channel_id FROM target_profile))
          IS DISTINCT FROM
          (SELECT "UserProfileId" FROM whatsapp_channels_mutated)
       OR EXISTS (
           SELECT 1
           FROM "UserChannels" AS current_e164
           JOIN whatsapp_channels_before AS before_e164
             ON before_e164."Id" = current_e164."Id"
           WHERE current_e164."Id" = (SELECT trusted_e164_channel_id FROM target_profile)
             AND ROW(
                 current_e164."Id",
                 current_e164."UserProfileId",
                 current_e164."ChannelType",
                 current_e164."ExternalIdentifier",
                 current_e164."NormalizedIdentifier",
                 current_e164."IsEnabled",
                 current_e164."MetadataJson",
                 current_e164."CreatedAt"
             ) IS DISTINCT FROM ROW(
                 before_e164."Id",
                 before_e164."UserProfileId",
                 before_e164."ChannelType",
                 before_e164."ExternalIdentifier",
                 before_e164."NormalizedIdentifier",
                 before_e164."IsEnabled",
                 before_e164."MetadataJson",
                 before_e164."CreatedAt"
             )
       ) THEN
        RAISE EXCEPTION 'deny: final state is not one unchanged enabled E.164 row and one enabled LID row for the same active profile';
    END IF;
END
$verification$;

CREATE TEMP TABLE whatsapp_channels_after ON COMMIT DROP AS
SELECT uc.*
FROM "UserChannels" AS uc
JOIN requested_whatsapp_aliases AS requested
  ON uc."ChannelType" = 'WhatsApp'
 AND uc."NormalizedIdentifier" IN (requested.trusted_e164, requested.trusted_lid);

SELECT * FROM whatsapp_channels_before ORDER BY "NormalizedIdentifier";
SELECT * FROM whatsapp_channels_mutated ORDER BY "NormalizedIdentifier";
SELECT * FROM whatsapp_channels_after ORDER BY "NormalizedIdentifier";

-- COMMIT only after independently preserving and reviewing both result sets.
ROLLBACK;
```

`ROLLBACK` is intentional in the repository example. An authorized operator must make an explicit, reviewed decision to replace it with `COMMIT`.

## Operational evidence and verification

Keep actual sender mapping evidence and any preserved-message evidence in an access-controlled location. Refer to it by approved evidence ID, not by reproducing identifiers or file details in this plan.

After a separately authorized deployment and data change, verification should use the preserved artifact identified by that evidence record:

1. Verify the artifact path, byte count, and SHA-256 against access-controlled evidence.
2. Register with the exact allowlisted LID.
3. Upload the exact original bytes if registration requests upload.
4. Poll status until completed, safely duplicated, or rejected.
5. Confirm the intended audit event and exactly two enabled WhatsApp aliases on the active target profile.

Do not perform unrelated OCR, extraction, Moloni changes, direct document-system writes, or fallback processing.

## Rollback requirements

For an approved mutation, preserve:

- the full pre-change rows for both normalized identifiers;
- the full post-change rows for both normalized identifiers and the full LID row returned by the mutation;
- which returned IDs were newly inserted;
- the approved target profile ID and evidence reference.

Rollback must be conditional and reviewed:

- For every pre-existing row, require the complete current row—`Id`, `UserProfileId`, `ChannelType`, `ExternalIdentifier`, `NormalizedIdentifier`, `IsEnabled`, `MetadataJson`, and `CreatedAt`—to equal the complete recorded post-change row before restoring the complete pre-change snapshot. Abort on any field divergence.
- For a newly inserted LID row, require the complete current row, including all eight fields listed above, to equal the complete recorded post-change row before deleting it. Abort on any field divergence.
- Never change or transfer `UserProfileId` during rollback.
- Abort the rollback if ownership or any guarded current value differs from the recorded evidence.
- Verify afterward that each pre-existing row exactly matches its complete snapshot and each inserted row is absent.

Restoring a backup is the final recovery option if the guarded row-level rollback cannot be proven safe.

## Residual risks

- A valid LID-to-person mapping is operational evidence, not something Sibyla can infer.
- A mapping can become stale; aliases should be reviewed and disabled when no longer trusted.
- The SQL assumes the documented unique key on `("ChannelType", "NormalizedIdentifier")`; schema drift must be checked before any approved use.
- Code tests do not replace an approved, access-controlled end-to-end verification.