leaflet.pub
a tool for shared writing and social publishing
awarm.space
1/**
2 * Tinybird Definitions
3 *
4 * Datasource matching the Vercel Web Analytics drain schema,
5 * endpoint pipes for publication analytics, and typed client.
6 *
7 * Column names use camelCase to match the JSON keys sent by
8 * Vercel's analytics drain (NDJSON format).
9 */
10
11import {
12 defineDatasource,
13 defineEndpoint,
14 Tinybird,
15 node,
16 t,
17 p,
18 engine,
19 type InferRow,
20 type InferParams,
21 type InferOutputRow,
22} from "@tinybirdco/sdk";
23
24// ============================================================================
25// Datasources
26// ============================================================================
27
28/**
29 * Vercel Web Analytics drain events.
30 * Column names match the Vercel drain JSON keys exactly.
31 * `timestamp` is stored as UInt64 (Unix millis) as sent by Vercel.
32 */
33export const analyticsEvents = defineDatasource("analytics_events", {
34 description: "Vercel Web Analytics drain events",
35 schema: {
36 timestamp: t.uint64(),
37 eventType: t.string().lowCardinality(),
38 eventName: t.string().default(""),
39 eventData: t.string().default(""),
40 sessionId: t.uint64(),
41 deviceId: t.uint64(),
42 origin: t.string(),
43 path: t.string(),
44 referrer: t.string().default(""),
45 queryParams: t.string().default(""),
46 route: t.string().default(""),
47 country: t.string().lowCardinality().default(""),
48 region: t.string().default(""),
49 city: t.string().default(""),
50 osName: t.string().lowCardinality().default(""),
51 osVersion: t.string().default(""),
52 clientName: t.string().lowCardinality().default(""),
53 clientType: t.string().lowCardinality().default(""),
54 clientVersion: t.string().default(""),
55 deviceType: t.string().lowCardinality().default(""),
56 deviceBrand: t.string().default(""),
57 deviceModel: t.string().default(""),
58 browserEngine: t.string().default(""),
59 browserEngineVersion: t.string().default(""),
60 sdkVersion: t.string().default(""),
61 sdkName: t.string().default(""),
62 sdkVersionFull: t.string().default(""),
63 vercelEnvironment: t.string().lowCardinality().default(""),
64 vercelUrl: t.string().default(""),
65 flags: t.string().default(""),
66 deployment: t.string().default(""),
67 schema: t.string().default(""),
68 projectId: t.string().default(""),
69 ownerId: t.string().default(""),
70 dataSourceName: t.string().default(""),
71 },
72 engine: engine.mergeTree({
73 sortingKey: ["origin", "timestamp"],
74 partitionKey: "toYYYYMM(fromUnixTimestamp64Milli(timestamp))",
75 }),
76});
77
78export type AnalyticsEventsRow = InferRow<typeof analyticsEvents>;
79
80// ============================================================================
81// Endpoints
82// ============================================================================
83
84/**
85 * publication_traffic – daily pageview time series for a publication domain.
86 */
87export const publicationTraffic = defineEndpoint("publication_traffic", {
88 description: "Daily pageview time series for a publication domain",
89 params: {
90 domain: p.string(),
91 date_from: p.string().optional(),
92 date_to: p.string().optional(),
93 path: p.string().optional(),
94 },
95 nodes: [
96 node({
97 name: "endpoint",
98 sql: `
99 SELECT
100 toDate(fromUnixTimestamp64Milli(timestamp)) AS day,
101 count() AS pageviews,
102 uniq(deviceId) AS visitors
103 FROM analytics_events
104 WHERE eventType = 'pageview'
105 AND domain(origin) = {{String(domain)}}
106 {% if defined(date_from) %}
107 AND fromUnixTimestamp64Milli(timestamp) >= parseDateTimeBestEffort({{String(date_from)}})
108 {% end %}
109 {% if defined(date_to) %}
110 AND fromUnixTimestamp64Milli(timestamp) <= parseDateTimeBestEffort({{String(date_to)}})
111 {% end %}
112 {% if defined(path) %}
113 AND path = {{String(path)}}
114 {% end %}
115 GROUP BY day
116 ORDER BY day ASC
117 `,
118 }),
119 ],
120 output: {
121 day: t.date(),
122 pageviews: t.uint64(),
123 visitors: t.uint64(),
124 },
125});
126
127export type PublicationTrafficParams = InferParams<typeof publicationTraffic>;
128export type PublicationTrafficOutput = InferOutputRow<typeof publicationTraffic>;
129
130/**
131 * publication_top_referrers – top referring domains for a publication.
132 */
133export const publicationTopReferrers = defineEndpoint(
134 "publication_top_referrers",
135 {
136 description: "Top referrers for a publication domain",
137 params: {
138 domain: p.string(),
139 date_from: p.string().optional(),
140 date_to: p.string().optional(),
141 path: p.string().optional(),
142 limit: p.int32().optional(10),
143 },
144 nodes: [
145 node({
146 name: "endpoint",
147 sql: `
148 SELECT
149 domain(referrer) AS referrer_host,
150 count() AS pageviews
151 FROM analytics_events
152 WHERE eventType = 'pageview'
153 AND domain(origin) = {{String(domain)}}
154 AND referrer != ''
155 AND domain(referrer) != {{String(domain)}}
156 {% if defined(date_from) %}
157 AND fromUnixTimestamp64Milli(timestamp) >= parseDateTimeBestEffort({{String(date_from)}})
158 {% end %}
159 {% if defined(date_to) %}
160 AND fromUnixTimestamp64Milli(timestamp) <= parseDateTimeBestEffort({{String(date_to)}})
161 {% end %}
162 {% if defined(path) %}
163 AND path = {{String(path)}}
164 {% end %}
165 GROUP BY referrer_host
166 ORDER BY pageviews DESC
167 LIMIT {{Int32(limit, 10)}}
168 `,
169 }),
170 ],
171 output: {
172 referrer_host: t.string(),
173 pageviews: t.uint64(),
174 },
175 },
176);
177
178export type PublicationTopReferrersParams = InferParams<
179 typeof publicationTopReferrers
180>;
181export type PublicationTopReferrersOutput = InferOutputRow<
182 typeof publicationTopReferrers
183>;
184
185/**
186 * publication_top_pages – top pages by pageviews for a publication.
187 */
188export const publicationTopPages = defineEndpoint("publication_top_pages", {
189 description: "Top pages for a publication domain",
190 params: {
191 domain: p.string(),
192 date_from: p.string().optional(),
193 date_to: p.string().optional(),
194 limit: p.int32().optional(10),
195 },
196 nodes: [
197 node({
198 name: "endpoint",
199 sql: `
200 SELECT
201 path,
202 count() AS pageviews
203 FROM analytics_events
204 WHERE eventType = 'pageview'
205 AND domain(origin) = {{String(domain)}}
206 {% if defined(date_from) %}
207 AND fromUnixTimestamp64Milli(timestamp) >= parseDateTimeBestEffort({{String(date_from)}})
208 {% end %}
209 {% if defined(date_to) %}
210 AND fromUnixTimestamp64Milli(timestamp) <= parseDateTimeBestEffort({{String(date_to)}})
211 {% end %}
212 GROUP BY path
213 ORDER BY pageviews DESC
214 LIMIT {{Int32(limit, 10)}}
215 `,
216 }),
217 ],
218 output: {
219 path: t.string(),
220 pageviews: t.uint64(),
221 },
222});
223
224export type PublicationTopPagesParams = InferParams<typeof publicationTopPages>;
225export type PublicationTopPagesOutput = InferOutputRow<
226 typeof publicationTopPages
227>;
228
229// ============================================================================
230// Client
231// ============================================================================
232
233export const tinybird = new Tinybird({
234 datasources: { analyticsEvents },
235 pipes: { publicationTraffic, publicationTopReferrers, publicationTopPages },
236 devMode: false,
237});