🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.
import { eq, inArray } from "drizzle-orm";
import { FastifyReply, FastifyRequest } from "fastify";
import { DateTime } from "luxon";
import { clickhouse } from "../../db/clickhouse/clickhouse.js";
import { db } from "../../db/postgres/postgres.js";
import { member, sites, user } from "../../db/postgres/schema.js";
import { getOrganizationSubscriptions } from "../../services/admin/subscriptionService.js";
interface EventCountResult {
site_id: string;
total_events: number;
}
export interface AdminOrganizationData {
id: string;
name: string;
createdAt: string;
monthlyEventCount: number;
overMonthlyLimit: boolean;
subscription: {
id: string | null;
source: "custom" | "override" | "stripe" | "appsumo" | "free";
planName: string;
status: string;
eventLimit: number;
currentPeriodEnd: Date;
cancelAtPeriodEnd?: boolean;
interval?: string;
memberLimit?: number | null;
siteLimit?: number | null;
};
planOverride: string | null;
customPlan: { events: number; members: number | null; websites: number | null } | null;
stripeCustomerId: string | null;
stripeDashboardUrl: string | null;
sites: {
siteId: number;
name: string;
domain: string;
type: "web" | "mobile" | null;
createdAt: string;
eventsLast24Hours: number;
eventsLast30Days: number;
}[];
members: {
memberId: string;
userId: string;
name: string;
email: string;
role: string;
createdAt: string;
}[];
}
export async function getAdminOrganizations(request: FastifyRequest, reply: FastifyReply) {
try {
// Get all organizations with their basic data
const organizationsData = await db.query.organization.findMany({
orderBy: (organization, { desc }) => [desc(organization.createdAt)],
});
// Get all members for all organizations
const allMembers = await db
.select({
memberId: member.id,
organizationId: member.organizationId,
userId: member.userId,
role: member.role,
createdAt: member.createdAt,
userName: user.name,
userEmail: user.email,
})
.from(member)
.innerJoin(user, eq(member.userId, user.id));
// Create map of organization IDs to their members
const orgMembersMap = new Map<string, any[]>();
for (const memberData of allMembers) {
if (!orgMembersMap.has(memberData.organizationId)) {
orgMembersMap.set(memberData.organizationId, []);
}
orgMembersMap.get(memberData.organizationId)?.push({
memberId: memberData.memberId,
userId: memberData.userId,
name: memberData.userName,
email: memberData.userEmail,
role: memberData.role,
createdAt: memberData.createdAt,
});
}
// Get all sites for all organizations
const allOrgIds = organizationsData.map(org => org.id);
const orgSites = await db
.select({
siteId: sites.siteId,
name: sites.name,
domain: sites.domain,
type: sites.type,
createdAt: sites.createdAt,
organizationId: sites.organizationId,
})
.from(sites)
.where(allOrgIds.length > 0 ? inArray(sites.organizationId, allOrgIds) : undefined);
// Get event counts for the past 24 hours and 30 days from ClickHouse
// Bounds are rendered and parsed in UTC so neither Node's nor ClickHouse's
// timezone shifts the window.
const now = DateTime.utc();
const yesterday = now.minus({ hours: 24 });
const thirtyDaysAgo = now.minus({ days: 30 });
let siteEventMap24h = new Map<number, number>();
let siteEventMap30d = new Map<number, number>();
try {
// Query for last 24 hours
const eventCounts24hResult = await clickhouse.query({
query: `
SELECT
site_id,
sum(event_count) as total_events
FROM
hourly_events_by_site_mv_target
WHERE
event_hour >= toDateTime('${yesterday.toFormat("yyyy-MM-dd HH:mm:ss")}', 'UTC') AND
event_hour <= toDateTime('${now.toFormat("yyyy-MM-dd HH:mm:ss")}', 'UTC')
GROUP BY
site_id
`,
format: "JSONEachRow",
});
const rawEventCounts24h = await eventCounts24hResult.json();
const eventCounts24h = rawEventCounts24h as EventCountResult[];
for (const event of eventCounts24h) {
siteEventMap24h.set(Number(event.site_id), event.total_events);
}
// Query for last 30 days
const eventCounts30dResult = await clickhouse.query({
query: `
SELECT
site_id,
sum(event_count) as total_events
FROM
hourly_events_by_site_mv_target
WHERE
event_hour >= toDateTime('${thirtyDaysAgo.toFormat("yyyy-MM-dd HH:mm:ss")}', 'UTC') AND
event_hour <= toDateTime('${now.toFormat("yyyy-MM-dd HH:mm:ss")}', 'UTC')
GROUP BY
site_id
`,
format: "JSONEachRow",
});
const rawEventCounts30d = await eventCounts30dResult.json();
const eventCounts30d = rawEventCounts30d as EventCountResult[];
for (const event of eventCounts30d) {
siteEventMap30d.set(Number(event.site_id), event.total_events);
}
} catch (clickhouseError) {
request.log.warn(clickhouseError as Error, "ClickHouse query failed, continuing without event counts");
}
// Create map of organization IDs to their sites with event counts
const orgSitesMap = new Map<string, any[]>();
for (const site of orgSites) {
if (site.organizationId) {
if (!orgSitesMap.has(site.organizationId)) {
orgSitesMap.set(site.organizationId, []);
}
orgSitesMap.get(site.organizationId)?.push({
siteId: site.siteId,
name: site.name,
domain: site.domain,
type: site.type,
createdAt: site.createdAt,
eventsLast24Hours: siteEventMap24h.get(site.siteId) || 0,
eventsLast30Days: siteEventMap30d.get(site.siteId) || 0,
});
}
}
// Get subscription data for organizations
const orgSubscriptionMap = await getOrganizationSubscriptions(organizationsData, true);
// Build the final response with subscription data
const enrichedOrganizations: AdminOrganizationData[] = organizationsData.map(org => {
const subscriptionData = orgSubscriptionMap.get(org.id)!; // Non-null assertion since service always returns data
return {
id: org.id,
name: org.name,
createdAt: org.createdAt,
monthlyEventCount: org.monthlyEventCount || 0,
overMonthlyLimit: org.overMonthlyLimit || false,
subscription: subscriptionData,
planOverride: org.planOverride,
customPlan: org.customPlan,
stripeCustomerId: org.stripeCustomerId,
stripeDashboardUrl: org.stripeCustomerId
? `https://dashboard.stripe.com/${process.env.STRIPE_SECRET_KEY?.startsWith("sk_live") ? "" : "test/"}customers/${org.stripeCustomerId}`
: null,
sites: orgSitesMap.get(org.id) || [],
members: orgMembersMap.get(org.id) || [],
};
});
return reply.status(200).send(enrichedOrganizations);
} catch (error) {
request.log.error({ err: error }, "Get Admin Organizations Error");
return reply.status(500).send({
error: "Failed to fetch organizations data",
details: error instanceof Error ? error.message : "Unknown error",
});
}
}