Free, Open Source, Self-Hosted WhatsApp API Gateway
TypeScript apibotgatewayself-hostedwhatsappwhatsapp-apiwhatsapp-automationwhatsapp-botwhatsapp-gatewaywhatsapp-gateway-apiwhatsapp-webwhatsapp-web-api
/* istanbul ignore file -- PG-gated: only runs under DATABASE_TYPE=postgres (test-postgres CI job);
skipped in the default test job, so its lines would be unread and skew the global coverage gate. */
import 'reflect-metadata';
import { DataSource, EntitySchema, LessThan } from 'typeorm';
import type { ClientBase } from 'pg';
import { assertDataConnectionUtc, postgresUtcExtra } from './postgres-utc';
import { InfraDataService, sqliteDatetimeColumns, toSqliteDatetime } from '../modules/infra/infra-data.service';
import { Session, SessionStatus } from '../modules/session/entities/session.entity';
import { Webhook } from '../modules/webhook/entities/webhook.entity';
import { Message, MessageDirection, MessageStatus } from '../modules/message/entities/message.entity';
import { MessageBatch } from '../modules/message/entities/message-batch.entity';
import { Template } from '../modules/template/entities/template.entity';
import { BaileysStoredMessage } from '../engine/adapters/baileys-stored-message.entity';
import { LidMapping } from '../engine/identity/lid-mapping.entity';
import { ChatState } from '../engine/adapters/baileys-chat-state.entity';
import { PluginInstance } from '../modules/integration/entities/plugin-instance.entity';
import { ConversationMapping } from '../modules/integration/entities/conversation-mapping.entity';
import { IngressEvent } from '../modules/integration/entities/ingress-event.entity';
import { WebhookDeliveryFailure } from '../modules/webhook/entities/webhook-delivery-failure.entity';
import { WebhookOutboxEvent } from '../modules/webhook/entities/webhook-outbox-event.entity';
import { WebhookOutboxService } from '../modules/webhook/webhook-outbox.service';
import { IntegrationDeliveryFailure } from '../modules/integration/entities/integration-delivery-failure.entity';
import { StatusUpdate } from '../modules/status-store/entities/status-update.entity';
import { AutomationRule } from '../modules/automation/entities/automation-rule.entity';
import { StatsService } from '../modules/stats/stats.service';
import type { MigrationTables } from '../modules/infra/migration-tables.types';
/**
* Runtime proof of the UTC pin against a real PostgreSQL, with the process deliberately OFF UTC.
*
* Off UTC the two halves of a row used to disagree: `DEFAULT now()` wrote the server's zone while the
* driver bound and parsed everything else in the process's zone, so a backup shifted by the offset on
* every restore and a retention window measured a cutoff that never happened. Everything here fails by
* exactly that offset if any one of the three pins (bind, parse, session) is removed.
*
* Gating: mirrors the repo's PG harness convention (DATABASE_TYPE=postgres, the "Test (PostgreSQL
* migrations)" CI job); the CI step additionally sets TZ, which the first test asserts rather than
* trusts, because on a UTC host every assertion below would hold for the wrong reason.
*/
const POSTGRES_ENABLED = process.env.DATABASE_TYPE === 'postgres';
// Fixed uuids: every primary key on this connection is a real uuid column.
const SESSION_ID = '11111111-1111-4111-8111-111111111111';
const OLD_ID = '22222222-2222-4222-8222-222222222222';
const YOUNG_ID = '33333333-3333-4333-8333-333333333333';
const BEFORE_ID = '44444444-4444-4444-8444-444444444444';
const AFTER_ID = '55555555-5555-4555-8555-555555555555';
/** An instant as the naive UTC text a pinned server writes into a `timestamp` column. */
const utcWallText = (at: Date): string => at.toISOString().replace('T', ' ').replace('Z', '');
const ENTITIES = [
Session,
Webhook,
Message,
MessageBatch,
Template,
BaileysStoredMessage,
LidMapping,
ChatState,
PluginInstance,
ConversationMapping,
IngressEvent,
WebhookDeliveryFailure,
WebhookOutboxEvent,
IntegrationDeliveryFailure,
StatusUpdate,
AutomationRule,
];
/**
* The `sessions` table as a SQLite deployment holds it: TypeORM's own `datetime` text for the
* create/update dates, and plain `text` for the columns DateTransformer writes as ISO. Spelled out
* rather than reusing the entity classes because those resolve their column types from DATABASE_TYPE
* at import time, which is `postgres` for this suite.
*/
const SqliteSession = new EntitySchema<Record<string, unknown>>({
name: 'SqliteSession',
tableName: 'sessions',
columns: {
id: { primary: true, type: 'varchar' },
name: { type: 'varchar' },
status: { type: 'varchar' },
phone: { type: 'varchar', nullable: true },
pushName: { type: 'varchar', nullable: true },
config: { type: 'text' },
proxyUrl: { type: 'varchar', nullable: true },
proxyType: { type: 'varchar', nullable: true },
connectedAt: { type: 'text', nullable: true },
lastActiveAt: { type: 'text', nullable: true },
createdAt: { type: 'datetime', createDate: true },
updatedAt: { type: 'datetime', updateDate: true },
},
});
(POSTGRES_ENABLED ? describe : describe.skip)('postgres UTC pin (real server, non-UTC process)', () => {
let ds: DataSource;
let sqlite: DataSource;
let infra: InfraDataService;
const connectionOptions = {
host: process.env.DATABASE_HOST || 'localhost',
port: Number(process.env.DATABASE_PORT || 5432),
username: process.env.DATABASE_USERNAME || 'openwa',
password: process.env.DATABASE_PASSWORD || 'openwa',
database: process.env.DATABASE_NAME || 'openwa',
};
// exportData only reads dataDatabase.type; stats reads its memo TTL, which 0 disables so a second
// call re-queries instead of replaying the first answer.
const cfg = {
get: (key: string, def?: unknown) =>
key === 'dataDatabase.type' ? 'postgres' : key === 'stats.cacheTtlMs' ? 0 : def,
};
beforeAll(async () => {
const admin = new DataSource({ type: 'postgres', ...connectionOptions });
await admin.initialize();
await admin.query('DROP SCHEMA IF EXISTS public CASCADE');
await admin.query('CREATE SCHEMA public');
await admin.destroy();
ds = new DataSource({
type: 'postgres',
...connectionOptions,
entities: ENTITIES,
synchronize: true,
extra: postgresUtcExtra(),
});
await ds.initialize();
infra = new InfraDataService(cfg as never, ds, undefined, undefined, undefined, undefined);
sqlite = new DataSource({ type: 'better-sqlite3', database: ':memory:', entities: [SqliteSession] });
await sqlite.initialize();
await sqlite.synchronize();
}, 120_000);
afterAll(async () => {
if (ds?.isInitialized) await ds.destroy();
if (sqlite?.isInitialized) await sqlite.destroy();
});
beforeEach(async () => {
await ds.query('DELETE FROM messages');
await ds.query('DELETE FROM webhook_outbox_events');
await ds.query('DELETE FROM sessions');
await sqlite.query('DELETE FROM sessions');
});
/** A session whose app-written columns hold a known instant; `createdAt` comes from DEFAULT now(). */
const seedSession = async (id: string, connectedAt: Date): Promise<Session> => {
const repo = ds.getRepository(Session);
return repo.save(
repo.create({
id,
name: `session-${id.slice(0, 8)}`,
status: SessionStatus.READY,
config: {},
connectedAt,
lastActiveAt: connectedAt,
}),
);
};
const rawSession = async (): Promise<Record<string, string | null>> => {
const rows: Array<Record<string, string | null>> = await ds.query(
'SELECT "connectedAt"::text AS connected, "createdAt"::text AS created FROM sessions',
);
return rows[0];
};
it('runs off UTC, on a session pinned to UTC, over a schema with no timestamp[] column', async () => {
// Without a non-UTC process every assertion in this suite would pass for the wrong reason.
expect(new Date().getTimezoneOffset()).not.toBe(0);
const session: Array<{ zone: string; offset_seconds: number }> = await ds.query(
`SELECT current_setting('TimeZone') AS zone, EXTRACT(TIMEZONE FROM now())::int AS offset_seconds`,
);
expect(session[0].offset_seconds).toBe(0);
// The parser override answers OID 1114 only. It would be unsafe if the schema held a `timestamp[]`
// column, whose OID (1115) keeps pg-types' array parser.
const arrays: unknown[] = await ds.query(
`SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public' AND udt_name IN ('_timestamp', '_timestamptz')`,
);
expect(arrays).toEqual([]);
});
it('accepts the pinned session and rejects a zone that only reads +00 half the year', async () => {
await expect(assertDataConnectionUtc(ds)).resolves.toBeUndefined();
// Europe/London is what a UK server default looks like behind a pooler that discards the
// per-connection SET: +00 from late October to late March, +01 the rest of the year. Sampling the
// offset once, at whatever instant boot happens to be, passes it for half the year and lets every
// DEFAULT now() run an hour ahead of what the driver reads back for the other half.
const london = new DataSource({
type: 'postgres',
...connectionOptions,
extra: { ...postgresUtcExtra(), onConnect: (c: ClientBase) => c.query("SET TIME ZONE 'Europe/London'"), max: 1 },
});
await london.initialize();
try {
const sampled: Array<{ jan: number; jul: number }> = await london.query(
`SELECT EXTRACT(TIMEZONE FROM TIMESTAMPTZ '2026-01-15 12:00:00+00')::int AS jan,
EXTRACT(TIMEZONE FROM TIMESTAMPTZ '2026-07-15 12:00:00+00')::int AS jul`,
);
expect([sampled[0].jan, sampled[0].jul]).toEqual([0, 3600]);
await expect(assertDataConnectionUtc(london)).rejects.toThrow(/TimeZone is "Europe\/London"/);
} finally {
await london.destroy();
}
});
it('stores an app-bound create/update date as UTC wall clock, like any other column the app writes', async () => {
// These three read as `DEFAULT now()` columns from the schema, but nothing ever lets the default
// fire: their only writer passes the value. TypeORM's upsert puts an explicitly-valued update-date
// column in the ON CONFLICT overwrite list rather than emitting `= DEFAULT`, so the row carries the
// PROCESS's convention, not the server's. That is what decides whether an operator converting a
// pre-0.23.6 database has to touch them, so it is asserted here rather than read off TypeORM.
const at = new Date('2026-01-01T00:00:00.000Z');
await ds.query('DELETE FROM lid_mappings');
await ds.query('DELETE FROM chat_states');
await seedSession(SESSION_ID, at);
const lid = { lid: '55501', phone: '628111', sessionId: null, updatedAt: at };
const chat = { sessionId: SESSION_ID, chatId: '[email protected]', archived: false, pinned: false, updatedAt: at };
const stored = { sessionId: SESSION_ID, waMessageId: 'WA1', serializedMessage: '{}', createdAt: at };
await ds.getRepository(LidMapping).upsert(lid, ['lid']);
await ds.getRepository(ChatState).upsert(chat, ['sessionId', 'chatId']);
await ds.getRepository(BaileysStoredMessage).upsert(stored, ['sessionId', 'waMessageId']);
const text = async (table: string, column: string): Promise<string> => {
const rows: Array<{ t: string }> = await ds.query(`SELECT "${column}"::text AS t FROM ${table}`);
return rows[0].t;
};
// The host's wall clock would read 2026-01-01 07:00:00 here.
expect(await text('lid_mappings', 'updatedAt')).toBe('2026-01-01 00:00:00');
expect(await text('chat_states', 'updatedAt')).toBe('2026-01-01 00:00:00');
expect(await text('baileys_stored_messages', 'createdAt')).toBe('2026-01-01 00:00:00');
});
it('stores what the app wrote and the server defaulted in the same zone', async () => {
const connectedAt = new Date('2026-01-01T00:00:00.000Z');
await seedSession(SESSION_ID, connectedAt);
const raw = await rawSession();
// The app-written column is UTC wall clock, not the host's +07:00 wall clock.
expect(raw.connected).toBe('2026-01-01 00:00:00');
// And the server-written one agrees with the process's own idea of now, within the test's runtime.
const created = new Date(`${raw.created?.replace(' ', 'T')}Z`).getTime();
expect(Math.abs(created - Date.now())).toBeLessThan(60_000);
const read = await ds.getRepository(Session).findOneByOrFail({ id: SESSION_ID });
expect(read.connectedAt?.toISOString()).toBe('2026-01-01T00:00:00.000Z');
});
it('carries every timestamp through export and two restores unchanged', async () => {
const connectedAt = new Date('2026-01-01T00:00:00.000Z');
await seedSession(SESSION_ID, connectedAt);
// Instants, not raw text: an archive carries ISO milliseconds, so the microseconds `now()` writes
// are rounded off by the first restore. That rounding is bounded at one millisecond and never
// compounds; a zone shift is what this asserts against.
const stamps = async (): Promise<Record<string, number | undefined>> => {
const row = await ds.getRepository(Session).findOneByOrFail({ id: SESSION_ID });
return { connected: row.connectedAt?.getTime(), created: row.createdAt.getTime() };
};
const before = await stamps();
expect(before.connected).toBe(connectedAt.getTime());
expect(Math.abs((before.created ?? 0) - Date.now())).toBeLessThan(60_000);
// The archive travels as JSON, which is where a Date becomes ISO text.
const first = JSON.parse(JSON.stringify(await infra.exportData())) as { tables: MigrationTables };
expect(await infra.importData({ tables: first.tables })).toMatchObject({ imported: true, warnings: [] });
expect(await stamps()).toEqual(before);
// Restoring the restore is the shape that used to compound the shift once per round.
const second = JSON.parse(JSON.stringify(await infra.exportData())) as { tables: MigrationTables };
expect(await infra.importData({ tables: second.tables })).toMatchObject({ imported: true, warnings: [] });
expect(await stamps()).toEqual(before);
expect(first.tables.sessions[0].connectedAt).toBe('2026-01-01T00:00:00.000Z');
// And the column still reads as UTC wall clock rather than the host's.
expect((await rawSession()).connected).toBe('2026-01-01 00:00:00');
});
it('restores a SQLite-made archive onto postgres at the same instant', async () => {
// Written by a real SQLite data connection below, which is what a SQLite deployment's export holds:
// `datetime` text for the create/update dates, DateTransformer's ISO text for the rest.
const repo = sqlite.getRepository(SqliteSession);
await repo.save({
id: SESSION_ID,
name: 'session-s1',
status: 'ready',
phone: null,
pushName: null,
config: '{}',
proxyUrl: null,
proxyType: null,
connectedAt: new Date('2026-01-01T00:00:00.000Z').toISOString(),
lastActiveAt: null,
});
const archivedRows: Array<Record<string, unknown>> = await sqlite.query('SELECT * FROM sessions');
const [archived] = archivedRows;
expect(archived.createdAt).toMatch(/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}(\.\d{3})?$/);
expect(await infra.importData({ tables: { sessions: [archived] } as never })).toMatchObject({
imported: true,
warnings: [],
});
const restored = await ds.getRepository(Session).findOneByOrFail({ id: SESSION_ID });
expect(restored.connectedAt?.toISOString()).toBe('2026-01-01T00:00:00.000Z');
expect(restored.createdAt.getTime()).toBe(
new Date(`${(archived.createdAt as string).replace(' ', 'T')}Z`).getTime(),
);
});
it('restores a postgres-made archive onto SQLite at the same instant', async () => {
const connectedAt = new Date('2026-01-01T00:00:00.000Z');
await seedSession(SESSION_ID, connectedAt);
const created = (await ds.getRepository(Session).findOneByOrFail({ id: SESSION_ID })).createdAt;
const archive = JSON.parse(JSON.stringify(await infra.exportData())) as { tables: MigrationTables };
const row = archive.tables.sessions[0] as unknown as Record<string, unknown>;
// The two steps importData takes on a SQLite target, with its own helpers: normalise the archived
// `datetime` columns, then bind through the `$N` rewrite the SQLite path uses.
const datetimeColumns = sqliteDatetimeColumns(sqlite).get('sessions') ?? [];
expect(datetimeColumns).toContain('createdAt');
const normalised = Object.fromEntries(
Object.entries(row).map(([key, value]) => [key, datetimeColumns.includes(key) ? toSqliteDatetime(value) : value]),
);
await sqlite.query(
`INSERT INTO sessions (id, name, status, phone, "pushName", config, "proxyUrl", "proxyType", "connectedAt", "lastActiveAt", "createdAt", "updatedAt")
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
normalised.id,
normalised.name,
normalised.status,
normalised.phone ?? null,
normalised.pushName ?? null,
normalised.config,
normalised.proxyUrl ?? null,
normalised.proxyType ?? null,
normalised.connectedAt ?? null,
normalised.lastActiveAt ?? null,
normalised.createdAt,
normalised.updatedAt,
],
);
const storedRows: Array<Record<string, string>> = await sqlite.query(
'SELECT "connectedAt", "createdAt" FROM sessions',
);
const [stored] = storedRows;
expect(stored.connectedAt).toBe('2026-01-01T00:00:00.000Z');
expect(new Date(`${stored.createdAt.replace(' ', 'T')}Z`).getTime()).toBe(created.getTime());
});
it('deletes exactly the rows a retention window names', async () => {
const outbox = ds.getRepository(WebhookOutboxEvent);
const hoursFromCutoff = (hours: number): string =>
// Written the way `DEFAULT now()` writes it on a pinned server, so this measures the cutoff the
// service binds against a stored value the app did not bind.
utcWallText(new Date(Date.now() - 7 * 24 * 60 * 60 * 1000 + hours * 60 * 60 * 1000));
for (const [id, createdAt] of [
// Both sit within the host's +07:00 offset of the 7-day cutoff, so a cutoff bound in the wrong
// zone takes the younger one with it: silent data loss, not a late delete.
[OLD_ID, hoursFromCutoff(-3)],
[YOUNG_ID, hoursFromCutoff(3)],
]) {
await ds.query(
`INSERT INTO webhook_outbox_events (id, "webhookId", "sessionId", event, "idempotencyKey", "deliveryId", payload, state, attempts, "createdAt")
VALUES ($1, $2, $3, 'message.received', $4, $5, '{}', 'delivered', 1, $6)`,
[id, SESSION_ID, SESSION_ID, `key-${id}`, `del-${id}`, createdAt],
);
}
const pruned = await new WebhookOutboxService(outbox).pruneSettled(7);
expect(pruned).toBe(1);
expect((await outbox.find()).map(saved => saved.id)).toEqual([YOUNG_ID]);
});
it('counts a day of messages from the local midnight the host means', async () => {
await seedSession(SESSION_ID, new Date('2026-01-01T00:00:00.000Z'));
const messages = ds.getRepository(Message);
const localMidnight = new Date();
localMidnight.setHours(0, 0, 0, 0);
// Stored as the server writes `createdAt` (DEFAULT now() on a pinned session), one minute either
// side of the host's local midnight.
for (const [id, offsetMs] of [
[BEFORE_ID, -60_000],
[AFTER_ID, 60_000],
] as Array<[string, number]>) {
await ds.query(
`INSERT INTO messages (id, "sessionId", "chatId", "from", "to", body, type, direction, status, "createdAt")
VALUES ($1, $2, '[email protected]', '[email protected]', '[email protected]', 'hi', 'text', $3, $4, $5)`,
[
id,
SESSION_ID,
MessageDirection.OUTGOING,
MessageStatus.SENT,
utcWallText(new Date(localMidnight.getTime() + offsetMs)),
],
);
}
const stats = new StatsService(ds.getRepository(Session), messages, cfg as never);
expect((await stats.getOverview()).messages.today).toEqual({ sent: 1, received: 0 });
});
it('keeps a lease comparison honest across the restore that carries it', async () => {
const repo = ds.getRepository(Session);
await seedSession(SESSION_ID, new Date('2026-01-01T00:00:00.000Z'));
const leaseExpiresAt = new Date(Date.now() + 60_000);
await repo.update({ id: SESSION_ID }, { nodeId: 'node-a', claimedAt: new Date(), leaseExpiresAt });
const archive = JSON.parse(JSON.stringify(await infra.exportData())) as { tables: MigrationTables };
expect(await infra.importData({ tables: archive.tables })).toMatchObject({ imported: true, warnings: [] });
// The claim is live, so it must not read as lapsed after the restore that carried it.
expect(await repo.findOneBy({ id: SESSION_ID, leaseExpiresAt: LessThan(new Date()) })).toBeNull();
const carried = await repo.findOneByOrFail({ id: SESSION_ID });
expect(carried.leaseExpiresAt!.getTime()).toBeGreaterThanOrEqual(leaseExpiresAt.getTime());
});
it('re-keys a name-keyed chat state on restore over a uuid sessions.id', async () => {
// synchronize gives sessions.id a native uuid type, which has no `=` against the varchar key.
await ds.query('DELETE FROM chat_states');
const session = await seedSession(SESSION_ID, new Date('2026-01-01T00:00:00.000Z'));
const chats = ds.getRepository(ChatState);
await chats.save([
{ sessionId: session.name, chatId: '[email protected]', archived: true, pinned: false },
{ sessionId: SESSION_ID, chatId: '[email protected]', archived: false, pinned: true },
]);
const archive = JSON.parse(JSON.stringify(await infra.exportData())) as { tables: MigrationTables };
expect(await infra.importData({ tables: archive.tables })).toMatchObject({ imported: true, warnings: [] });
const rows = await chats.find({ order: { chatId: 'ASC' } });
expect(rows.map(r => [r.chatId, r.sessionId, r.archived, r.pinned])).toEqual([
['[email protected]', SESSION_ID, false, true],
['[email protected]', SESSION_ID, true, false],
]);
});
it('rolls back a rejected row with its real error instead of an aborted-transaction failure', async () => {
await seedSession(SESSION_ID, new Date('2026-01-01T00:00:00.000Z'));
const archive = JSON.parse(JSON.stringify(await infra.exportData())) as { tables: MigrationTables };
const [row] = archive.tables.sessions;
const res = await infra.importData({
tables: {
sessions: [
{ ...row, id: OLD_ID },
{ ...row, id: OLD_ID, name: 'dup' },
],
},
});
expect(res.imported).toBe(false);
expect(res.warnings).toHaveLength(1);
expect(res.warnings[0]).toContain('duplicate key');
expect((await ds.getRepository(Session).find()).map(s => s.id)).toEqual([SESSION_ID]);
});
});