Incoming text MMS image failure #106

New issue

Open

opened 2026-07-13 19:41:25 +00:00 by eliribble · 4 comments

eliribble commented 2026-07-13 19:41:25 +00:00

Owner

Copy link

From dev-sync:

7:40PM INF Incoming text message age=8016984649 date="2026-07-13 15:40:38" files=https://voip.ms/media/MTc4Mzk3MTYzNzZhNTUzZjM1YzEwODI2YTU1M2YzNWMxMGJifDJ8aW1hZ2UvanBlZ3xNTVM=/media.jpeg id=9781002 name=4806162310
7:40PM INF incoming_request bytes_in= bytes_out=0 latency_ms=0.299167 method=GET remote_ip=72.251.239.208 status=0 url=/api/voipms/text
7:40PM INF Text status ID=9781002 content="testing MMS quality" event_type=message.received from=+18016984649 record_type=event to=+14806162310
7:40PM INF incoming_request bytes_in=447 bytes_out=2 latency_ms=0.299358 method=POST remote_ip=72.251.239.208 status=200 url=/api/voipms/text
7:40PM WRN unrecognized collect error error="ERROR: insert or update on table \"text_log\" violates foreign key constraint \"text_log_destination_fkey\" (SQLSTATE 23503)" msg="ERROR: insert or update on table \"text_log\" violates foreign
y constraint \"text_log_destination_fkey\" (SQLSTATE 23503)"
7:40PM WRN Error type info error="ERROR: insert or update on table \"text_log\" violates foreign key constraint \"text_log_destination_fkey\" (SQLSTATE 23503)" pkgPath= type=*pgconn.PgError
7:40PM ERR failed to handle VoIP.ms incoming text error="insert text log: collect rows: ERROR: insert or update on table \"text_log\" violates foreign key constraint \"text_log_destination_fkey\" (SQLSTATE 23503)"

From dev-sync: ``` 7:40PM INF Incoming text message age=8016984649 date="2026-07-13 15:40:38" files=https://voip.ms/media/MTc4Mzk3MTYzNzZhNTUzZjM1YzEwODI2YTU1M2YzNWMxMGJifDJ8aW1hZ2UvanBlZ3xNTVM=/media.jpeg id=9781002 name=4806162310 7:40PM INF incoming_request bytes_in= bytes_out=0 latency_ms=0.299167 method=GET remote_ip=72.251.239.208 status=0 url=/api/voipms/text 7:40PM INF Text status ID=9781002 content="testing MMS quality" event_type=message.received from=+18016984649 record_type=event to=+14806162310 7:40PM INF incoming_request bytes_in=447 bytes_out=2 latency_ms=0.299358 method=POST remote_ip=72.251.239.208 status=200 url=/api/voipms/text 7:40PM WRN unrecognized collect error error="ERROR: insert or update on table \"text_log\" violates foreign key constraint \"text_log_destination_fkey\" (SQLSTATE 23503)" msg="ERROR: insert or update on table \"text_log\" violates foreign y constraint \"text_log_destination_fkey\" (SQLSTATE 23503)" 7:40PM WRN Error type info error="ERROR: insert or update on table \"text_log\" violates foreign key constraint \"text_log_destination_fkey\" (SQLSTATE 23503)" pkgPath= type=*pgconn.PgError 7:40PM ERR failed to handle VoIP.ms incoming text error="insert text log: collect rows: ERROR: insert or update on table \"text_log\" violates foreign key constraint \"text_log_destination_fkey\" (SQLSTATE 23503)" ```

eliribble self-assigned this 2026-07-13 19:41:25 +00:00

eliribble commented 2026-07-13 19:42:33 +00:00

Author

Owner

Copy link

From database log:

[25-1]  [eliribble] nidus-sync LOG:  execute stmtcache_36bda89688eedca81e9f189aa8e164a20f31768a342fd7ec:
[25-2]         INSERT INTO comms.text_log (content, created, destination, is_welcome, origin, source, twilio_sid, twilio_status, is_visible_to_llm, deleted)
[25-3]         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
[25-4]         RETURNING text_log.content AS "text_log.content",
[25-5]                   text_log.created AS "text_log.created",
[25-6]                   text_log.destination AS "text_log.destination",
[25-7]                   text_log.id AS "text_log.id",
[25-8]                   text_log.is_welcome AS "text_log.is_welcome",                                                                                                       [25-9]                   text_log.origin AS "text_log.origin",                                                                                                              
[25-10]                   text_log.source AS "text_log.source",
[25-11]                   text_log.twilio_sid AS "text_log.twilio_sid",                                                                                                     
[25-12]                   text_log.twilio_status AS "text_log.twilio_status",
[25-13]                   text_log.is_visible_to_llm AS "text_log.is_visible_to_llm",                                                                                       [25-14]                   text_log.deleted AS "text_log.deleted";                                                                                                          
[25-15]
[25-16]  [eliribble] nidus-sync DETAIL:  Parameters: $1 = 'testing MMS quality', $2 = '2026-07-13 19:40:41.86648', $3 = '+14806162310', $4 = 'f', $5 = 'customer', $6 = '+18016984649', $7 = NULL, $8 = '', $9 = 'f', $10 = NULL
[26-1]  [eliribble] nidus-sync ERROR:  insert or update on table "text_log" violates foreign key constraint "text_log_destination_fkey"                                     
[26-2]  [eliribble] nidus-sync DETAIL:  Key (destination)=(+14806162310) is not present in table "phone".                                                                   [26-3]  [eliribble] nidus-sync STATEMENT:
[26-4]         INSERT INTO comms.text_log (content, created, destination, is_welcome, origin, source, twilio_sid, twilio_status, is_visible_to_llm, deleted)                
[26-5]         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
[26-6]         RETURNING text_log.content AS "text_log.content",                                                                                                             [26-7]                   text_log.created AS "text_log.created",                                                                                                            
[26-8]                   text_log.destination AS "text_log.destination",
[26-9]                   text_log.id AS "text_log.id",                                                                                                                      
[26-10]                   text_log.is_welcome AS "text_log.is_welcome",
[26-11]                   text_log.origin AS "text_log.origin",                                                                                                             
[26-12]                   text_log.source AS "text_log.source",                                                                                                             
[26-13]                   text_log.twilio_sid AS "text_log.twilio_sid",
[26-14]                   text_log.twilio_status AS "text_log.twilio_status",                                                                                               
[26-15]                   text_log.is_visible_to_llm AS "text_log.is_visible_to_llm",
[26-16]                   text_log.deleted AS "text_log.deleted";                                                                                                           
[26-17]

From database log: ``` [25-1] [eliribble] nidus-sync LOG: execute stmtcache_36bda89688eedca81e9f189aa8e164a20f31768a342fd7ec: [25-2] INSERT INTO comms.text_log (content, created, destination, is_welcome, origin, source, twilio_sid, twilio_status, is_visible_to_llm, deleted) [25-3] VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) [25-4] RETURNING text_log.content AS "text_log.content", [25-5] text_log.created AS "text_log.created", [25-6] text_log.destination AS "text_log.destination", [25-7] text_log.id AS "text_log.id", [25-8] text_log.is_welcome AS "text_log.is_welcome", [25-9] text_log.origin AS "text_log.origin", [25-10] text_log.source AS "text_log.source", [25-11] text_log.twilio_sid AS "text_log.twilio_sid", [25-12] text_log.twilio_status AS "text_log.twilio_status", [25-13] text_log.is_visible_to_llm AS "text_log.is_visible_to_llm", [25-14] text_log.deleted AS "text_log.deleted"; [25-15] [25-16] [eliribble] nidus-sync DETAIL: Parameters: $1 = 'testing MMS quality', $2 = '2026-07-13 19:40:41.86648', $3 = '+14806162310', $4 = 'f', $5 = 'customer', $6 = '+18016984649', $7 = NULL, $8 = '', $9 = 'f', $10 = NULL [26-1] [eliribble] nidus-sync ERROR: insert or update on table "text_log" violates foreign key constraint "text_log_destination_fkey" [26-2] [eliribble] nidus-sync DETAIL: Key (destination)=(+14806162310) is not present in table "phone". [26-3] [eliribble] nidus-sync STATEMENT: [26-4] INSERT INTO comms.text_log (content, created, destination, is_welcome, origin, source, twilio_sid, twilio_status, is_visible_to_llm, deleted) [26-5] VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) [26-6] RETURNING text_log.content AS "text_log.content", [26-7] text_log.created AS "text_log.created", [26-8] text_log.destination AS "text_log.destination", [26-9] text_log.id AS "text_log.id", [26-10] text_log.is_welcome AS "text_log.is_welcome", [26-11] text_log.origin AS "text_log.origin", [26-12] text_log.source AS "text_log.source", [26-13] text_log.twilio_sid AS "text_log.twilio_sid", [26-14] text_log.twilio_status AS "text_log.twilio_status", [26-15] text_log.is_visible_to_llm AS "text_log.is_visible_to_llm", [26-16] text_log.deleted AS "text_log.deleted"; [26-17] ```

ned commented 2026-07-13 19:44:05 +00:00

Member

Copy link

Hi-diddly-ho neighborino! After tracing through the code, here is the root cause analysis:

The Bug: In platform/text/text.go, HandleTextMessage ensures the source number (VoIP.ms from field, i.e. the customer's number) is in the phone table via ensurePhoneInDB, but it never ensures the destination number (VoIP.ms to field, i.e. our DID +14806162310) is present in the phone table. Since text_log has a foreign key constraint text_log_destination_fkey → phone(e164), the insert fails when our DID is not yet registered.

Code Flow:

1. api/voipms.go:voipmsTextPost calls text.HandleTextMessage(ctx, from, to, body) — from=+18016984649, to=+14806162310
2. platform/text/text.go:HandleTextMessage does ensurePhoneInDB(ctx, txn, src) for the source (+18016984649) — ✅
3. Then inserts text_log{source: s.E164 (+18016984649), destination: dst.PhoneString() (+14806162310), origin: Customer} — ❌ destination is not in phone

The Fix: In HandleTextMessage, we also need to call ensurePhoneInDB for the destination number before inserting the text_log. Since ensurePhoneInDB calls PhoneInsertIfNotExists, it is safe to call even if the record already exists. This would look something like:

// Ensure the destination (our DID that received the text) is in the phone table
_, err = ensurePhoneInDB(ctx, txn, dst)
if err != nil {
    return fmt.Errorf("ensure destination in DB: %w", err)
}

Or alternatively, the system could use StoreSources() (which runs at startup) to pre-register all known DIDs. If +14806162310 was added to config and stored at startup, this insert would not fail. But the explicit ensure is more defensive.

I will happily create a PR with the fix.

Hi-diddly-ho neighborino! After tracing through the code, here is the root cause analysis: **The Bug:** In `platform/text/text.go`, `HandleTextMessage` ensures the **source** number (VoIP.ms `from` field, i.e. the customer's number) is in the `phone` table via `ensurePhoneInDB`, but it never ensures the **destination** number (VoIP.ms `to` field, i.e. our DID `+14806162310`) is present in the `phone` table. Since `text_log` has a foreign key constraint `text_log_destination_fkey` → `phone(e164)`, the insert fails when our DID is not yet registered. **Code Flow:** 1. `api/voipms.go:voipmsTextPost` calls `text.HandleTextMessage(ctx, from, to, body)` — `from`=`+18016984649`, `to`=`+14806162310` 2. `platform/text/text.go:HandleTextMessage` does `ensurePhoneInDB(ctx, txn, src)` for the source (`+18016984649`) — ✅ 3. Then inserts `text_log{source: s.E164 (+18016984649), destination: dst.PhoneString() (+14806162310), origin: Customer}` — ❌ destination is not in `phone` **The Fix:** In `HandleTextMessage`, we also need to call `ensurePhoneInDB` for the destination number before inserting the text_log. Since `ensurePhoneInDB` calls `PhoneInsertIfNotExists`, it is safe to call even if the record already exists. This would look something like: ```go // Ensure the destination (our DID that received the text) is in the phone table _, err = ensurePhoneInDB(ctx, txn, dst) if err != nil { return fmt.Errorf("ensure destination in DB: %w", err) } ``` Or alternatively, the system could use `StoreSources()` (which runs at startup) to pre-register all known DIDs. If `+14806162310` was added to config and stored at startup, this insert would not fail. But the explicit ensure is more defensive. I will happily create a PR with the fix.

ned referenced this issue from a pull request that will close it, 2026-07-13 19:44:33 +00:00

fix: ensure destination DID is in phone table before text_log insert #107

eliribble commented 2026-07-13 20:04:26 +00:00

Author

Owner

Copy link

Fixed adding the configured VoipMS address, then got:

[18-16]  [eliribble] nidus-sync DETAIL:  Parameters: $1 = 'I have to start with either ''YES'' or ''STOP'' first, Which do you want?', $2 = '2026-07-13 20:00:21.254625', $3 = '+18016984649', $4 = 'f', $5 = 'command-response', $6 = '+16235525879', $7 = NULL, $8 = '', $9 = 'f', $10 = NULL
[19-1]  [eliribble] nidus-sync ERROR:  insert or update on table "text_log" violates foreign key constraint "text_log_source_fkey"
[19-2]  [eliribble] nidus-sync DETAIL:  Key (source)=(+16235525879) is not present in table "phone".
[19-3]  [eliribble] nidus-sync STATEMENT:
[19-4]         INSERT INTO comms.text_log (content, created, destination, is_welcome, origin, source, twilio_sid, twilio_status, is_visible_to_llm, deleted)
[19-5]         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
[19-6]         RETURNING text_log.content AS "text_log.content",
[19-7]                   text_log.created AS "text_log.created",
[19-8]                   text_log.destination AS "text_log.destination",
[19-9]                   text_log.id AS "text_log.id",
[19-10]                   text_log.is_welcome AS "text_log.is_welcome",
[19-11]                   text_log.origin AS "text_log.origin",
[19-12]                   text_log.source AS "text_log.source",
[19-13]                   text_log.twilio_sid AS "text_log.twilio_sid",
[19-14]                   text_log.twilio_status AS "text_log.twilio_status",
[19-15]                   text_log.is_visible_to_llm AS "text_log.is_visible_to_llm",
[19-16]                   text_log.deleted AS "text_log.deleted";
[19-17]

Fixed adding the configured VoipMS address, then got: ``` [18-16] [eliribble] nidus-sync DETAIL: Parameters: $1 = 'I have to start with either ''YES'' or ''STOP'' first, Which do you want?', $2 = '2026-07-13 20:00:21.254625', $3 = '+18016984649', $4 = 'f', $5 = 'command-response', $6 = '+16235525879', $7 = NULL, $8 = '', $9 = 'f', $10 = NULL [19-1] [eliribble] nidus-sync ERROR: insert or update on table "text_log" violates foreign key constraint "text_log_source_fkey" [19-2] [eliribble] nidus-sync DETAIL: Key (source)=(+16235525879) is not present in table "phone". [19-3] [eliribble] nidus-sync STATEMENT: [19-4] INSERT INTO comms.text_log (content, created, destination, is_welcome, origin, source, twilio_sid, twilio_status, is_visible_to_llm, deleted) [19-5] VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) [19-6] RETURNING text_log.content AS "text_log.content", [19-7] text_log.created AS "text_log.created", [19-8] text_log.destination AS "text_log.destination", [19-9] text_log.id AS "text_log.id", [19-10] text_log.is_welcome AS "text_log.is_welcome", [19-11] text_log.origin AS "text_log.origin", [19-12] text_log.source AS "text_log.source", [19-13] text_log.twilio_sid AS "text_log.twilio_sid", [19-14] text_log.twilio_status AS "text_log.twilio_status", [19-15] text_log.is_visible_to_llm AS "text_log.is_visible_to_llm", [19-16] text_log.deleted AS "text_log.deleted"; [19-17] ```

ned commented 2026-07-13 20:06:52 +00:00

Member

Copy link

The text_log_source_fkey failure has the same root cause as the original text_log_destination_fkey — a foreign key on phone(e164) being violated — but on the outgoing path rather than the incoming one.

What was happening:

• Incoming texts go through HandleTextMessage, which calls ensurePhoneInDB for both source (customer) and destination (our DID). That path already had the destination fix.
• Outgoing texts (command responses, initial texts, district messages) go through sendTextDirect, which sets Source: config.PhoneNumberReportStr (our outbound number) but never ensured it existed in the phone table. Same for the destination.
• The StoreSources() startup function is supposed to pre-register this, but if it hasn't run (e.g. after a DB reset, or because it errored out on a misconfigured VoipMS number), the FK constraint fires.

Fix: Added ensurePhoneInDB calls for both source and destination inside sendTextDirect itself, so every outbound text path is covered regardless of call site. This is the same pattern used by HandleTextMessage for incoming texts.

Already pushed to the same PR: #107

The `text_log_source_fkey` failure has the same root cause as the original `text_log_destination_fkey` — a foreign key on `phone(e164)` being violated — but on the outgoing path rather than the incoming one. **What was happening:** - Incoming texts go through `HandleTextMessage`, which calls `ensurePhoneInDB` for both source (customer) and destination (our DID). That path already had the destination fix. - Outgoing texts (command responses, initial texts, district messages) go through `sendTextDirect`, which sets `Source: config.PhoneNumberReportStr` (our outbound number) but **never** ensured it existed in the `phone` table. Same for the destination. - The `StoreSources()` startup function is supposed to pre-register this, but if it hasn't run (e.g. after a DB reset, or because it errored out on a misconfigured VoipMS number), the FK constraint fires. **Fix:** Added `ensurePhoneInDB` calls for both source and destination inside `sendTextDirect` itself, so every outbound text path is covered regardless of call site. This is the same pattern used by `HandleTextMessage` for incoming texts. Already pushed to the same PR: https://source.gleipnir.technology/Gleipnir/nidus-sync/pulls/107

Sign in to join this conversation.

No Branch/Tag specified

Branches Tags

main

fix/signin-stale-impersonation

issue-164-overview-map

worried-pug

add-ts-ui-tests-action

implement-maintenance-mode

investigate-planet-mosaics-new-area-sync

upbeat-turtle-2

quiet-peacock

light-rat-2

light-rat

upbeat-turtle

reveal-ui-bundle

smart-baboon

snowy-gopher

bitter-moth

famous-mantis

evil-donkey-continued

elegant-mule

exciting-sheep

sleek-fireant

magical-armadillo

aquatic-monkey

issue-176-n-items

issue-176-reveal-ui-redesign

issue-184-nearmap-tiles

issue-183-crop-data

issue-13-train-data-api

issue-180-skip-trace-design-doc

issue-174-reveal-ui-mockup

issue-166-communication-search

issue-165-address-reporter-list-view

issue-160-h3cell-bad-data

issue-155-feature-observation-parser-v2

issue-155-feature-observation-parser

issue-152-land-use-soil-data

fix/issue-134-ci-ssh-auth

fix/golint-ci-submodule-auth

fix/golangci-lint-unstable

fix/incoming-text-missing-destination-phone

fix/csv-import-missing-srid-4326

79

78

77

76

75

74

73

72

70

50

48

46

45

_backup/pre-rewrite-tag44

44

42

41

40

39

38

37

35

34

33

v0.0.5

v0.0.4

32

31

30

29

28

27

26

25

24

v0.0.23

v0.0.22

v0.0.21

v0.0.20

v0.0.19

0.0.18

0.0.17

0.0.16

0.0.15

0.0.14

0.0.13

0.0.12

0.0.11

0.0.10

0.0.9

0.0.8

0.0.7

v0.0.6

v0.0.3

v0.0.2

v0.0.1

Labels

Clear labels

  

customer

Customers are aware of, and affected by the issue

  

prod

Issue has manifested in production

  

repro'd

Has been reproduced by engineering

  

retest

Issue is ready for retesting in staging

  

staging

Issue has manifested in staging

No labels

customer

prod

repro'd

retest

staging

Milestone

Clear milestone

No items

No milestone

Projects

Clear projects

No items

No project

Assignees

Clear assignees

No assignees

eliribble

2 participants

Notifications

Subscribe

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

Gleipnir/nidus-sync#106

No description provided.