Open source Loom alternative. Beautiful, shareable screen recordings.
Rust appcapcossloommacnextjsnextjs15open-sourceossreactrecordrustscreen-capturescreen-recorderscreenshotsolidjstauritauri-apptypescriptvite
import { db } from "@cap/database";
import { videos } from "@cap/database/schema";
import { Tinybird } from "@cap/web-backend";
import { Video } from "@cap/web-domain";
import { eq } from "drizzle-orm";
import { Effect } from "effect";
import { runPromise } from "@/lib/server";
const DAY_IN_MS = 24 * 60 * 60 * 1000;
const MIN_RANGE_DAYS = 1;
const MAX_RANGE_DAYS = 90;
const DEFAULT_RANGE_DAYS = MAX_RANGE_DAYS;
// Escape backslashes BEFORE quotes: ClickHouse honors C-style `\'` inside
// single-quoted literals, so doubling quotes alone lets a trailing backslash
// neutralise the doubled quote and break out of the string (SQL injection).
const escapeLiteral = (value: string) =>
value.replace(/\\/g, "\\\\").replace(/'/g, "''");
const formatDate = (date: Date) => date.toISOString().slice(0, 10);
const formatDateTime = (date: Date) =>
date.toISOString().slice(0, 19).replace("T", " ");
const buildConditions = (clauses: Array<string | undefined>) =>
clauses.filter((clause): clause is string => Boolean(clause)).join(" AND ");
const normalizeRangeDays = (rangeDays?: number) => {
if (!Number.isFinite(rangeDays)) return DEFAULT_RANGE_DAYS;
const normalized = Math.floor(rangeDays as number);
if (normalized <= 0) return DEFAULT_RANGE_DAYS;
return Math.max(MIN_RANGE_DAYS, Math.min(normalized, MAX_RANGE_DAYS));
};
export interface VideoViewCountOptions {
rangeDays?: number;
}
export async function getVideoViewCount(
videoId: string,
options?: VideoViewCountOptions,
) {
if (!videoId) throw new Error("Video ID is required");
const [{ orgId } = { orgId: null }] = await db()
.select({ orgId: videos.orgId })
.from(videos)
.where(eq(videos.id, Video.VideoId.make(videoId)))
.limit(1);
return runPromise(
Effect.gen(function* () {
const tinybird = yield* Tinybird;
const rangeDays = normalizeRangeDays(options?.rangeDays);
const now = new Date();
const from = new Date(now.getTime() - rangeDays * DAY_IN_MS);
const pathname = `/s/${videoId}`;
const aggregateConditions = [
orgId ? `tenant_id = '${escapeLiteral(orgId)}'` : undefined,
`pathname = '${escapeLiteral(pathname)}'`,
`date BETWEEN toDate('${formatDate(from)}') AND toDate('${formatDate(now)}')`,
];
const aggregateSql = `SELECT coalesce(uniqMerge(visits), 0) AS views FROM analytics_pages_mv WHERE ${buildConditions(aggregateConditions)}`;
const rawConditions = [
"action = 'page_hit'",
orgId ? `tenant_id = '${escapeLiteral(orgId)}'` : undefined,
`pathname = '${escapeLiteral(pathname)}'`,
`timestamp BETWEEN toDateTime('${formatDateTime(from)}') AND toDateTime('${formatDateTime(now)}')`,
];
const rawSql = `SELECT coalesce(uniq(session_id), 0) AS views FROM analytics_events WHERE ${buildConditions(rawConditions)}`;
const querySql = (sql: string) =>
tinybird.querySql<{ views: number }>(sql).pipe(
Effect.catchAll((e) => {
console.error("tinybird sql error", e);
return Effect.succeed({ data: [] });
}),
);
const aggregateResult = yield* querySql(aggregateSql);
const fallbackResult = aggregateResult.data?.length
? aggregateResult
: yield* querySql(rawSql);
const data = fallbackResult?.data ?? [];
const firstItem = data[0];
const count =
typeof firstItem === "number"
? firstItem
: typeof firstItem === "object" &&
firstItem !== null &&
"views" in firstItem
? Number(firstItem.views ?? 0)
: 0;
return { count: Number.isFinite(count) ? count : 0 };
}),
);
}