Free, Open Source, Self-Hosted WhatsApp API Gateway
TypeScript apibotgatewayself-hostedwhatsappwhatsapp-apiwhatsapp-automationwhatsapp-botwhatsapp-gatewaywhatsapp-gateway-apiwhatsapp-webwhatsapp-web-api
import { MigrationInterface, QueryRunner } from 'typeorm';
/**
* Enforces one row per (sessionId, waMessageId) on `messages` (issue #464): the engine can re-fire
* the inbound `message` event, and the old blind insert persisted the same WhatsApp message twice.
*
* Pre-existing duplicates are removed losslessly — a duplicate (sessionId, waMessageId) IS the same
* message, so the earliest row (createdAt ASC, id ASC) is kept and the rest deleted. Rows with a NULL
* waMessageId are exempt (SQL treats NULLs as distinct), preserving transient outgoing PENDING rows.
*
* Hand-authored because `synchronize` is off for the `data` connection. Idempotent + cross-dialect.
*/
export class AddMessagesWaMessageIdUnique1781300000000 implements MigrationInterface {
name = 'AddMessagesWaMessageIdUnique1781300000000';
public async up(queryRunner: QueryRunner): Promise<void> {
if (queryRunner.dataSource.options.type === 'postgres') {
// Boot and CLI migrations run on a pool without the runtime statement_timeout (pg-boot-migrations.ts);
// this guards the dedup DELETE / CREATE UNIQUE INDEX over the hot messages table against a role- or
// database-level default aborting it mid-flight. SET LOCAL is transaction-scoped (auto-reverts at
// COMMIT); SQLite rejects it syntactically, hence the guard.
await queryRunner.query('SET LOCAL statement_timeout = 0');
}
if (!(await queryRunner.hasTable('messages'))) return;
await queryRunner.query(
`DELETE FROM "messages" WHERE "waMessageId" IS NOT NULL AND "id" <> (` +
`SELECT m2."id" FROM "messages" m2 ` +
`WHERE m2."sessionId" = "messages"."sessionId" AND m2."waMessageId" = "messages"."waMessageId" ` +
`ORDER BY m2."createdAt" ASC, m2."id" ASC LIMIT 1)`,
);
await queryRunner.query(`DROP INDEX IF EXISTS "IDX_messages_sessionId_waMessageId"`);
await queryRunner.query(
`CREATE UNIQUE INDEX IF NOT EXISTS "UQ_messages_sessionId_waMessageId" ON "messages" ("sessionId", "waMessageId")`,
);
}
public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`DROP INDEX IF EXISTS "UQ_messages_sessionId_waMessageId"`);
await queryRunner.query(
`CREATE INDEX IF NOT EXISTS "IDX_messages_sessionId_waMessageId" ON "messages" ("sessionId", "waMessageId")`,
);
}
}