Skip to content

Open-source platform for 3D AI agents. Turn text or a photo into a rigged, animated GLB avatar, give it an LLM brain, memory and a wallet, and embed it anywhere with one web component. Remote MCP server, x402 payments, Three.js, browser-native.

8.6 KB
Raw Blame
// api/_lib/irl-analytics.js
//
// Site-wide /irl usage analytics — the one thing the feature never had. Every
// other IRL surface (pins, interactions, drops, world lines) tracks state
// per-pin or per-owner; nothing rolled it up into "how many people actually
// use this feature, and through which door." This module is that rollup.
//
// Design constraints, both driven by the existing IRL privacy posture
// (see docs/irl/THREAT-MODEL.md, the H4/H5/H6 notes in api/irl/pins.js and
// api/irl/interactions.js):
//   - Never store a raw device token or exact coordinate. Events carry a
//     16-hex-char sha256 prefix of the device token (irreversible, matches
//     the `ipHash` pattern already used for sweep detection in pins.js) and,
//     when a location is available, only the ~150m geocell7 the rest of the
//     codebase already treats as the public-safe location granularity.
//   - Bounded retention. `irl_events` is swept by the existing IRL reaper
//     cron (api/cron/irl-reap.js) the same way interaction rows are, so a
//     coarse usage trail doesn't accumulate forever either.
//   - Logging must never be able to break the request it's attached to.
//     logIrlEvent() swallows and logs every failure internally.
//
// Follows the same self-provisioning-schema convention as the rest of
// api/irl/* (CREATE TABLE IF NOT EXISTS inline, cached per warm container —
// see ensureTable() in api/irl/pins.js) rather than the api/_lib/migrations
// pipeline, which IRL tables don't participate in.

import { sql } from './db.js';
import { sha256 } from './crypto.js';
import { encodeGeohash } from './geohash.js';

const GEOCELL_PRECISION = 7; // matches api/irl/pins.js's density-cell precision

let schemaReady = false;

export async function ensureIrlAnalyticsSchema() {
	if (schemaReady) return;
	await sql`
		CREATE TABLE IF NOT EXISTS irl_events (
			id          BIGSERIAL PRIMARY KEY,
			event_type  TEXT NOT NULL,
			pin_id      UUID,
			geocell7    TEXT,
			device_hash TEXT,
			metadata    JSONB,
			created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
		)
	`;
	await sql`CREATE INDEX IF NOT EXISTS irl_events_type_created ON irl_events (event_type, created_at DESC)`;
	await sql`CREATE INDEX IF NOT EXISTS irl_events_created ON irl_events (created_at)`;

	await sql`
		CREATE TABLE IF NOT EXISTS irl_pin_shares (
			id          UUID DEFAULT gen_random_uuid() PRIMARY KEY,
			pin_id      UUID NOT NULL REFERENCES irl_pins(id) ON DELETE CASCADE,
			token       TEXT NOT NULL UNIQUE,
			image_key   TEXT NOT NULL,
			image_url   TEXT NOT NULL,
			device_hash TEXT,
			view_count  BIGINT NOT NULL DEFAULT 0,
			created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
		)
	`;
	await sql`CREATE INDEX IF NOT EXISTS irl_pin_shares_pin ON irl_pin_shares (pin_id)`;

	schemaReady = true;
}

/** sha256(token).slice(0,16) — same opaque-prefix convention as pins.js's ipHash. Never stores the raw token. */
export async function hashDeviceToken(token) {
	if (!token || typeof token !== 'string') return null;
	return (await sha256(token)).slice(0, 16);
}

// event_type vocabulary — keep this list authoritative so a typo'd type never
// silently fragments the rollup below.
export const IRL_EVENT_TYPES = new Set([
	'pin_created',   // a placement landed (AR method carried in metadata.mode: webxr | quicklook | pin | map)
	'nearby_fetch',  // a browse/discovery read of the nearby feed (metadata.count = pins returned)
	'share_created', // a shareable card was minted for a pin
	'share_viewed',  // a minted share link was opened
]);

/**
 * Fire-and-forget event log. Never throws — a caller on the hot path (placing
 * a pin, reading nearby) must never fail or slow down because analytics did.
 *
 * @param {object} opts
 * @param {string} opts.type IRL_EVENT_TYPES member
 * @param {string} [opts.pinId]
 * @param {number} [opts.lat] @param {number} [opts.lng] — coarsened to geocell7 before storage, never kept raw
 * @param {string} [opts.deviceToken] — hashed before storage, never kept raw
 * @param {object} [opts.metadata]
 */
export async function logIrlEvent({ type, pinId, lat, lng, deviceToken, metadata } = {}) {
	if (!IRL_EVENT_TYPES.has(type)) return;
	try {
		await ensureIrlAnalyticsSchema();
		const geocell7 = Number.isFinite(lat) && Number.isFinite(lng) ? encodeGeohash(lat, lng, GEOCELL_PRECISION) : null;
		const deviceHash = await hashDeviceToken(deviceToken);
		await sql`
			INSERT INTO irl_events (event_type, pin_id, geocell7, device_hash, metadata)
			VALUES (${type}, ${pinId || null}, ${geocell7}, ${deviceHash}, ${metadata ? JSON.stringify(metadata) : null}::jsonb)
		`;
	} catch (err) {
		console.error('[irl-analytics] logIrlEvent failed (non-fatal)', { type, reason: err?.message || String(err) });
	}
}

/** Record a share-link open, best-effort, and bump the share row's view_count. */
export async function recordShareView(token) {
	try {
		await ensureIrlAnalyticsSchema();
		const rows = await sql`
			UPDATE irl_pin_shares SET view_count = view_count + 1
			WHERE token = ${token}
			RETURNING pin_id
		`;
		if (rows[0]) {
			await logIrlEvent({ type: 'share_viewed', pinId: rows[0].pin_id, metadata: { token } });
		}
		return rows[0] || null;
	} catch (err) {
		console.error('[irl-analytics] recordShareView failed (non-fatal)', { reason: err?.message || String(err) });
		return null;
	}
}

const WINDOWS = { '24h': '24 hours', '7d': '7 days', '30d': '30 days' };

/**
 * Owner-facing rollup — GET /api/irl/analytics. Every number here is a real
 * aggregate query, no cached/sampled/fake figures.
 */
export async function getIrlAnalyticsSummary() {
	await ensureIrlAnalyticsSchema();

	const windows = {};
	for (const [key, interval] of Object.entries(WINDOWS)) {
		const [placed] = await sql`
			SELECT count(*)::int AS n, count(DISTINCT device_hash)::int AS unique_devices
			FROM irl_events WHERE event_type = 'pin_created' AND created_at > now() - ${interval}::interval
		`;
		const [nearby] = await sql`
			SELECT count(*)::int AS n, count(DISTINCT device_hash)::int AS unique_devices
			FROM irl_events WHERE event_type = 'nearby_fetch' AND created_at > now() - ${interval}::interval
		`;
		const interactionRows = await sql`
			SELECT type, count(*)::int AS n FROM irl_interactions
			WHERE created_at > now() - ${interval}::interval GROUP BY type
		`;
		const [shares] = await sql`
			SELECT count(*)::int AS created, coalesce(sum(view_count), 0)::int AS views
			FROM irl_pin_shares WHERE created_at > now() - ${interval}::interval
		`;
		const [drops] = await sql`
			SELECT count(*)::int AS n FROM irl_drop_claims
			WHERE status = 'confirmed' AND confirmed_at > now() - ${interval}::interval
		`;

		windows[key] = {
			pins_placed: placed?.n || 0,
			unique_placers: placed?.unique_devices || 0,
			nearby_fetches: nearby?.n || 0,
			unique_browsers: nearby?.unique_devices || 0,
			interactions: Object.fromEntries(interactionRows.map((r) => [r.type, r.n])),
			shares_created: shares?.created || 0,
			share_views: shares?.views || 0,
			drops_claimed: drops?.n || 0,
		};
	}

	const placementModes = await sql`
		SELECT coalesce(metadata->>'mode', 'unknown') AS mode, count(*)::int AS n
		FROM irl_events
		WHERE event_type = 'pin_created' AND created_at > now() - interval '30 days'
		GROUP BY mode ORDER BY n DESC
	`;

	// The series gets an explicit column alias, `AS d(day)`, and every reference is
	// qualified. Without it the bare `day` in each join condition is ambiguous between
	// the series column and the sub-select's own `day`, and Postgres refuses the whole
	// query with `column reference "day" is ambiguous`, which the caller then reported
	// as an empty summary rather than as the broken query it was.
	const dailySeries = await sql`
		SELECT
			d.day::date AS day,
			coalesce(p.n, 0)::int AS pins_placed,
			coalesce(n.n, 0)::int AS nearby_fetches
		FROM generate_series(current_date - interval '29 days', current_date, interval '1 day') AS d(day)
		LEFT JOIN (
			SELECT date_trunc('day', created_at) AS day, count(*) AS n
			FROM irl_events WHERE event_type = 'pin_created' AND created_at > now() - interval '30 days'
			GROUP BY 1
		) p ON p.day = d.day
		LEFT JOIN (
			SELECT date_trunc('day', created_at) AS day, count(*) AS n
			FROM irl_events WHERE event_type = 'nearby_fetch' AND created_at > now() - interval '30 days'
			GROUP BY 1
		) n ON n.day = d.day
		ORDER BY d.day ASC
	`;

	return {
		windows,
		placement_modes_30d: Object.fromEntries(placementModes.map((r) => [r.mode, r.n])),
		daily_series_30d: dailySeries.map((r) => ({
			day: r.day instanceof Date ? r.day.toISOString().slice(0, 10) : String(r.day).slice(0, 10),
			pins_placed: r.pins_placed,
			nearby_fetches: r.nearby_fetches,
		})),
		generated_at: new Date().toISOString(),
	};
}