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