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 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 | 487x 456x 456x 456x 456x 77x 77x 10x 10x 11x 58x 64x 12x 11x 11x 8x 7x 7x 7x 6x 6x 18x 18x 38x 10x 10x 10x 153x 153x 153x 6x 6x 6x 6x 6x 13x 12x 12x 12x 9x 8x 8x 8x 8x 8x 1x 1x 7x 14x 16x 12x 12x 821x | import type { Database } from "@/lib/db";
import type { Photo } from "@/types";
import { photosTable } from "@/lib/db/schema";
import { eq, and, isNull, isNotNull, asc, desc, inArray, sql } from "drizzle-orm";
import { generateId } from "@/lib/id";
export class PhotoRepository {
constructor(private db: Database) {}
async create(
// rotation is optional at the create boundary (DB default 0) so existing
// callers that don't set it keep compiling.
data: Omit<Photo, "id" | "createdAt" | "updatedAt" | "deletedAt" | "rotation"> & {
rotation?: number;
}
): Promise<Photo> {
const id = generateId("photo");
const now = new Date();
const [photo] = await this.db
.insert(photosTable)
.values({
id,
itemId: data.itemId,
collectionId: data.collectionId,
blobKey: data.blobKey,
filename: data.filename,
mimeType: data.mimeType,
fileSize: data.fileSize,
uploadedBy: data.uploadedBy,
isHero: data.isHero,
rotation: data.rotation ?? 0,
createdAt: now,
updatedAt: now,
deletedAt: null,
})
.returning();
return this.mapToPhoto(photo);
}
async findById(id: string): Promise<Photo | null> {
const photos = await this.db
.select()
.from(photosTable)
.where(and(eq(photosTable.id, id), isNull(photosTable.deletedAt)));
return photos.length > 0 ? this.mapToPhoto(photos[0]) : null;
}
async findByIds(collectionId: string, photoIds: string[]): Promise<Photo[]> {
Iif (photoIds.length === 0) return [];
const rows = await this.db
.select()
.from(photosTable)
.where(
and(
eq(photosTable.collectionId, collectionId),
inArray(photosTable.id, photoIds),
isNull(photosTable.deletedAt),
),
);
return rows.map((r) => this.mapToPhoto(r));
}
async findByItem(itemId: string): Promise<Photo[]> {
const photos = await this.db
.select()
.from(photosTable)
.where(and(eq(photosTable.itemId, itemId), isNull(photosTable.deletedAt)));
return photos.map((p) => this.mapToPhoto(p));
}
// Per-item cover for a batch of items: ONE row per item, chosen in SQL —
// hero first, else the earliest (created_at, then id) non-deleted photo.
// Items with no photos are absent from the map.
async findCoverPhotosByItemIds(itemIds: string[]): Promise<Map<string, Photo>> {
if (itemIds.length === 0) return new Map();
const rows = await this.db
.selectDistinctOn([photosTable.itemId])
.from(photosTable)
.where(and(inArray(photosTable.itemId, itemIds), isNull(photosTable.deletedAt)))
.orderBy(photosTable.itemId, desc(photosTable.isHero), asc(photosTable.createdAt), asc(photosTable.id));
return new Map(rows.map((r) => [r.itemId as string, this.mapToPhoto(r)]));
}
// Per-collection cover: ONE assigned (item_id IS NOT NULL) photo per collection,
// hero first, else earliest (created_at, then id). Pooled photos have no
// item-scoped asset URL and are excluded. NOT an access boundary: callers pass
// only access-filtered ids (see findCollectionIdsWithPooledPhotos).
async findCoverPhotosByCollectionIds(collectionIds: string[]): Promise<Map<string, Photo>> {
if (collectionIds.length === 0) return new Map();
const rows = await this.db
.selectDistinctOn([photosTable.collectionId])
.from(photosTable)
.where(
and(
inArray(photosTable.collectionId, collectionIds),
isNotNull(photosTable.itemId),
isNull(photosTable.deletedAt),
),
)
.orderBy(photosTable.collectionId, desc(photosTable.isHero), asc(photosTable.createdAt), asc(photosTable.id));
return new Map(rows.map((r) => [r.collectionId, this.mapToPhoto(r)]));
}
// Which of the GIVEN collections hold at least one pooled photo (item_id IS
// NULL, deleted_at IS NULL). Powers the /collections "Photos waiting" cue.
//
// BOUNDED by construction: SELECT DISTINCT collection_id returns at most one row
// per input id — never photo rows, no global pool, no unbounded scan. The
// existing photos_collection_id_idx backs the inArray filter.
//
// NOT an access boundary. Like findCoverPhotosByCollectionIds, the bare
// inArray trusts its input: callers MUST pass ONLY the authenticated user's
// already-access-filtered collection ids (the /collections page derives them
// from CollectionController.listCollections(userId)). Never hand it raw/client
// ids or it would expose other users' pool state.
async findCollectionIdsWithPooledPhotos(collectionIds: string[]): Promise<Set<string>> {
if (collectionIds.length === 0) return new Set();
const rows = await this.db
.selectDistinct({ collectionId: photosTable.collectionId })
.from(photosTable)
.where(
and(
inArray(photosTable.collectionId, collectionIds),
isNull(photosTable.itemId),
isNull(photosTable.deletedAt),
),
);
return new Set(rows.map((r) => r.collectionId));
}
// Loose (un-grouped) photos of a collection, oldest first. `limit` bounds the
// read at the SQL layer for the workspace preview (findPoolByCollection(id, 6));
// existing callers omit it and get the full pool. Order is created_at ASC, id ASC:
// `created_at` matches /pool's oldest-first order; `id` breaks created_at ties so a
// LIMIT cut is deterministic. Never order by updated_at — rotation bumps it.
//
// Index/EXPLAIN decision (verified on the test DB, 5000-row photos): NO new
// index is needed. For a selective collection the planner uses the existing
// photos_collection_id_idx (Index Scan, cost ~8). Worst case — one collection
// holding the whole table — it Seq Scans, but the LIMIT turns the sort into a
// bounded top-N heapsort (~26kB, cost ~223) that is roughly half the cost of
// today's *accepted* unbounded pool read (full quicksort, ~732kB, cost ~440).
// So the bounded preview is strictly cheaper than the read it slices.
async findPoolByCollection(collectionId: string, limit?: number): Promise<Photo[]> {
const base = this.db
.select()
.from(photosTable)
.where(
and(
eq(photosTable.collectionId, collectionId),
isNull(photosTable.itemId),
isNull(photosTable.deletedAt),
),
)
.orderBy(asc(photosTable.createdAt), asc(photosTable.id));
const photos = limit === undefined ? await base : await base.limit(limit);
return photos.map((p) => this.mapToPhoto(p));
}
async update(id: string, data: Partial<Omit<Photo, "id" | "createdAt">>): Promise<Photo> {
const now = new Date();
const [photo] = await this.db
.update(photosTable)
.set({
...data,
updatedAt: now,
})
.where(and(eq(photosTable.id, id), isNull(photosTable.deletedAt)))
.returning();
return this.mapToPhoto(photo);
}
async softDelete(id: string): Promise<Photo> {
const now = new Date();
const [photo] = await this.db
.update(photosTable)
.set({
deletedAt: now,
updatedAt: now,
})
.where(eq(photosTable.id, id))
.returning();
return this.mapToPhoto(photo);
}
async clearHeroPhotos(itemId: string): Promise<void> {
const now = new Date();
await this.db
.update(photosTable)
.set({
isHero: false,
updatedAt: now,
})
.where(and(eq(photosTable.itemId, itemId), eq(photosTable.isHero, true)));
}
async delete(id: string): Promise<void> {
await this.softDelete(id);
}
async setHero(photoId: string, itemId: string): Promise<Photo> {
await this.clearHeroPhotos(itemId);
return this.update(photoId, { isHero: true });
}
// Assigns the given pooled photos to an item and marks exactly one as hero,
// in a single UPDATE. The WHERE clause is the IDOR / double-assign guard: it
// matches only this collection's still-pooled, non-deleted rows, so a photo
// from another collection, an already-grouped photo, or a deleted one can
// never be captured. The caller compares the returned count to the requested
// count and rejects on a mismatch — a single guarded UPDATE needs no transaction.
async assignPooledPhotosToItem(
collectionId: string,
photoIds: string[],
itemId: string,
heroPhotoId: string,
): Promise<number> {
if (photoIds.length === 0) return 0;
const now = new Date();
const rows = await this.db
.update(photosTable)
.set({
itemId,
isHero: sql<boolean>`(${photosTable.id} = ${heroPhotoId})`,
updatedAt: now,
})
.where(
and(
eq(photosTable.collectionId, collectionId),
inArray(photosTable.id, photoIds),
isNull(photosTable.itemId),
isNull(photosTable.deletedAt),
),
)
.returning({ id: photosTable.id });
return rows.length;
}
// How many of these ids are currently available pooled photos of this
// collection (unassigned, not deleted). Used to reject a grouping conflict
// BEFORE any item is created or any photo is assigned — the create→assign path
// is deliberately non-transactional, so a pre-check keeps a conflict from leaking
// a half-assigned orphan item (partial failure is at worst a harmless empty item).
async countAvailablePooled(collectionId: string, photoIds: string[]): Promise<number> {
if (photoIds.length === 0) return 0;
const rows = await this.db
.select({ id: photosTable.id })
.from(photosTable)
.where(
and(
eq(photosTable.collectionId, collectionId),
inArray(photosTable.id, photoIds),
isNull(photosTable.itemId),
isNull(photosTable.deletedAt),
),
);
return rows.length;
}
// Reassign photos to a destination item (or NULL for the pool) and set hero:
// the heroPhotoId becomes is_hero=true, everything else moved is_hero=false.
// Used by the unified "Move to…" (move / split / return-to-pool).
async reassignPhotos(
collectionId: string,
photoIds: string[],
targetItemId: string | null,
heroPhotoId: string | null,
): Promise<number> {
Iif (photoIds.length === 0) return 0;
const rows = await this.db
.update(photosTable)
.set({
itemId: targetItemId,
isHero: heroPhotoId
? sql<boolean>`(${photosTable.id} = ${heroPhotoId})`
: sql<boolean>`false`,
updatedAt: new Date(),
})
.where(
and(
eq(photosTable.collectionId, collectionId),
inArray(photosTable.id, photoIds),
isNull(photosTable.deletedAt),
),
)
.returning({ id: photosTable.id });
return rows.length;
}
// Return every photo of an item to the pool (item_id NULL, hero cleared).
// Used to compensate a race-lost grouping so no photo is left pointing at a
// soft-deleted item.
async unassignAllFromItem(itemId: string): Promise<number> {
const rows = await this.db
.update(photosTable)
.set({ itemId: null, isHero: false, updatedAt: new Date() })
.where(and(eq(photosTable.itemId, itemId), isNull(photosTable.deletedAt)))
.returning({ id: photosTable.id });
return rows.length;
}
// Permanently DELETE every photo of an item (active AND soft-deleted — no
// deletedAt filter), returning each row's id + blob_key so the caller can purge
// the backing blobs AND that photo's resized variants (which are keyed by photo
// id). Purge-only; the item is being hard-deleted, so all its photos must go
// regardless of soft-delete state.
async hardDeleteByItem(itemId: string): Promise<{ id: string; blobKey: string }[]> {
return this.db
.delete(photosTable)
.where(eq(photosTable.itemId, itemId))
.returning({ id: photosTable.id, blobKey: photosTable.blobKey });
}
// Permanently DELETE every photo of the collection (active AND soft-deleted,
// assigned AND pooled — no deletedAt/itemId filter), returning each row's
// blob_key so the caller can purge the backing blobs. Purge-only.
async hardDeleteByCollection(collectionId: string): Promise<string[]> {
const rows = await this.db
.delete(photosTable)
.where(eq(photosTable.collectionId, collectionId))
.returning({ blobKey: photosTable.blobKey });
return rows.map((r) => r.blobKey);
}
// Scrub the uploader identifier (NOT delete the row). Used during account
// deletion for photos the user contributed into OTHER owners' collections:
// the photo is shared content that must survive, but its uploaded_by must no
// longer link back to the deleted user. Returns the affected-row count.
async scrubUploader(userId: string, tombstone: string): Promise<number> {
const rows = await this.db
.update(photosTable)
.set({ uploadedBy: tombstone, updatedAt: new Date() })
.where(eq(photosTable.uploadedBy, userId))
.returning({ id: photosTable.id });
return rows.length;
}
private mapToPhoto(row: typeof photosTable.$inferSelect): Photo {
return {
id: row.id,
itemId: row.itemId,
collectionId: row.collectionId,
blobKey: row.blobKey,
filename: row.filename,
mimeType: row.mimeType,
fileSize: row.fileSize,
uploadedBy: row.uploadedBy,
isHero: row.isHero,
rotation: row.rotation,
createdAt: row.createdAt,
updatedAt: row.updatedAt,
deletedAt: row.deletedAt,
};
}
}
|