Press n or j to go to the next uncovered block, b, p or k for the previous block.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 | 16x 4x 6x 6x 6x 6x 6x 6x 4x 4x 2x | import { and, eq, gte, sql } from "drizzle-orm";
import type { DrizzleD1Database } from "drizzle-orm/d1";
import type * as schema from "../db/schema";
import { cities, hoProViews, pros } from "../db/schema";
/**
* Homeowner pro-page view log. Powers the /account hub's activity bar
* ("you viewed N pros in {city} yesterday → send an inquiry").
*
* Append-only: every authenticated /pros/:slug view inserts a row via
* c.executionCtx.waitUntil so a transient D1 hiccup never breaks the page
* response. There's no UNIQUE constraint — read-time dedup with
* COUNT(DISTINCT pro_id) handles the "user opened 5 tabs" case without
* write-side complexity.
*/
export class HoProViewsDal {
constructor(private db: DrizzleD1Database<typeof schema>) {}
/**
* Record a view. Fire-and-forget at the route layer; never blocks the
* response.
*
* The day-bucket UNIQUE index `(user_id, pro_id, date(viewed_at))` from
* migration 0044 means `.onConflictDoNothing()` here is now load-bearing,
* not just defensive: it collapses 100 tabs of the same pro in one day
* into 1 row. Cross-day re-views still produce fresh rows (the bucket
* changes), preserving "you viewed this pro again today" signal.
*/
async recordView(userId: string, proId: string): Promise<void> {
await this.db
.insert(hoProViews)
.values({ userId, proId })
.onConflictDoNothing()
.run();
}
/**
* Find the city where this user has viewed the most distinct pros in
* the last `days` days. Returns null when fewer than `minDistinct`
* pros viewed (so the activity bar can fall through to the next rule
* cleanly).
*
* Joins to `pros` because the city we want to display is the pro's
* city, not anything stored on the view row itself. Single indexed
* range scan on `(user_id, viewed_at DESC)`.
*/
async recentByUserCity(
userId: string,
opts: { days?: number; minDistinct?: number } = {},
): Promise<{ cityId: string; cityName: string; count: number } | null> {
const days = opts.days ?? 7;
const minDistinct = opts.minDistinct ?? 2;
const cutoff = new Date(Date.now() - days * 24 * 60 * 60 * 1000);
const rows = await this.db
.select({
cityId: cities.id,
cityName: cities.name,
count: sql<number>`COUNT(DISTINCT ${hoProViews.proId})`,
})
.from(hoProViews)
.innerJoin(pros, eq(pros.id, hoProViews.proId))
.innerJoin(cities, eq(cities.id, pros.cityId))
.where(
and(
eq(hoProViews.userId, userId),
gte(hoProViews.viewedAt, cutoff),
),
)
.groupBy(cities.id, cities.name)
.orderBy(sql`COUNT(DISTINCT ${hoProViews.proId}) DESC`)
.limit(1)
.all();
const top = rows[0];
if (!top) return null;
const count = Number(top.count);
if (count < minDistinct) return null;
return { cityId: top.cityId, cityName: top.cityName, count };
}
}
|