tests/cloudflare-utils.unit.test.ts
Rama main · réplica de lectura del repositorio original.
import type { Context } from 'hono'
import { beforeEach, describe, expect, it, vi } from 'vitest'
import { buildReadDevicesCFQuery as buildPluginReadDevicesCFQuery } from '../supabase/functions/_backend/plugin_runtime/utils/cloudflare.ts'
import { buildDeviceIdsByInstallSourcesQuery, buildReadDevicesCFQuery, countDevicesCF, countInstallSourcesCF, formatDateCF, readBandwidthUsageCF, readDeviceUsageCF, readDevicesCF } from '../supabase/functions/_backend/utils/cloudflare.ts'
import { readDevicesSB } from '../supabase/functions/_backend/utils/supabase.ts'
const pgMocks = vi.hoisted(() => ({
getPgClient: vi.fn(),
}))
vi.mock('../supabase/functions/_backend/utils/pg.ts', async importOriginal => ({
...await importOriginal<typeof import('../supabase/functions/_backend/utils/pg.ts')>(),
getPgClient: pgMocks.getPgClient,
closeClient: vi.fn(),
}))
function createContextMock() {
return {
env: {
SUPABASE_URL: 'http://localhost:54321',
SUPABASE_SERVICE_ROLE_KEY: 'service-role-key',
CF_ANALYTICS_TOKEN: 'cf-analytics-token',
CF_ACCOUNT_ANALYTICS_ID: 'cf-account-id',
BANDWIDTH_USAGE: {},
},
req: { url: 'http://localhost/private/devices' },
get: vi.fn((key: string) => key === 'requestId' ? 'test-request' : undefined),
}
}
beforeEach(() => {
pgMocks.getPgClient.mockReset()
vi.unstubAllGlobals()
})
describe('buildReadDevicesCFQuery', () => {
it.concurrent('applies default device_id cursor pagination when no order is provided', () => {
const query = buildReadDevicesCFQuery({
app_id: 'com.example.app',
cursor: '2026-04-04 03:05:59|11111111-1111-4111-8111-111111111111',
limit: 1,
}, false)
const groupByIndex = query.indexOf('GROUP BY blob1')
const cursorIndex = query.indexOf(`WHERE device_id > '11111111-1111-4111-8111-111111111111'`)
expect(groupByIndex).toBeGreaterThan(-1)
expect(cursorIndex).toBeGreaterThan(groupByIndex)
expect(query).toContain('ORDER BY device_id ASC')
})
const installSourceArgMax = 'argMax(blob9, if(blob9 != \'\', timestamp, toDateTime(\'1970-01-01 00:00:00\'))) AS install_source'
it.concurrent('reads install_source from blob9 on default device listing', () => {
const query = buildReadDevicesCFQuery({
app_id: 'com.example.app',
limit: 1,
}, false)
expect(query).toContain(installSourceArgMax)
expect(query).not.toContain('blob10')
})
it.concurrent('reads install_source from blob9 in plugin-runtime device listing', () => {
const query = buildPluginReadDevicesCFQuery({
app_id: 'com.example.app',
limit: 1,
}, false)
expect(query).toContain(installSourceArgMax)
})
it.concurrent('does not read country code for custom id listing without device ids', () => {
const query = buildReadDevicesCFQuery({
app_id: 'com.example.app',
limit: 1,
}, true)
expect(query).toContain('custom_id != \'\'')
expect(query).not.toContain('blob5 != \'\'')
expect(query).not.toContain('blob10')
})
it.concurrent('reads country code when devices are requested by id', () => {
const query = buildReadDevicesCFQuery({
app_id: 'com.example.app',
deviceIds: ['11111111-1111-4111-8111-111111111111'],
limit: 1,
}, false)
expect(query).toContain('argMax(blob10, if(blob10 != \'\', timestamp, toDateTime(\'1970-01-01 00:00:00\'))) AS country_code')
})
it.concurrent('applies descending cursor pagination after device grouping', () => {
const query = buildReadDevicesCFQuery({
app_id: 'com.example.app',
cursor: '2026-04-04 03:05:59|11111111-1111-4111-8111-111111111111',
limit: 1,
order: [{ key: 'updated_at', sortable: 'desc' }],
}, false)
const groupByIndex = query.indexOf('GROUP BY blob1')
const cursorIndex = query.indexOf(`WHERE (updated_at < toDateTime('2026-04-04 03:05:59')`)
const tiebreakerIndex = query.indexOf(`AND device_id > '11111111-1111-4111-8111-111111111111'`)
expect(groupByIndex).toBeGreaterThan(-1)
expect(cursorIndex).toBeGreaterThan(groupByIndex)
expect(tiebreakerIndex).toBeGreaterThan(cursorIndex)
expect(query).not.toContain(`AND (timestamp < toDateTime('2026-04-04 03:05:59')`)
expect(query).toContain(`ORDER BY updated_at DESC, device_id ASC`)
})
it.concurrent('applies ascending cursor pagination after device grouping', () => {
const query = buildReadDevicesCFQuery({
app_id: 'com.example.app',
cursor: '2026-04-04 03:05:59|11111111-1111-4111-8111-111111111111',
limit: 1,
order: [{ key: 'updated_at', sortable: 'asc' }],
}, false)
const cursorIndex = query.indexOf(`WHERE (updated_at > toDateTime('2026-04-04 03:05:59')`)
const tiebreakerIndex = query.indexOf(`AND device_id > '11111111-1111-4111-8111-111111111111'`)
expect(query).toContain(`WHERE (updated_at > toDateTime('2026-04-04 03:05:59')`)
expect(tiebreakerIndex).toBeGreaterThan(cursorIndex)
expect(query).toContain(`ORDER BY updated_at ASC, device_id ASC`)
})
it.concurrent('builds install source device lookup from non-empty blob9 rows', () => {
const query = buildDeviceIdsByInstallSourcesQuery('com.example.app', ['app_store', 'amazon_appstore'])
expect(query).toContain(`WHERE index1 = 'com.example.app' AND blob9 != ''`)
expect(query).toContain('GROUP BY blob1')
expect(query).toContain(`install_source IN ('app_store', 'amazon_appstore')`)
})
it.concurrent('filters devices with updated_at greater than the provided timestamp', () => {
const query = buildReadDevicesCFQuery({
app_id: 'com.example.app',
updated_at_gt: '2026-04-04T03:05:59.000Z',
limit: 1,
}, false)
const groupByIndex = query.indexOf('GROUP BY blob1')
const innerFilterIndex = query.indexOf(`timestamp > toDateTime('2026-04-04 03:05:59')`)
const outerFilterIndex = query.indexOf(`WHERE updated_at > toDateTime('2026-04-04 03:05:59')`)
expect(groupByIndex).toBeGreaterThan(-1)
expect(innerFilterIndex).toBeGreaterThan(-1)
expect(innerFilterIndex).toBeLessThan(groupByIndex)
expect(outerFilterIndex).toBeGreaterThan(groupByIndex)
})
it.concurrent('filters devices by latest aggregated platform and version after grouping', () => {
const query = buildReadDevicesCFQuery({
app_id: 'com.example.app',
platform: 'ios',
version_name: '1.2.3',
limit: 1,
}, false)
const groupByIndex = query.indexOf('GROUP BY blob1')
const outerWhereIndex = query.indexOf('WHERE platform = 1 AND version_name = \'1.2.3\'')
const altOuterWhereIndex = query.indexOf('WHERE version_name = \'1.2.3\' AND platform = 1')
expect(query).not.toContain(`blob2 = '1.2.3'`)
expect(Math.max(outerWhereIndex, altOuterWhereIndex)).toBeGreaterThan(groupByIndex)
})
it.concurrent('filters devices by latest aggregated default_channel after grouping', () => {
const query = buildReadDevicesCFQuery({
app_id: 'com.example.app',
default_channel: 'beta\'s',
limit: 1,
}, false)
const groupByIndex = query.indexOf('GROUP BY blob1')
expect(query).not.toContain(`blob7 = `)
expect(query.indexOf(`default_channel = 'beta''s'`)).toBeGreaterThan(groupByIndex)
})
it.concurrent('filters devices by os_version gte after grouping', () => {
const query = buildReadDevicesCFQuery({
app_id: 'com.example.app',
platform: 'android',
os_version_compare: { op: 'gte', value: '14' },
version_name_compare: { op: 'lte', value: '1.2.3' },
limit: 1,
}, false)
const groupByIndex = query.indexOf('GROUP BY blob1')
expect(query).toContain('argMax(blob4, timestamp) AS os_version')
expect(query.indexOf('>= 14')).toBeGreaterThan(groupByIndex)
expect(query.indexOf('splitByChar(\'.\', os_version)')).toBeGreaterThan(groupByIndex)
expect(query.indexOf('splitByChar(\'.\', version_name)')).toBeGreaterThan(groupByIndex)
expect(query).not.toContain('concat(')
})
it.concurrent('filters devices by multiple version names after grouping', () => {
const query = buildReadDevicesCFQuery({
app_id: 'com.example.app',
version_name: ['1.2.3', '2.0.0'],
limit: 1,
}, false)
const groupByIndex = query.indexOf('GROUP BY blob1')
const outerWhereIndex = query.indexOf(`WHERE version_name IN ('1.2.3', '2.0.0')`)
expect(query).not.toContain(`blob2 IN ('1.2.3', '2.0.0')`)
expect(outerWhereIndex).toBeGreaterThan(groupByIndex)
})
it.concurrent('filters plugin-runtime devices by multiple version names', () => {
const query = buildPluginReadDevicesCFQuery({
app_id: 'com.example.app',
version_name: ['1.2.3', '2.0.0'],
limit: 1,
}, false)
expect(query).toContain(`blob2 IN ('1.2.3', '2.0.0')`)
})
it.concurrent('applies search against latest aggregated device fields after grouping', () => {
const query = buildReadDevicesCFQuery({
app_id: 'com.example.app',
platform: 'android',
search: 'abc',
limit: 1,
}, false)
const groupByIndex = query.indexOf('GROUP BY blob1')
const searchIndex = query.indexOf(`position('abc' IN toLower(device_id))`)
expect(query).not.toContain(`toLower(blob1)`)
expect(searchIndex).toBeGreaterThan(groupByIndex)
expect(query.indexOf('platform = 0')).toBeGreaterThan(groupByIndex)
})
it('maps install_source from Analytics Engine rows in readDevicesCF', async () => {
let query = ''
const fetchMock = vi.fn(async (_url: string, init?: RequestInit) => {
query = String(init?.body ?? '')
return new Response(JSON.stringify({
meta: [
{ name: 'device_id', type: 'String' },
{ name: 'version_name', type: 'String' },
{ name: 'plugin_version', type: 'String' },
{ name: 'os_version', type: 'String' },
{ name: 'custom_id', type: 'String' },
{ name: 'version_build', type: 'String' },
{ name: 'default_channel', type: 'String' },
{ name: 'key_id', type: 'String' },
{ name: 'install_source', type: 'String' },
{ name: 'platform', type: 'Float64' },
{ name: 'is_prod', type: 'Float64' },
{ name: 'is_emulator', type: 'Float64' },
{ name: 'updated_at', type: 'DateTime' },
],
data: [{
device_id: '11111111-1111-4111-8111-111111111111',
version_name: '1.0.0',
plugin_version: '8.0.0',
os_version: '17.0',
custom_id: '',
version_build: '1',
default_channel: 'production',
key_id: '',
install_source: 'app_store',
platform: 1,
is_prod: 1,
is_emulator: 0,
updated_at: '2026-04-04 03:05:59',
}],
}), {
status: 200,
headers: { 'content-type': 'application/json' },
})
})
vi.stubGlobal('fetch', fetchMock)
const devices = await readDevicesCF(createContextMock() as unknown as Context, {
app_id: 'com.example.app',
limit: 1,
}, false)
expect(query).toContain(installSourceArgMax)
expect(devices).toHaveLength(1)
expect(devices[0]?.install_source).toBe('app_store')
})
})
describe('countDevicesCF', () => {
it('does not read install source blob on default device counts', async () => {
let query = ''
const fetchMock = vi.fn(async (_url: string, init?: RequestInit) => {
query = String(init?.body ?? '')
return new Response(JSON.stringify({
meta: [{ name: 'total', type: 'UInt64' }],
data: [{ total: 12 }],
}), {
status: 200,
headers: { 'content-type': 'application/json' },
})
})
vi.stubGlobal('fetch', fetchMock)
const count = await countDevicesCF(
createContextMock() as unknown as Context,
'com.example.app',
false,
)
expect(count).toBe(12)
expect(query).toContain('COUNT(DISTINCT blob1) AS total')
expect(query).not.toContain('blob9')
expect(query).not.toContain('install_source')
})
})
describe('countDevicesCF default channel', () => {
it('filters counts by latest aggregated default_channel', async () => {
let query = ''
const fetchMock = vi.fn(async (_url: string, init?: RequestInit) => {
query = String(init?.body ?? '')
return new Response(JSON.stringify({
meta: [{ name: 'total', type: 'UInt64' }],
data: [{ total: 3 }],
}), {
status: 200,
headers: { 'content-type': 'application/json' },
})
})
vi.stubGlobal('fetch', fetchMock)
const count = await countDevicesCF(
createContextMock() as unknown as Context,
'com.example.app',
false,
[],
undefined,
undefined,
{ defaultChannel: 'beta' },
)
expect(count).toBe(3)
expect(query).toContain('argMax(blob7, timestamp) AS default_channel')
expect(query.indexOf(`default_channel = 'beta'`)).toBeGreaterThan(query.indexOf('GROUP BY blob1'))
})
})
describe('readBandwidthUsageCF', () => {
it('throws strict bandwidth read failures instead of returning empty usage', async () => {
const fetchMock = vi.fn(async () => {
throw new Error('analytics unavailable')
})
vi.stubGlobal('fetch', fetchMock)
await expect(readBandwidthUsageCF(
createContextMock() as unknown as Context,
'com.example.app',
'2026-06-01',
'2026-07-01',
{ throwOnError: true },
)).rejects.toThrow('runQueryToCFA encountered an error')
expect(fetchMock).toHaveBeenCalledTimes(1)
})
it('includes Cloudflare Analytics HTTP error details in strict bandwidth failures', async () => {
const fetchMock = vi.fn(async () => new Response(JSON.stringify({
errors: [{ message: 'bad Analytics Engine query' }],
}), {
status: 400,
headers: { 'content-type': 'application/json' },
}))
vi.stubGlobal('fetch', fetchMock)
await expect(readBandwidthUsageCF(
createContextMock() as unknown as Context,
'com.example.app',
'2026-06-01',
'2026-07-01',
{ throwOnError: true },
)).rejects.toThrow('runQueryToCFA HTTP 400: {"errors":[{"message":"bad Analytics Engine query"}]}')
})
})
describe('countInstallSourcesCF', () => {
it('counts latest install sources in a dedicated aggregate query', async () => {
let query = ''
const fetchMock = vi.fn(async (_url: string, init?: RequestInit) => {
query = String(init?.body ?? '')
return new Response(JSON.stringify({
meta: [
{ name: 'install_source', type: 'String' },
{ name: 'total', type: 'UInt64' },
],
data: [
{ install_source: 'app_store', total: 3 },
{ install_source: 'testflight', total: 2 },
],
}), {
status: 200,
headers: { 'content-type': 'application/json' },
})
})
vi.stubGlobal('fetch', fetchMock)
const counts = await countInstallSourcesCF(createContextMock() as unknown as Context, 'com.example.app')
expect(counts).toEqual({ app_store: 3, testflight: 2 })
expect(fetchMock).toHaveBeenCalledTimes(1)
expect(query).toContain('GROUP BY install_source')
expect(query).toContain('argMax(blob9, timestamp) AS install_source')
expect(query).toContain('COUNT() AS total')
expect(query).not.toContain('COUNT(DISTINCT blob1) AS total')
expect(query).not.toContain('CASE WHEN blob9')
})
it('reuses cached install source counts for the same app', async () => {
const cachedResponses = new Map<string, Response>()
vi.stubGlobal('caches', {
open: vi.fn(async () => ({
match: vi.fn(async (request: Request) => cachedResponses.get(request.url)),
put: vi.fn(async (request: Request, response: Response) => {
cachedResponses.set(request.url, response)
}),
})),
})
const fetchMock = vi.fn(async () => new Response(JSON.stringify({
meta: [
{ name: 'install_source', type: 'String' },
{ name: 'total', type: 'UInt64' },
],
data: [{ install_source: 'app_store', total: 3 }],
}), {
status: 200,
headers: { 'content-type': 'application/json' },
}))
vi.stubGlobal('fetch', fetchMock)
const context = createContextMock() as unknown as Context
await expect(countInstallSourcesCF(context, 'com.example.app')).resolves.toEqual({ app_store: 3 })
await expect(countInstallSourcesCF(context, 'com.example.app')).resolves.toEqual({ app_store: 3 })
expect(fetchMock).toHaveBeenCalledTimes(1)
})
})
describe('readDevicesSB', () => {
async function readDevicesQuery(params: Parameters<typeof readDevicesSB>[1], customIdMode = false) {
const query = vi.fn(async (_text: string, _values: unknown[]) => ({ rows: [] }))
pgMocks.getPgClient.mockResolvedValueOnce({ query })
await readDevicesSB(createContextMock() as unknown as Context, params, customIdMode)
expect(query).toHaveBeenCalledTimes(1)
const [text, values] = query.mock.calls[0]!
return { text, values }
}
it('applies default device_id cursor pagination when no order is provided', async () => {
const { text, values } = await readDevicesQuery({
app_id: 'com.example.app',
cursor: '2026-04-04 03:05:59|11111111-1111-4111-8111-111111111111',
limit: 1,
})
expect(text).toContain('FROM public.devices WHERE app_id = $1 AND device_id > $2 ORDER BY device_id ASC LIMIT $3')
expect(values).toEqual(['com.example.app', '11111111-1111-4111-8111-111111111111', 2])
expect(pgMocks.getPgClient).toHaveBeenLastCalledWith(expect.anything(), false)
})
it('applies install source filters', async () => {
const { text, values } = await readDevicesQuery({
app_id: 'com.example.app',
installSources: ['app_store', 'testflight'],
limit: 1,
})
expect(text).toContain('install_source = ANY($2::text[])')
expect(values).toContainEqual(['app_store', 'testflight'])
})
it('applies platform and version_name filters together', async () => {
const { text, values } = await readDevicesQuery({
app_id: 'com.example.app',
platform: 'android',
version_name: '2.0.0',
limit: 1,
})
expect(text).toContain('version_name = $2')
expect(text).toContain('platform = $3::public.platform_os')
expect(values).toEqual(['com.example.app', '2.0.0', 'android', 2])
})
it('applies default_channel filter', async () => {
const { text, values } = await readDevicesQuery({
app_id: 'com.example.app',
default_channel: 'beta',
limit: 1,
})
expect(text).toContain('default_channel = $2')
expect(values).toEqual(['com.example.app', 'beta', 2])
})
it('filters devices with updated_at greater than the provided timestamp', async () => {
const { text, values } = await readDevicesQuery({
app_id: 'com.example.app',
updated_at_gt: '2026-04-04T03:05:59.000Z',
limit: 1,
})
expect(text).toContain('updated_at > $2::timestamptz')
expect(values).toEqual(['com.example.app', '2026-04-04T03:05:59.000Z', 2])
})
it('orders devices by updated_at when requested', async () => {
const { text } = await readDevicesQuery({
app_id: 'com.example.app',
order: [{ key: 'updated_at', sortable: 'desc' }],
limit: 1,
})
expect(text).toContain('ORDER BY updated_at DESC, device_id ASC')
})
it('returns rows as PostgREST JSON', async () => {
const { text } = await readDevicesQuery({ app_id: 'com.example.app', limit: 1 })
expect(text).toContain('pg_catalog.to_json(q.*)')
})
})
describe('formatDateCF', () => {
it.concurrent.each([
['Date objects', new Date('2026-03-17T09:08:07.654Z')],
['ISO strings with offsets', '2026-03-17T10:08:07+01:00'],
] as const)('normalizes %s to a stable UTC SQL timestamp', (_name, input) => {
expect(formatDateCF(input)).toBe('2026-03-17 09:08:07')
})
})
function createDeviceUsageContext() {
return {
env: {
CF_ANALYTICS_TOKEN: 'cf-analytics-token',
CF_ACCOUNT_ANALYTICS_ID: 'cf-account-id',
DEVICE_USAGE: {},
},
get: (key: string) => key === 'requestId' ? 'device-usage-test' : undefined,
} as unknown as Context
}
describe('readDeviceUsageCF', () => {
it('aggregates daily MAU inside Analytics Engine instead of returning one row per device', async () => {
const queries: string[] = []
vi.stubGlobal('fetch', vi.fn(async (_url: string, init?: RequestInit) => {
queries.push(String(init?.body))
return new Response(JSON.stringify({
meta: [
{ name: 'date', type: 'String' },
{ name: 'app_id', type: 'String' },
{ name: 'org_id', type: 'String' },
{ name: 'mau', type: 'UInt64' },
],
data: [
{ date: '2026-09-01', app_id: 'com.example.app', org_id: 'org-new', mau: '600000' },
{ date: '2026-09-01', app_id: 'com.example.app', org_id: 'org-old', mau: '5' },
{ date: '2026-09-02', app_id: 'com.example.app', org_id: 'org-new', mau: '328104' },
],
rows: 3,
}), { status: 200, headers: { 'content-type': 'application/json' } })
}))
const usage = await readDeviceUsageCF(createDeviceUsageContext(), 'com.example.app', '2026-09-01T00:00:00Z', '2026-10-01T00:00:00Z', { throwOnError: true })
expect(queries).toHaveLength(1)
// Device ids stay inside the inner GROUP BY; the result is one row per day/org.
expect(queries[0]).toMatch(/count\(\) AS mau/)
expect(queries[0]).toMatch(/GROUP BY date, app_id, org_id/)
expect(queries[0]!.split('\n')[1]!.trim()).toBe('date,')
expect(usage).toEqual([
{ date: '2026-09-01', app_id: 'com.example.app', org_id: 'org-new', mau: 600005 },
{ date: '2026-09-02', app_id: 'com.example.app', org_id: 'org-new', mau: 328104 },
])
})
})