fix(meshcore): widen neighbor_info timestamp columns to BIGINT (int32 overflow) by Yeraze · Pull Request #3602 · Yeraze/meshmonitor

Yeraze · 2026-06-21T15:59:05Z

Problem

A production PostgreSQL deployment crashes in MeshCoreRepository.getNeighbors (surfaced as "Error fetching MeshCore neighbors" via meshcoreRoutes.ts):

DrizzleQueryError: Failed query: select ... from "meshcore_neighbor_info"
where ("sourceId" in ($1) and "timestamp" >= $2) order by "timestamp" desc
params: [ 'b4e6c07e-...', 1781969045993 ]
cause: error: value "1781969045993" is out of range for type integer
       (code 22003, pg_strtoint32_safe, numutils.c)

Root cause

The meshcore_neighbor_info table (created in migration 073) declared its timestamp and createdAt columns as 32-bit INTEGER on PostgreSQL and INT on MySQL. Both columns store millisecond-epoch values written via Date.now() in MeshCoreRepository.insertNeighborsBatch (e.g. 1781969045993), which overflows the signed 32-bit max of 2,147,483,647.

SQLite was unaffected because its INTEGER is 64-bit / dynamically typed. This is the same int32-overflow class documented in CLAUDE.md ("Node IDs / packet IDs are BIGINT in PostgreSQL/MySQL") — ms timestamps share the defect.

This table was the lone offender: the sibling meshcore_messages, meshcore_nodes, and meshcore_packet_log tables already use bigint(..., { mode: 'number' }) for their ms-epoch columns. A full audit of every PG pgInteger/MySQL myInt column with a timestamp-shaped name confirmed no other ms-epoch columns remain mis-typed.

Columns changed

Table	Column	Before (PG / MySQL)	After
`meshcore_neighbor_info`	`timestamp`	`integer` / `int`	`bigint` / `bigint`
`meshcore_neighbor_info`	`createdAt`	`integer` / `int`	`bigint` / `bigint`

Deliberately left as int: lastHeardSecs (small "seconds-ago" value, not ms-epoch), id, snr, rssi — none can overflow.

Changes

src/db/schema/meshcoreNeighbors.ts — PG/MySQL timestamp + createdAt → bigint('...', { mode: 'number' }). SQLite unchanged (64-bit already). mode: 'number' keeps reads as JS numbers, matching meshcore_packet_log, so no read-site Number() coercion was needed.
src/server/migrations/095_meshcore_neighbor_timestamp_bigint.ts — idempotent migration:
- PostgreSQL: ALTER TABLE ... ALTER COLUMN ... TYPE BIGINT, guarded by information_schema.columns data-type check.
- MySQL: ALTER TABLE ... MODIFY COLUMN ... BIGINT NOT NULL, guarded by information_schema.COLUMNS check.
- SQLite: no-op (INTEGER is already 64-bit).
Registered migration 095 in src/db/migrations.ts; bumped src/db/migrations.test.ts count to 95 and last-name assertion.

Verification

Full Vitest suite: 21199 passed, 0 failed (exit 0).
tsc -p tsconfig.server.json --noEmit: no new errors (only the ~5 pre-existing TelemetryChart.tsx frontend errors remain).

🤖 Generated with Claude Code

… overflow) The meshcore_neighbor_info table (migration 073) declared its `timestamp` and `createdAt` columns as 32-bit INTEGER on PostgreSQL and INT on MySQL. Both columns store millisecond-epoch values written via `Date.now()` in MeshCoreRepository.insertNeighborsBatch (e.g. 1781969045993), which overflow signed 32-bit integers (max 2,147,483,647). Production PostgreSQL deployments crashed in MeshCoreRepository.getNeighbors ("Error fetching MeshCore neighbors"): DrizzleQueryError: Failed query: select ... from "meshcore_neighbor_info" where ("sourceId" in ($1) and "timestamp" >= $2) order by "timestamp" desc cause: error: value "1781969045993" is out of range for type integer (code 22003, pg_strtoint32_safe, numutils.c) SQLite was unaffected (its INTEGER is 64-bit / dynamically typed). Changes: - src/db/schema/meshcoreNeighbors.ts: PG `timestamp`/`createdAt` -> pgBigint (mode 'number'); MySQL `timestamp`/`createdAt` -> myBigint (mode 'number'). Matches the sibling meshcore_messages / meshcore_nodes / meshcore_packet_log ms-epoch convention. `lastHeardSecs` (seconds-ago, small int) left as-is. - src/server/migrations/095_meshcore_neighbor_timestamp_bigint.ts: idempotent ALTER ... TYPE BIGINT (PG) / MODIFY ... BIGINT NOT NULL (MySQL), guarded by information_schema checks; SQLite no-op. - Registered migration 095; bumped migrations.test.ts count to 95. mode:'number' keeps reads as JS numbers, so no read-site Number() coercion is required (consistent with meshcore_packet_log). Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_011JEaCGwY9Wz8jeV4e22GW4

github-actions · 2026-06-21T15:59:20Z

Claude finished @Yeraze's task —— View job

PR Review: fix(meshcore): widen neighbor_info timestamp columns to BIGINT

Overall: LGTM — This is a clean, well-targeted bug fix. The root cause diagnosis is correct and the fix is minimal and surgical.

Schema (`src/db/schema/meshcoreNeighbors.ts`)

The changes are correct and consistent with the existing pattern:

SQLite integer correctly left unchanged (64-bit already)
PostgreSQL pgBigint('...', { mode: 'number' }) and MySQL myBigint('...', { mode: 'number' }) correctly match the pattern used by meshcore_messages, meshcore_nodes, and meshcore_packet_log
mode: 'number' means no read-site coercion is needed, which is consistent with how the returned timestamp is used at meshcoreRoutes.ts:3050 and in getNeighbors
The columns correctly left as int (lastHeardSecs, id, snr, rssi) are all genuinely small values that can't overflow

One very minor nit: the inline comments in the schema file (// ms-epoch timestamps overflow 32-bit INTEGER...) are helpful and appropriate given this is a known pitfall documented in CLAUDE.md.

Migration (`src/server/migrations/095_meshcore_neighbor_timestamp_bigint.ts`)

The migration is well-structured and follows the established patterns:

Idempotency: Both PostgreSQL and MySQL variants check information_schema before altering, so re-running is safe ✓
PostgreSQL: Uses information_schema.columns with data_type check before ALTER COLUMN ... TYPE BIGINT ✓
MySQL: Uses information_schema.COLUMNS with DATA_TYPE check before MODIFY COLUMN ... BIGINT NOT NULL — correctly preserves NOT NULL constraint from the original DDL ✓
SQLite: Explicit no-op with explanatory log message ✓
ESLint suppressions: Raw DDL exemptions are correctly scoped and annotated ✓

One observation: The PostgreSQL ALTER TABLE uses an unquoted ${TABLE} but a quoted "${col}" — this is fine since table names are lowercase, but it's slightly inconsistent. Not a bug.

Migration Registry (`src/db/migrations.ts`)

Migration 095 is correctly registered at line 1514–1521
settingsKey follows the established naming convention
The import aliases are appropriately descriptive

Tests (`src/db/migrations.test.ts`)

Count bumped to 95 ✓
Last migration name assertion updated ✓
Sequential numbering test will pass ✓
all migrations have sqlite, postgres, and mysql functions test will pass since all three variants are provided ✓

No concerns found

No new tests are needed beyond the migration count assertion (there's no logic to test, just DDL)
The fix is orthogonal to source scoping — sourceId was already correct on this table since migration 073
The PR description's audit claim ("no other ms-epoch columns remain mis-typed") is credible given the sibling tables already used bigint

This PR is ready to merge.

…p-bigint # Conflicts: # src/db/migrations.test.ts # src/db/migrations.ts

github-actions · 2026-06-21T16:13:10Z

Claude finished @Yeraze's task —— View job

PR Review: fix(meshcore): widen neighbor_info timestamp columns to BIGINT

Overall: LGTM — This is a clean, well-targeted bug fix. The root cause diagnosis is correct and the fix is minimal and surgical.

Note: The PR description references migration 095, but on the current branch it was renumbered to 096 (likely due to another migration being merged to main in the interim). All files are internally consistent with number 096.

Schema (`src/db/schema/meshcoreNeighbors.ts`)

The changes are correct and consistent with the established pattern:

SQLite integer correctly left unchanged (64-bit already) ✓
PostgreSQL pgBigint('...', { mode: 'number' }) and MySQL myBigint('...', { mode: 'number' }) correctly match the pattern used by meshcore_messages, meshcore_nodes, and meshcore_packet_log ✓
mode: 'number' means no read-site coercion is needed, matching how timestamp is consumed downstream ✓
Columns correctly left as int (lastHeardSecs, id, snr, rssi) are all genuinely small values that can't overflow ✓
Inline comments explaining the overflow rationale are helpful and appropriate for this known pitfall ✓

Migration (`src/server/migrations/096_meshcore_neighbor_timestamp_bigint.ts`)

The migration is well-structured and follows established patterns:

Idempotency: Both PostgreSQL and MySQL variants check information_schema before altering, so re-running is safe ✓
PostgreSQL: Uses information_schema.columns with data_type check before ALTER COLUMN ... TYPE BIGINT ✓
MySQL: Uses information_schema.COLUMNS with DATA_TYPE check before MODIFY COLUMN ... BIGINT NOT NULL — correctly preserves NOT NULL constraint from the original DDL ✓
SQLite: Explicit no-op with explanatory log message ✓
ESLint suppressions: Raw DDL exemptions are correctly scoped and annotated ✓
MySQL connection handling: conn.release() is in a finally block, ensuring no connection leaks even if an error is thrown mid-loop ✓

One minor observation: The PostgreSQL ALTER TABLE uses an unquoted ${TABLE} but a quoted "${col}". Since TABLE = 'meshcore_neighbor_info' is lowercase-only this is safe, but quoting both would be slightly more consistent. Not a bug.

Migration Registry (`src/db/migrations.ts`)

Migration 096 correctly registered with descriptive import aliases ✓
settingsKey follows the migration_NNN_name naming convention ✓

Tests (`src/db/migrations.test.ts`)

Count bumped to 96 ✓
Last migration name assertion updated to meshcore_neighbor_timestamp_bigint ✓
Sequential numbering test (migrations are sequentially numbered from 1 to 96) will pass ✓
all migrations have sqlite, postgres, and mysql functions test will pass since all three variants are provided ✓

No concerns found

No additional tests are needed beyond the migration count assertion — there is no logic to test, only DDL
The fix is orthogonal to source scoping — sourceId was already correct on this table since migration 073
The audit claim ("no other ms-epoch columns remain mis-typed") is credible given the sibling tables already used bigint

This PR is ready to merge.

Bump version to 4.11.3 across all five version files and document the changes merged since 4.11.2 in the changelog. Features: - Dead Drop / Mailbox auto-responder (#3538) - MeshCore node favoriting (#3588) - FEM LNA Mode LoRa config on Device Config + Remote Admin (#3599) - MeshCore CLI bundled in the Docker image (#3587) Bug fixes: - meshcore_neighbor_info timestamp BIGINT — PG/MySQL int32 overflow crash (#3602) - downlink/uplinkEnabled proto3 boolean elision revert (#3594) - Position-history SNR for directly-heard (0-hop) nodes (#3590) - MeshCore auto-ack {SNR}/{ROUTE} tokens intermittently blank (#3589) - macOS x64 DMG re2.node arch (Intel Mac crash) (#3603) - Mesh request endpoints return 503 when disconnected (#3596) Also refreshes the CLAUDE.md version header and migration count (96). Claude-Session: https://claude.ai/code/session_011JEaCGwY9Wz8jeV4e22GW4 Co-authored-by: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Merge remote-tracking branch 'origin/main' into fix/meshcore-timestam…

1e80779

…p-bigint # Conflicts: # src/db/migrations.test.ts # src/db/migrations.ts

Yeraze merged commit 2bf6720 into main Jun 21, 2026
20 checks passed

Yeraze deleted the fix/meshcore-timestamp-bigint branch June 21, 2026 16:39

Yeraze mentioned this pull request Jun 21, 2026

release: v4.11.3 #3605

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

fix(meshcore): widen neighbor_info timestamp columns to BIGINT (int32 overflow)#3602

fix(meshcore): widen neighbor_info timestamp columns to BIGINT (int32 overflow)#3602
Yeraze merged 2 commits into
mainfrom
fix/meshcore-timestamp-bigint

Yeraze commented Jun 21, 2026

Uh oh!

github-actions Bot commented Jun 21, 2026 •

edited

Loading

Uh oh!

github-actions Bot commented Jun 21, 2026 •

edited

Loading

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

Uh oh!

Conversation

Yeraze commented Jun 21, 2026

Problem

Root cause

Columns changed

Changes

Verification

Uh oh!

github-actions Bot commented Jun 21, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

PR Review: fix(meshcore): widen neighbor_info timestamp columns to BIGINT

Schema (src/db/schema/meshcoreNeighbors.ts)

Migration (src/server/migrations/095_meshcore_neighbor_timestamp_bigint.ts)

Migration Registry (src/db/migrations.ts)

Tests (src/db/migrations.test.ts)

No concerns found

Uh oh!

github-actions Bot commented Jun 21, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

PR Review: fix(meshcore): widen neighbor_info timestamp columns to BIGINT

Schema (src/db/schema/meshcoreNeighbors.ts)

Migration (src/server/migrations/096_meshcore_neighbor_timestamp_bigint.ts)

Migration Registry (src/db/migrations.ts)

Tests (src/db/migrations.test.ts)

No concerns found

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

github-actions Bot commented Jun 21, 2026 •

edited

Loading

Schema (`src/db/schema/meshcoreNeighbors.ts`)

Migration (`src/server/migrations/095_meshcore_neighbor_timestamp_bigint.ts`)

Migration Registry (`src/db/migrations.ts`)

Tests (`src/db/migrations.test.ts`)

github-actions Bot commented Jun 21, 2026 •

edited

Loading

Schema (`src/db/schema/meshcoreNeighbors.ts`)

Migration (`src/server/migrations/096_meshcore_neighbor_timestamp_bigint.ts`)

Migration Registry (`src/db/migrations.ts`)

Tests (`src/db/migrations.test.ts`)