nandi.uk
This repository has no description
2.3 kB
77 lines
1import {
2 pgTable,
3 text,
4 timestamp,
5 uuid,
6 boolean,
7 integer,
8 index,
9} from 'drizzle-orm/pg-core';
10import { publishedRecords } from './publishedRecord.sql';
11import { cards } from './card.sql';
12
13export const collections = pgTable(
14 'collections',
15 {
16 id: uuid('id').primaryKey(),
17 authorId: text('author_id').notNull(),
18 name: text('name').notNull(),
19 description: text('description'),
20 accessType: text('access_type').notNull(), // OPEN, CLOSED
21 cardCount: integer('card_count').notNull().default(0),
22 createdAt: timestamp('created_at').notNull().defaultNow(),
23 updatedAt: timestamp('updated_at').notNull().defaultNow(),
24 publishedRecordId: uuid('published_record_id').references(
25 () => publishedRecords.id,
26 ),
27 },
28 (table) => {
29 return {
30 // Critical for all collection queries by user
31 authorIdIdx: index('collections_author_id_idx').on(table.authorId),
32
33 // For paginated collection listings (most common sort)
34 authorUpdatedAtIdx: index('collections_author_updated_at_idx').on(
35 table.authorId,
36 table.updatedAt,
37 ),
38 };
39 },
40);
41
42// Join table for collection collaborators
43export const collectionCollaborators = pgTable('collection_collaborators', {
44 id: uuid('id').primaryKey(),
45 collectionId: uuid('collection_id')
46 .notNull()
47 .references(() => collections.id, { onDelete: 'cascade' }),
48 collaboratorId: text('collaborator_id').notNull(),
49});
50
51// Join table for cards in collections
52export const collectionCards = pgTable(
53 'collection_cards',
54 {
55 id: uuid('id').primaryKey(),
56 collectionId: uuid('collection_id')
57 .notNull()
58 .references(() => collections.id, { onDelete: 'cascade' }),
59 cardId: uuid('card_id')
60 .notNull()
61 .references(() => cards.id, { onDelete: 'cascade' }),
62 addedBy: text('added_by').notNull(),
63 addedAt: timestamp('added_at').notNull().defaultNow(),
64 publishedRecordId: uuid('published_record_id').references(
65 () => publishedRecords.id,
66 ),
67 },
68 (table) => {
69 return {
70 // Critical for getCollectionsContainingCardForUser queries
71 cardIdIdx: index('collection_cards_card_id_idx').on(table.cardId),
72 collectionIdIdx: index('collection_cards_collection_id_idx').on(
73 table.collectionId,
74 ),
75 };
76 },
77);