Free, Open Source, Self-Hosted WhatsApp API Gateway
TypeScript apibotgatewayself-hostedwhatsappwhatsapp-apiwhatsapp-automationwhatsapp-botwhatsapp-gatewaywhatsapp-gateway-apiwhatsapp-webwhatsapp-web-api
import { Injectable, NotFoundException } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { ConfigService } from '@nestjs/config';
import { Repository } from 'typeorm';
import { Session, SessionStatus } from '../session/entities/session.entity';
import { Message, MessageDirection, MessageStatus } from '../message/entities/message.entity';
/**
* SQL for the time-series timestamp bucket, per DB dialect. SQLite has strftime(); Postgres has
* neither strftime nor a case-insensitive bare `m.createdAt` (unquoted it folds to lowercase and
* misses the quoted "createdAt" column) — so it needs to_char() with a quoted column. The hour
* format yields an identical zero-padded, chronologically-sortable label on both engines, so the
* GROUP BY/ORDER BY on the alias and the downstream map() are unchanged.
*/
export function timeSeriesTimestampSql(dbType: string, interval: 'hour' | 'day'): string {
if (dbType === 'postgres') {
const fmt = interval === 'hour' ? 'YYYY-MM-DD HH24:00:00' : 'YYYY-MM-DD';
return `to_char(m."createdAt", '${fmt}')`;
}
const fmt = interval === 'hour' ? '%Y-%m-%d %H:00:00' : '%Y-%m-%d';
return `strftime('${fmt}', m.createdAt)`;
}
/** SQL for the integer hour-of-day (0-23) bucket, per DB dialect. */
export function hourBucketSql(dbType: string): string {
return dbType === 'postgres'
? `CAST(EXTRACT(HOUR FROM m."createdAt") AS INTEGER)`
: `CAST(strftime('%H', m.createdAt) AS INTEGER)`;
}
/**
* SQL for the most-recent-activity timestamp (MAX of createdAt) as an identical text format on both
* engines. SQLite's MAX over a `datetime` column returns the stored text; Postgres returns a timestamp
* the driver hydrates to a JS Date (serialized to a different ISO string). to_char/strftime pin both
* to `YYYY-MM-DD HH:MM:SS`, matching the format the time-series buckets already use, so the lastActive
* field is stable regardless of the backing database.
*/
export function maxCreatedAtSql(dbType: string): string {
return dbType === 'postgres'
? `to_char(MAX(m."createdAt"), 'YYYY-MM-DD HH24:MI:SS')`
: `strftime('%Y-%m-%d %H:%M:%S', MAX(m.createdAt))`;
}
/**
* SQL for a top chat's label. `chatName` holds the SENDER's push name, not the chat's: MAX over a group's
* rows named the group after whichever member sorts last. Only a 1:1 chat's inbound rows carry the name
* of the chat itself, so groups get null and the dashboard falls back to the chat id.
*
* The chat id is tested first on purpose: it is in the chatId indexes, so SQLite answers a group's row
* without seeking the table row that `direction` and `chatName` live in, which on a table of large rows
* is most of the cost (1.7 s down to 0.6 s over 59k messages, most of them in groups).
*/
const chatLabelSql = (alias: string): string =>
`MAX(CASE WHEN ${alias}.chatId NOT LIKE '%@g.us' AND ${alias}.direction = '${MessageDirection.INCOMING}' THEN ${alias}.chatName END)`;
export interface OverviewStats {
sessions: {
active: number;
total: number;
byStatus: Record<string, number>;
};
messages: {
sent: number;
received: number;
failed: number;
today: { sent: number; received: number };
};
}
export interface TimeSeriesPoint {
timestamp: string;
sent: number;
received: number;
}
export interface MessageStats {
timeSeries: TimeSeriesPoint[];
byType: Record<string, number>;
bySession: Array<{ sessionId: string; name: string; sent: number; received: number }>;
topChats: Array<{ chatId: string; chatName: string | null; messageCount: number }>;
}
export interface SessionStats {
session: { id: string; name: string; status: string };
messages: { sent: number; received: number; today: number; failed: number };
topChats: Array<{ chatId: string; chatName: string | null; count: number; lastActive: string }>;
hourlyActivity: Array<{ hour: number; sent: number; received: number }>;
}
@Injectable()
export class StatsService {
/**
* In-process TTL memo for the aggregate responses, keyed by query shape ('overview',
* 'messages:<period>', 'session:<id>'). The aggregates run GROUP BY scans over the whole
* messages table — on the default SQLite backend synchronously on the event loop — so
* dashboard polling would otherwise re-run them on every request. TTL-only invalidation:
* entries expire after stats.cacheTtlMs; there is no write-path hook. Session-scoped entries
* are additionally re-validated on serve (getSessionStats), so a deleted session is not
* resurrected from the memo. Every finished computation also drops the entries that have expired, so
* the entries of deleted sessions nobody asks for again do not accumulate. Concurrent misses
* on one key share a single computation (`inflight`), so an expired entry costs one scan, not one
* per waiting request; a rejection is handed to every waiter and never stored.
*/
private readonly memo = new Map<string, { expiresAt: number; value: unknown }>();
private readonly inflight = new Map<string, Promise<unknown>>();
constructor(
@InjectRepository(Session, 'data')
private readonly sessionRepo: Repository<Session>,
@InjectRepository(Message, 'data')
private readonly messageRepo: Repository<Message>,
private readonly configService: ConfigService,
) {}
/** The data-connection dialect ('sqlite' | 'postgres'), used to pick portable date SQL. */
private get dataDbType(): string {
return this.messageRepo.manager.dataSource.options.type;
}
/** Memo TTL in ms; 0 disables memoization (every request hits the DB). Read per call. */
private get memoTtlMs(): number {
return this.configService.get<number>('stats.cacheTtlMs', 30000);
}
/**
* Returns the memoized value for `key` while fresh; otherwise joins the computation already
* running for it, or starts one. The TTL runs from completion, so a scan slower than the TTL does
* not store an entry that is already stale. The identity check keeps a flight that was dropped
* (getSessionStats on a deleted session) from writing its result back.
*/
private memoized<T>(key: string, compute: () => Promise<T>): Promise<T> {
const ttl = this.memoTtlMs;
if (ttl <= 0) return compute();
const hit = this.memo.get(key);
if (hit && hit.expiresAt > Date.now()) return Promise.resolve(hit.value as T);
const pending = this.inflight.get(key);
if (pending) return pending as Promise<T>;
const flight: Promise<T> = compute().then(
value => {
if (this.inflight.get(key) === flight) {
this.inflight.delete(key);
const now = Date.now();
for (const [memoKey, entry] of this.memo) if (entry.expiresAt <= now) this.memo.delete(memoKey);
this.memo.set(key, { expiresAt: now + ttl, value });
}
return value;
},
(err: unknown) => {
if (this.inflight.get(key) === flight) this.inflight.delete(key);
throw err;
},
);
this.inflight.set(key, flight);
return flight;
}
async getOverview(): Promise<OverviewStats> {
return this.memoized('overview', () => this.loadOverview());
}
private async loadOverview(): Promise<OverviewStats> {
// Get session stats
const sessions = await this.sessionRepo.find();
const byStatus: Record<string, number> = {};
let active = 0;
for (const session of sessions) {
byStatus[session.status] = (byStatus[session.status] || 0) + 1;
if (session.status === SessionStatus.READY) active++;
}
// Get message stats
const todayStart = new Date();
todayStart.setHours(0, 0, 0, 0);
const messageStats = await this.messageRepo
.createQueryBuilder('m')
.select('m.direction', 'direction')
.addSelect('COUNT(*)', 'count')
.groupBy('m.direction')
.getRawMany<{ direction: string; count: string }>();
const todayStats = await this.messageRepo
.createQueryBuilder('m')
.select('m.direction', 'direction')
.addSelect('COUNT(*)', 'count')
.where('m.createdAt >= :todayStart', { todayStart })
.groupBy('m.direction')
.getRawMany<{ direction: string; count: string }>();
const sent = parseInt(messageStats.find(m => m.direction === 'outgoing')?.count || '0');
const received = parseInt(messageStats.find(m => m.direction === 'incoming')?.count || '0');
const todaySent = parseInt(todayStats.find(m => m.direction === 'outgoing')?.count || '0');
const todayReceived = parseInt(todayStats.find(m => m.direction === 'incoming')?.count || '0');
// Count failed messages
const failed = await this.messageRepo.count({
where: { status: MessageStatus.FAILED },
});
return {
sessions: {
active,
total: sessions.length,
byStatus,
},
messages: {
sent,
received,
failed,
today: { sent: todaySent, received: todayReceived },
},
};
}
async getMessageStats(period: '24h' | '7d' | '30d'): Promise<MessageStats> {
return this.memoized(`messages:${period}`, () => this.loadMessageStats(period));
}
private async loadMessageStats(period: '24h' | '7d' | '30d'): Promise<MessageStats> {
const since = this.getPeriodStart(period);
const interval = period === '24h' ? 'hour' : 'day';
// Time series - using raw query for SQLite compatibility
const timeSeries = await this.getTimeSeries(since, interval);
// By type. Rows with no body AND no metadata are content-less system/event rows (e.g. @lid
// privacy-user events the engine maps to `unknown`) — counting them would put a misleading
// "unknown" slice in the by-type chart, so they're excluded from the aggregation.
const byTypeRaw = await this.messageRepo
.createQueryBuilder('m')
.select('m.type', 'type')
.addSelect('COUNT(*)', 'count')
.where('m.createdAt >= :since', { since })
// Parenthesized: TypeORM does not wrap an andWhere, so a bare OR would escape the period bound.
.andWhere("((m.body IS NOT NULL AND m.body != '') OR m.metadata IS NOT NULL)")
.groupBy('m.type')
.getRawMany<{ type: string; count: string }>();
const byType: Record<string, number> = {};
for (const row of byTypeRaw) {
byType[row.type || 'unknown'] = parseInt(row.count);
}
// By session
const bySessionRaw = await this.messageRepo
.createQueryBuilder('m')
.select('m.sessionId', 'sessionId')
.addSelect('m.direction', 'direction')
.addSelect('COUNT(*)', 'count')
.where('m.createdAt >= :since', { since })
.groupBy('m.sessionId')
.addGroupBy('m.direction')
.getRawMany<{ sessionId: string; direction: string; count: string }>();
const sessionMap = new Map<string, { sent: number; received: number }>();
for (const row of bySessionRaw) {
if (!sessionMap.has(row.sessionId)) {
sessionMap.set(row.sessionId, { sent: 0, received: 0 });
}
const entry = sessionMap.get(row.sessionId)!;
if (row.direction === 'outgoing') entry.sent = parseInt(row.count);
else entry.received = parseInt(row.count);
}
const sessions = await this.sessionRepo.find();
const sessionNames = new Map(sessions.map(s => [s.id, s.name]));
const bySession = Array.from(sessionMap.entries()).map(([sessionId, stats]) => ({
sessionId,
name: sessionNames.get(sessionId) || 'Unknown',
...stats,
}));
const topChats = await this.getTopChats(since);
return {
timeSeries,
byType,
bySession,
topChats: topChats.map(c => ({
chatId: c.chatId,
chatName: c.chatName ?? null,
messageCount: parseInt(c.messageCount),
})),
};
}
/** The ten chats with the most messages since `since`, each 1:1 chat with its name. */
private async getTopChats(
since: Date,
): Promise<Array<{ chatId: string; messageCount: string; chatName: string | null }>> {
if (this.dataDbType === 'postgres') {
return (
this.messageRepo
.createQueryBuilder('m')
.select('m.chatId', 'chatId')
.addSelect('COUNT(*)', 'messageCount')
.addSelect(chatLabelSql('m'), 'chatName')
.where('m.createdAt >= :since', { since })
.groupBy('m.chatId')
// Order by the aggregate expression, not the "messageCount" alias: Postgres folds an unquoted
// ORDER BY messageCount to lowercase and 42703s against the quoted alias.
.orderBy('COUNT(*)', 'DESC')
.limit(10)
.getRawMany<{ chatId: string; messageCount: string; chatName: string | null }>()
);
}
// SQLite keeps no statistics, so for GROUP BY m.chatId it reads the whole table in chatId index order
// rather than the createdAt range, however narrow: 3.2 s over 59k messages for a window of 306, and
// better-sqlite3 runs it on the event loop, so every request waits. Materializing the window first
// makes the createdAt index drive the read (21 ms for the same window). The window is still built by
// the query builder, which formats the date parameter the way the column is stored; the TypeORM SQLite
// driver cannot emit the MATERIALIZED keyword itself.
const [window, params] = this.messageRepo
.createQueryBuilder('m')
.select('m.chatId', 'chatId')
.addSelect('m.chatName', 'chatName')
.addSelect('m.direction', 'direction')
.where('m.createdAt >= :since', { since })
.getQueryAndParameters();
return this.messageRepo.manager.query(
`WITH w AS MATERIALIZED (${window}) SELECT w.chatId AS "chatId", COUNT(*) AS "messageCount", ` +
`${chatLabelSql('w')} AS "chatName" FROM w GROUP BY w.chatId ORDER BY COUNT(*) DESC LIMIT 10`,
params,
);
}
async getSessionStats(sessionId: string): Promise<SessionStats> {
// The memo has no write-path hook, so a `session:<id>` entry can outlive its session row and
// would keep serving a deleted session's stats until the TTL expires. Re-check existence on
// every call — a cheap primary-key lookup next to the aggregate scans the memo exists to
// avoid — and drop the stale entry instead of serving it.
const key = `session:${sessionId}`;
if ((await this.sessionRepo.count({ where: { id: sessionId } })) === 0) {
this.memo.delete(key);
this.inflight.delete(key);
throw new NotFoundException('Session not found');
}
return this.memoized(key, () => this.loadSessionStats(sessionId));
}
private async loadSessionStats(sessionId: string): Promise<SessionStats> {
const session = await this.sessionRepo.findOne({ where: { id: sessionId } });
if (!session) {
throw new NotFoundException('Session not found');
}
const todayStart = new Date();
todayStart.setHours(0, 0, 0, 0);
// Message counts
const stats = await this.messageRepo
.createQueryBuilder('m')
.select('m.direction', 'direction')
.addSelect('COUNT(*)', 'count')
.where('m.sessionId = :sessionId', { sessionId })
.groupBy('m.direction')
.getRawMany<{ direction: string; count: string }>();
const todayCount = await this.messageRepo
.createQueryBuilder('m')
.where('m.sessionId = :sessionId', { sessionId })
.andWhere('m.createdAt >= :todayStart', { todayStart })
.getCount();
const sent = parseInt(stats.find(s => s.direction === 'outgoing')?.count || '0');
const received = parseInt(stats.find(s => s.direction === 'incoming')?.count || '0');
// Count failed messages for this session
const failed = await this.messageRepo.count({
where: { sessionId, status: MessageStatus.FAILED },
});
// Top chats for this session
const topChats = await this.messageRepo
.createQueryBuilder('m')
.select('m.chatId', 'chatId')
.addSelect('COUNT(*)', 'count')
.addSelect(maxCreatedAtSql(this.dataDbType), 'lastActive')
.addSelect(chatLabelSql('m'), 'chatName')
.where('m.sessionId = :sessionId', { sessionId })
.groupBy('m.chatId')
.orderBy('count', 'DESC')
.limit(10)
.getRawMany<{ chatId: string; count: string; lastActive: string; chatName: string | null }>();
// Hourly activity (last 24h)
const hourlyActivity = await this.getHourlyActivity(sessionId);
return {
session: { id: session.id, name: session.name, status: session.status },
messages: { sent, received, today: todayCount, failed },
topChats: topChats.map(c => ({
chatId: c.chatId,
chatName: c.chatName ?? null,
count: parseInt(c.count),
lastActive: c.lastActive,
})),
hourlyActivity,
};
}
private getPeriodStart(period: '24h' | '7d' | '30d'): Date {
const now = new Date();
switch (period) {
case '24h':
return new Date(now.getTime() - 24 * 60 * 60 * 1000);
case '7d':
return new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);
case '30d':
return new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000);
}
}
private async getTimeSeries(since: Date, interval: 'hour' | 'day'): Promise<TimeSeriesPoint[]> {
// Alias the bucket as `bucket`, not `timestamp`: `timestamp` is a reserved type keyword in
// PostgreSQL, so `GROUP BY timestamp` is not read as the output alias and the query 500s
// ("column m.createdAt must appear in the GROUP BY"). SQLite tolerates it, hence the dialect-only
// bug. The API field stays `timestamp` (mapped below).
const raw = await this.messageRepo
.createQueryBuilder('m')
.select(timeSeriesTimestampSql(this.dataDbType, interval), 'bucket')
.addSelect(`SUM(CASE WHEN m.direction = 'outgoing' THEN 1 ELSE 0 END)`, 'sent')
.addSelect(`SUM(CASE WHEN m.direction = 'incoming' THEN 1 ELSE 0 END)`, 'received')
.where('m.createdAt >= :since', { since })
.groupBy('bucket')
.orderBy('bucket', 'ASC')
.getRawMany<{ bucket: string; sent: string; received: string }>();
return raw.map(r => ({
timestamp: r.bucket,
sent: parseInt(r.sent || '0'),
received: parseInt(r.received || '0'),
}));
}
private async getHourlyActivity(sessionId: string): Promise<Array<{ hour: number; sent: number; received: number }>> {
const since = new Date(Date.now() - 24 * 60 * 60 * 1000);
const raw = await this.messageRepo
.createQueryBuilder('m')
.select(hourBucketSql(this.dataDbType), 'hour')
.addSelect(`SUM(CASE WHEN m.direction = 'outgoing' THEN 1 ELSE 0 END)`, 'sent')
.addSelect(`SUM(CASE WHEN m.direction = 'incoming' THEN 1 ELSE 0 END)`, 'received')
.where('m.sessionId = :sessionId', { sessionId })
.andWhere('m.createdAt >= :since', { since })
.groupBy('hour')
.orderBy('hour', 'ASC')
.getRawMany<{ hour: string; sent: string; received: string }>();
// Fill in missing hours
const result: Array<{ hour: number; sent: number; received: number }> = [];
const hourMap = new Map(raw.map(r => [parseInt(r.hour), r]));
for (let h = 0; h < 24; h++) {
const data = hourMap.get(h);
result.push({
hour: h,
sent: data ? parseInt(data.sent || '0') : 0,
received: data ? parseInt(data.received || '0') : 0,
});
}
return result;
}
}