Skip to content

Free, Open Source, Self-Hosted WhatsApp API Gateway

27.4 KB
Raw Blame
import { NotFoundException } from '@nestjs/common';
import { DataSource } from 'typeorm';
import { StatsService, timeSeriesTimestampSql, hourBucketSql, maxCreatedAtSql } from './stats.service';
import { Session, SessionStatus } from '../session/entities/session.entity';
import { Message, MessageDirection, MessageStatus } from '../message/entities/message.entity';

// these two analytics queries used SQLite-only strftime(), which 500s on a Postgres
// data DB. The dialect-correct SQL is generated by these pure helpers; the Postgres branch is
// unit-tested here (a live Postgres isn't available in CI), and the SQLite path is exercised
// end-to-end below so the fix can't regress the working backend.
describe('stats SQL dialect helpers', () => {
  it('uses strftime on sqlite (hour + day buckets)', () => {
    expect(timeSeriesTimestampSql('sqlite', 'hour')).toBe(`strftime('%Y-%m-%d %H:00:00', m.createdAt)`);
    expect(timeSeriesTimestampSql('sqlite', 'day')).toBe(`strftime('%Y-%m-%d', m.createdAt)`);
    expect(hourBucketSql('sqlite')).toBe(`CAST(strftime('%H', m.createdAt) AS INTEGER)`);
  });

  it('uses to_char/extract with a quoted createdAt on postgres', () => {
    expect(timeSeriesTimestampSql('postgres', 'hour')).toBe(`to_char(m."createdAt", 'YYYY-MM-DD HH24:00:00')`);
    expect(timeSeriesTimestampSql('postgres', 'day')).toBe(`to_char(m."createdAt", 'YYYY-MM-DD')`);
    expect(hourBucketSql('postgres')).toBe(`CAST(EXTRACT(HOUR FROM m."createdAt") AS INTEGER)`);
  });

  it('formats MAX(createdAt) to an identical text timestamp on both engines (lastActive parity)', () => {
    // Postgres returns a timestamp the driver hydrates to a JS Date (serialized to a different ISO
    // string than SQLite's stored text); pinning both to the same to_char/strftime format keeps the
    // lastActive field stable regardless of the backing database.
    expect(maxCreatedAtSql('sqlite')).toBe(`strftime('%Y-%m-%d %H:%M:%S', MAX(m.createdAt))`);
    expect(maxCreatedAtSql('postgres')).toBe(`to_char(MAX(m."createdAt"), 'YYYY-MM-DD HH24:MI:SS')`);
  });
});

describe('StatsService time-series + hourly activity on SQLite (end-to-end regression)', () => {
  let ds: DataSource;
  let service: StatsService;

  beforeEach(async () => {
    ds = new DataSource({
      type: 'better-sqlite3',
      database: ':memory:',
      entities: [Session, Message],
      synchronize: true,
    });
    await ds.initialize();
    const config = { get: () => 30000 };
    service = new StatsService(ds.getRepository(Session), ds.getRepository(Message), config as never);
  });

  afterEach(async () => {
    await ds.destroy();
  });

  const seedMessage = (over: Partial<Message>) =>
    ds.getRepository(Message).save(
      ds.getRepository(Message).create({
        sessionId: 's1',
        chatId: 'c1',
        from: 'a',
        to: 'b',
        type: 'text',
        direction: MessageDirection.OUTGOING,
        status: MessageStatus.SENT,
        ...over,
      }),
    );

  it('getMessageStats returns a populated time series without throwing', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ direction: MessageDirection.OUTGOING });
    await seedMessage({ direction: MessageDirection.INCOMING });

    const stats = await service.getMessageStats('24h');
    expect(Array.isArray(stats.timeSeries)).toBe(true);
    expect(stats.timeSeries.length).toBeGreaterThanOrEqual(1);
    const totals = stats.timeSeries.reduce(
      (acc, p) => ({ sent: acc.sent + p.sent, received: acc.received + p.received }),
      {
        sent: 0,
        received: 0,
      },
    );
    expect(totals.sent).toBe(1);
    expect(totals.received).toBe(1);
    // The hour bucket must be the zero-padded chronological label strftime produces.
    expect(stats.timeSeries[0].timestamp).toMatch(/^\d{4}-\d{2}-\d{2} \d{2}:00:00$/);
  });

  it('getMessageStats topChats surfaces chatName via the MAX aggregate (ignoring null rows)', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ chatId: '[email protected]', chatName: 'Alice', direction: MessageDirection.INCOMING });
    await seedMessage({ chatId: '[email protected]', chatName: undefined, direction: MessageDirection.INCOMING });

    const stats = await service.getMessageStats('24h');
    const chat = stats.topChats.find(c => c.chatId === '[email protected]');
    // MAX(m.chatName) across the group returns the non-null name, so a legacy null row can't blank it.
    expect(chat?.chatName).toBe('Alice');
  });

  // chatName holds the SENDER's push name, so a group's MAX over it named the group after whichever member
  // sorts last, and an outgoing row would carry the operator's own name. Only a 1:1 chat's inbound rows name it.
  it('topChats names a 1:1 chat from its inbound rows only and leaves a group unnamed, in both queries', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ chatId: '[email protected]', chatName: 'Andi', direction: MessageDirection.INCOMING });
    await seedMessage({ chatId: '[email protected]', chatName: 'Zul', direction: MessageDirection.INCOMING });
    await seedMessage({ chatId: '[email protected]', chatName: 'Bob', direction: MessageDirection.INCOMING });
    await seedMessage({ chatId: '[email protected]', chatName: 'Zz Operator', direction: MessageDirection.OUTGOING });

    const overall = await service.getMessageStats('24h');
    expect(overall.topChats.find(c => c.chatId === '[email protected]')?.chatName).toBeNull();
    expect(overall.topChats.find(c => c.chatId === '[email protected]')?.chatName).toBe('Bob');

    const perSession = await service.getSessionStats('s1');
    expect(perSession.topChats.find(c => c.chatId === '[email protected]')?.chatName).toBeNull();
    expect(perSession.topChats.find(c => c.chatId === '[email protected]')?.chatName).toBe('Bob');
  });

  it('getMessageStats byType excludes content-less system/event rows (no body AND no metadata)', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ body: 'hello' });
    // A media message with no caption (body '') but a metadata.media payload must still be counted —
    // only rows empty on BOTH are excluded.
    await seedMessage({ type: 'image', body: '', metadata: { media: { mimetype: 'image/png', data: 'x' } } });
    // The misleading slice: an @lid privacy-user event the engine maps to `unknown` — no content at all.
    await seedMessage({ type: 'unknown', body: '', direction: MessageDirection.INCOMING });

    const stats = await service.getMessageStats('24h');
    expect(stats.byType).toEqual({ text: 1, image: 1 });
  });

  it('getMessageStats byType counts only rows inside the period, metadata-carrying rows included', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    const oldImage = await seedMessage({ type: 'image', body: '', metadata: { media: { mimetype: 'image/png' } } });
    const oldReply = await seedMessage({ body: '', metadata: { quotedMessageId: 'q1' } });
    const old = new Date(Date.now() - 20 * 24 * 60 * 60 * 1000);
    await ds.getRepository(Message).update([oldImage.id, oldReply.id], { createdAt: old });
    await seedMessage({ type: 'image', body: '', metadata: { media: { mimetype: 'image/png' } } });

    expect((await service.getMessageStats('24h')).byType).toEqual({ image: 1 });
    expect((await service.getMessageStats('7d')).byType).toEqual({ image: 1 });
  });

  it('time-series query never groups by the bare reserved word `timestamp` (Postgres-safe)', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ direction: MessageDirection.OUTGOING });

    // SQLite tolerates `GROUP BY timestamp`, but `timestamp` is a reserved type keyword in Postgres
    // and crashes there ("column m.createdAt must appear in the GROUP BY"). Assert the generated SQL,
    // not the result, so the fix can't silently regress on the backend the test DB doesn't exercise.
    const captured: string[] = [];
    const repo = ds.getRepository(Message);
    const origCreate = repo.createQueryBuilder.bind(repo);
    jest.spyOn(repo, 'createQueryBuilder').mockImplementation((alias?: string) => {
      const qb = origCreate(alias);
      const origGetRawMany = qb.getRawMany.bind(qb);
      jest.spyOn(qb, 'getRawMany').mockImplementation((async () => {
        captured.push(qb.getQuery());
        return origGetRawMany();
      }) as never);
      return qb;
    });

    await service.getMessageStats('24h');

    expect(captured.some(sql => /GROUP BY/i.test(sql))).toBe(true); // sanity: a grouped query was built
    for (const sql of captured) {
      expect(sql).not.toMatch(/GROUP BY\s+timestamp\b/i);
    }
  });

  it('no analytics query groups/orders by a bare reserved word — on the Postgres dialect shape too', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ direction: MessageDirection.OUTGOING });

    // Force the Postgres SQL shape and capture the generated SQL WITHOUT executing (SQLite can't run
    // to_char/EXTRACT). groupBy/orderBy arguments are emitted verbatim, so this sweeps every grouped
    // analytics query for a reserved-word alias on the dialect the SQLite test DB can't exercise —
    // the exact #476 blindness class, extended beyond the single time-series query.
    // Shadow the (prototype) getter on this fresh per-test instance so it can't leak to other tests.
    Object.defineProperty(service, 'dataDbType', { get: () => 'postgres', configurable: true });

    const captured: string[] = [];
    const repo = ds.getRepository(Message);
    const origCreate = repo.createQueryBuilder.bind(repo);
    jest.spyOn(repo, 'createQueryBuilder').mockImplementation((alias?: string) => {
      const qb = origCreate(alias);
      jest.spyOn(qb, 'getRawMany').mockImplementation(() => {
        captured.push(qb.getQuery());
        return Promise.resolve([]);
      });
      return qb;
    });

    await service.getMessageStats('24h'); // time-series (bucket) + byType + bySession + topChats
    await service.getSessionStats('s1'); // hourly activity (hour)

    expect(captured.length).toBeGreaterThan(0);
    // A small set of PostgreSQL reserved type/keywords that a naive alias could collide with.
    const PG_RESERVED = ['timestamp', 'user', 'order', 'end', 'all', 'column', 'table'];
    for (const sql of captured) {
      for (const kw of PG_RESERVED) {
        expect(sql).not.toMatch(new RegExp(`GROUP BY\\s+["']?${kw}["']?\\s*(,|$|\\s)`, 'i'));
        expect(sql).not.toMatch(new RegExp(`ORDER BY\\s+["']?${kw}["']?\\s*(,|$|\\s|ASC|DESC)`, 'i'));
      }
    }
  });

  it('no analytics query orders/groups by an unquoted mixed-case alias (Postgres case-folds it → 42703)', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ direction: MessageDirection.OUTGOING });

    Object.defineProperty(service, 'dataDbType', { get: () => 'postgres', configurable: true });

    const captured: string[] = [];
    const repo = ds.getRepository(Message);
    const origCreate = repo.createQueryBuilder.bind(repo);
    jest.spyOn(repo, 'createQueryBuilder').mockImplementation((alias?: string) => {
      const qb = origCreate(alias);
      jest.spyOn(qb, 'getRawMany').mockImplementation(() => {
        captured.push(qb.getQuery());
        return Promise.resolve([]);
      });
      return qb;
    });

    await service.getMessageStats('24h'); // time-series + byType + bySession + topChats
    await service.getSessionStats('s1');

    expect(captured.length).toBeGreaterThan(0);
    // Postgres folds an UNQUOTED identifier to lowercase; a mixed-case alias defined with quotes
    // (COUNT(*) AS "messageCount") then no longer matches a bare `ORDER BY messageCount` → it looks for
    // "messagecount" and 42703s. SQLite is case-insensitive so it never surfaced. Flag any bare
    // (unquoted) ORDER BY / GROUP BY term that contains an uppercase letter.
    const offenders: string[] = [];
    for (const sql of captured) {
      for (const clause of sql.match(/\b(?:ORDER|GROUP) BY\s+[^\s,]+/gi) ?? []) {
        const term = clause.replace(/\b(?:ORDER|GROUP) BY\s+/i, '');
        // Only a BARE identifier (no quotes, no dots, no parens) folds — `COUNT(*)`, `to_char(...)`,
        // `"m"."chatId"` are all safe; `messageCount` is the landmine.
        if (/^[A-Za-z_][A-Za-z0-9_]*$/.test(term) && /[A-Z]/.test(term)) offenders.push(`${clause}  ::  ${sql}`);
      }
    }
    expect(offenders).toEqual([]);
  });

  it('getSessionStats returns 24 hourly buckets with the right counts', async () => {
    await ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));
    await seedMessage({ direction: MessageDirection.OUTGOING });
    await seedMessage({ direction: MessageDirection.OUTGOING });
    await seedMessage({ direction: MessageDirection.INCOMING });

    const stats = await service.getSessionStats('s1');
    expect(stats.hourlyActivity).toHaveLength(24);
    const totals = stats.hourlyActivity.reduce(
      (acc, h) => ({ sent: acc.sent + h.sent, received: acc.received + h.received }),
      { sent: 0, received: 0 },
    );
    expect(totals.sent).toBe(2);
    expect(totals.received).toBe(1);
  });

  const addSession = () =>
    ds
      .getRepository(Session)
      .save(ds.getRepository(Session).create({ id: 's1', name: 'n', status: SessionStatus.READY, config: {} }));

  /** Moves a seeded row's createdAt, which the column stamps with the insert time. */
  const backdate = (id: string, at: Date) => ds.getRepository(Message).update(id, { createdAt: at });

  // "Top chats" means the busiest chats: ASC would return the ten quietest and a larger cap a hundred. Chat k
  // holds k messages, so every count differs and the two quietest chats are the ones that drop out.
  it('topChats are the ten busiest chats, busiest first, in the overall and the per-session query', async () => {
    await addSession();
    for (let k = 1; k <= 12; k++) {
      for (let i = 0; i < k; i++) await seedMessage({ chatId: `chat${k}@c.us` });
    }
    const busiest = [12, 11, 10, 9, 8, 7, 6, 5, 4, 3];

    const overall = (await service.getMessageStats('24h')).topChats;
    expect(overall.map(c => c.messageCount)).toEqual(busiest);
    expect(overall[0].chatId).toBe('[email protected]');

    const perSession = (await service.getSessionStats('s1')).topChats;
    expect(perSession.map(c => c.count)).toEqual(busiest);
    expect(perSession[0].chatId).toBe('[email protected]');
  });

  // SQLite keeps no statistics, so GROUP BY m.chatId made it read the whole table in chatId index order for a
  // window of a few rows (3.2 s over 59k messages, on the event loop). The window must be read through the
  // createdAt index and the table never scanned.
  it('the overall top chats read the createdAt window through its index, never the whole table', async () => {
    await addSession();
    for (let i = 0; i < 40; i++) await seedMessage({ chatId: `chat${i % 8}@c.us` });
    const seen: Array<[string, unknown[]]> = [];
    const query = ds.manager.query.bind(ds.manager);
    jest.spyOn(ds.manager, 'query').mockImplementation(((sql: string, params?: unknown[]) => {
      seen.push([sql, params ?? []]);
      return query(sql, params);
    }) as never);

    await service.getMessageStats('24h');

    expect(seen).toHaveLength(1);
    const [sql, params] = seen[0];
    const plan = await ds.query<Array<{ detail: string }>>(`EXPLAIN QUERY PLAN ${sql}`, params);
    const details = plan.map(row => row.detail);
    expect(details.some(d => /USING (COVERING )?INDEX IDX_messages_createdAt/.test(d))).toBe(true);
    expect(details.filter(d => /^SCAN (messages|m)\b/.test(d))).toEqual([]);
  });

  // Today starts at local midnight, so it must differ from the all-time count. A row two days old is before
  // midnight in every time zone.
  it('counts only the rows since local midnight as today, overall and per session', async () => {
    await addSession();
    await seedMessage({ direction: MessageDirection.OUTGOING });
    await seedMessage({ direction: MessageDirection.INCOMING });
    const old = await seedMessage({ direction: MessageDirection.OUTGOING });
    await backdate(old.id, new Date(Date.now() - 48 * 60 * 60 * 1000));

    const overview = await service.getOverview();
    expect(overview.messages).toMatchObject({ sent: 2, received: 1, today: { sent: 1, received: 1 } });
    expect((await service.getSessionStats('s1')).messages).toMatchObject({ sent: 2, received: 1, today: 2 });
  });

  it('buckets the 24h series by hour, oldest first, split by direction', async () => {
    await addSession();
    const base = Date.now();
    const at = (hoursAgo: number) => new Date(base - hoursAgo * 60 * 60 * 1000);
    for (const [hoursAgo, direction] of [
      [2, MessageDirection.OUTGOING],
      [2, MessageDirection.OUTGOING],
      [2, MessageDirection.INCOMING],
      [5, MessageDirection.INCOMING],
    ] as const) {
      await backdate((await seedMessage({ direction })).id, at(hoursAgo));
    }
    const hour = (d: Date) => `${d.toISOString().slice(0, 13).replace('T', ' ')}:00:00`;

    expect((await service.getMessageStats('24h')).timeSeries).toEqual([
      { timestamp: hour(at(5)), sent: 0, received: 1 },
      { timestamp: hour(at(2)), sent: 2, received: 1 },
    ]);
  });

  it('buckets the 7d series by day, oldest first', async () => {
    await addSession();
    const base = Date.now();
    const at = (daysAgo: number) => new Date(base - daysAgo * 24 * 60 * 60 * 1000);
    await backdate((await seedMessage({ direction: MessageDirection.OUTGOING })).id, at(3));
    await backdate((await seedMessage({ direction: MessageDirection.INCOMING })).id, at(1));
    const day = (d: Date) => d.toISOString().slice(0, 10);

    expect((await service.getMessageStats('7d')).timeSeries).toEqual([
      { timestamp: day(at(3)), sent: 1, received: 0 },
      { timestamp: day(at(1)), sent: 0, received: 1 },
    ]);
  });
});

describe('StatsService aggregate memo (in-process TTL)', () => {
  let ds: DataSource;

  beforeEach(async () => {
    ds = new DataSource({
      type: 'better-sqlite3',
      database: ':memory:',
      entities: [Session, Message],
      synchronize: true,
    });
    await ds.initialize();
    const sessions = ds.getRepository(Session);
    await sessions.save(sessions.create({ id: 's1', name: 'n1', status: SessionStatus.READY, config: {} }));
    await sessions.save(sessions.create({ id: 's2', name: 'n2', status: SessionStatus.READY, config: {} }));
    const messages = ds.getRepository(Message);
    const base = {
      chatId: 'c1',
      from: 'a',
      to: 'b',
      type: 'text',
      direction: MessageDirection.OUTGOING,
      status: MessageStatus.SENT,
    };
    await messages.save(messages.create({ ...base, sessionId: 's1' }));
    await messages.save(messages.create({ ...base, sessionId: 's2' }));
  });

  afterEach(async () => {
    await ds.destroy();
  });

  const makeService = (ttlMs: number) =>
    new StatsService(ds.getRepository(Session), ds.getRepository(Message), { get: () => ttlMs } as never);

  it('serves a repeated identical call from the memo within the TTL (no second DB hit)', async () => {
    const service = makeService(30000);
    const spy = jest.spyOn(ds.getRepository(Message), 'createQueryBuilder');

    await service.getMessageStats('24h');
    const afterFirst = spy.mock.calls.length;
    expect(afterFirst).toBeGreaterThan(0);

    await service.getMessageStats('24h');
    expect(spy.mock.calls.length).toBe(afterFirst);
  });

  it('re-runs the aggregate once the TTL has expired', async () => {
    const service = makeService(30000);
    const spy = jest.spyOn(ds.getRepository(Message), 'createQueryBuilder');
    const nowSpy = jest.spyOn(Date, 'now').mockReturnValue(1_000_000);
    try {
      await service.getMessageStats('24h');
      const afterFirst = spy.mock.calls.length;

      nowSpy.mockReturnValue(1_000_000 + 30_001);
      await service.getMessageStats('24h');
      expect(spy.mock.calls.length).toBeGreaterThan(afterFirst);
    } finally {
      nowSpy.mockRestore();
    }
  });

  it('keys the memo by query shape and by session id', async () => {
    const service = makeService(30000);
    const spy = jest.spyOn(ds.getRepository(Message), 'createQueryBuilder');

    await service.getMessageStats('24h');
    let n = spy.mock.calls.length;
    await service.getMessageStats('7d'); // different period → different key → DB hit
    expect(spy.mock.calls.length).toBeGreaterThan(n);

    n = spy.mock.calls.length;
    await service.getSessionStats('s1');
    await service.getSessionStats('s1'); // memo hit — no new queries
    const afterS1 = spy.mock.calls.length;
    expect(afterS1).toBeGreaterThan(n);

    await service.getSessionStats('s2'); // different session → different key → DB hit
    expect(spy.mock.calls.length).toBeGreaterThan(afterS1);
  });

  // The private loaders, reached through a cast so the spies count computations rather than queries.
  type Loaders = { loadOverview: () => Promise<unknown>; loadSessionStats: (id: string) => Promise<unknown> };

  it('runs one aggregate for concurrent callers on a cold memo', async () => {
    const service = makeService(30000);
    const loadOverview = jest.spyOn(service as unknown as Loaders, 'loadOverview');
    const loadSession = jest.spyOn(service as unknown as Loaders, 'loadSessionStats');

    const [a, b] = await Promise.all([service.getOverview(), service.getOverview()]);
    await Promise.all([service.getSessionStats('s1'), service.getSessionStats('s1')]);

    expect(loadOverview).toHaveBeenCalledTimes(1);
    expect(b).toEqual(a);
    expect(loadSession).toHaveBeenCalledTimes(1);
  });

  it('shares a rejection with the concurrent callers and does not memoize it', async () => {
    const service = makeService(30000);
    const loadOverview = jest
      .spyOn(service as unknown as Loaders, 'loadOverview')
      .mockRejectedValueOnce(new Error('boom'));

    const results = await Promise.allSettled([service.getOverview(), service.getOverview()]);
    expect(results.map(r => r.status)).toEqual(['rejected', 'rejected']);
    expect(loadOverview).toHaveBeenCalledTimes(1);

    await expect(service.getOverview()).resolves.toBeDefined();
    expect(loadOverview).toHaveBeenCalledTimes(2);
  });

  it('starts the TTL when the aggregate completes, not when it starts', async () => {
    const service = makeService(30000);
    const loadOverview = jest.spyOn(service as unknown as Loaders, 'loadOverview');
    const nowSpy = jest.spyOn(Date, 'now').mockReturnValue(1_000_000);
    try {
      // A scan that outlives the TTL: stamping the entry at the start would store it already expired.
      loadOverview.mockImplementationOnce(() => {
        nowSpy.mockReturnValue(1_000_000 + 40_000);
        return Promise.resolve({} as never);
      });
      await service.getOverview();
      await service.getOverview();
      expect(loadOverview).toHaveBeenCalledTimes(1);
    } finally {
      nowSpy.mockRestore();
    }
  });

  it('a 0 TTL disables the memo (every call hits the DB)', async () => {
    const service = makeService(0);
    const spy = jest.spyOn(ds.getRepository(Message), 'createQueryBuilder');

    await service.getMessageStats('24h');
    const n = spy.mock.calls.length;
    await service.getMessageStats('24h');
    expect(spy.mock.calls.length).toBeGreaterThan(n);
  });

  it('does not serve a deleted session from the memo — the stale entry is evicted, not served', async () => {
    const service = makeService(30000);

    const first = await service.getSessionStats('s1'); // populates the 'session:s1' memo entry
    expect(first.session.name).toBe('n1');

    await ds.getRepository(Session).delete('s1');
    // Within the TTL the memo still holds the deleted session's snapshot; serving it would
    // resurrect a deleted session with a 200 instead of the honest 404.
    await expect(service.getSessionStats('s1')).rejects.toThrow(NotFoundException);

    // The stale entry is evicted, not just bypassed: a re-created session recomputes immediately
    // instead of waiting out the TTL with the pre-delete snapshot.
    await ds
      .getRepository(Session)
      .save(
        ds.getRepository(Session).create({ id: 's1', name: 'n1-recreated', status: SessionStatus.READY, config: {} }),
      );
    const recomputed = await service.getSessionStats('s1');
    expect(recomputed.session.name).toBe('n1-recreated');
  });

  // getSessionStats drops the in-flight entry of a session that has just been deleted. The computation that
  // was already running must then not write its result back, or a session created again under the same id
  // would be served the deleted one's snapshot for the rest of the TTL.
  it('does not write a dropped in-flight computation back into the memo', async () => {
    const service = makeService(30000);
    let release!: () => void;
    const held = new Promise<void>(resolve => {
      release = resolve;
    });
    const load = jest.spyOn(service as unknown as Loaders, 'loadSessionStats').mockImplementationOnce(async () => {
      await held;
      return { session: { id: 's1', name: 'deleted-session', status: SessionStatus.READY } };
    });

    const inFlight = service.getSessionStats('s1');
    while (load.mock.calls.length === 0) {
      await new Promise<void>(resolve => {
        setImmediate(resolve);
      });
    }

    await ds.getRepository(Session).delete('s1');
    await expect(service.getSessionStats('s1')).rejects.toThrow(NotFoundException);
    release();
    await inFlight;

    await ds
      .getRepository(Session)
      .save(
        ds.getRepository(Session).create({ id: 's1', name: 'n1-recreated', status: SessionStatus.READY, config: {} }),
      );
    expect((await service.getSessionStats('s1')).session.name).toBe('n1-recreated');
    expect(load).toHaveBeenCalledTimes(2);
  });

  it('bounds every cross-session aggregate with a createdAt range predicate the standalone index serves', async () => {
    const service = makeService(30000);
    // Capture the generated SQL the same way the reserved-word regression tests above do.
    const captured: string[] = [];
    const repo = ds.getRepository(Message);
    const origCreate = repo.createQueryBuilder.bind(repo);
    jest.spyOn(repo, 'createQueryBuilder').mockImplementation((alias?: string) => {
      const qb = origCreate(alias);
      const origGetRawMany = qb.getRawMany.bind(qb);
      jest.spyOn(qb, 'getRawMany').mockImplementation((async () => {
        captured.push(qb.getQuery());
        return origGetRawMany();
      }) as never);
      return qb;
    });

    await service.getMessageStats('24h'); // time-series + byType + bySession + topChats

    expect(captured.length).toBeGreaterThan(0);
    // IDX_messages_createdAt serves `createdAt >= ?`; an unbounded GROUP BY here would be the
    // full-history scan this service must no longer run.
    for (const sql of captured) {
      expect(sql).toMatch(/WHERE\s+.*"createdAt"\s*>=/i);
    }
  });
});