Favorites & Collections Schema Deep Dive

Overview

The Ever Works Template implements a favorites system that serves as the user collection mechanism. There is no separate collections table -- user curation of items is handled through the favorites table, which stores user-saved items with denormalized metadata for efficient display. For admin-curated collections, the featured_items table provides a managed set of highlighted items.

Source file: template/lib/db/schema.ts Relations file: template/lib/db/migrations/relations.ts

---

Table: favorites

User-created bookmark/collection of items. Each user can save items to their personal favorites list.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
userId	userId	text	No	-	FK -> users.id (CASCADE)
itemSlug	item_slug	text	No	-	Item identifier
itemName	item_name	text	No	-	Denormalized display name
itemIconUrl	item_icon_url	text	Yes	-	Denormalized icon URL
itemCategory	item_category	text	Yes	-	Denormalized category
createdAt	created_at	timestamp	No	now()	-
updatedAt	updated_at	timestamp	No	now()	-

Indexes

Name	Columns	Type
user_item_favorite_unique_idx	(userId, itemSlug)	Unique
favorites_user_id_idx	userId	B-tree
favorites_item_slug_idx	itemSlug	B-tree
favorites_created_at_idx	createdAt	B-tree

Key Constraints

• One favorite per user per item: The unique composite index user_item_favorite_unique_idx on (userId, itemSlug) prevents duplicate favorites.
• Cascade deletion: When a user is deleted, all their favorites are automatically removed.

TypeScript Types

export type Favorite = typeof favorites.$inferSelect;
export type NewFavorite = typeof favorites.$inferInsert;
export type FavoriteWithUser = Favorite & {
    user: typeof users.$inferSelect;
};

---

Table: featured_items

Admin-curated collection of highlighted items. Supports ordering, activation/deactivation, and optional time-based expiration.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
itemSlug	item_slug	text	No	-	Item identifier
itemName	item_name	text	No	-	Denormalized
itemIconUrl	item_icon_url	text	Yes	-	Denormalized
itemCategory	item_category	text	Yes	-	Denormalized
itemDescription	item_description	text	Yes	-	Denormalized
featuredOrder	featured_order	integer	No	0	Sort order
featuredUntil	featured_until	timestamp	Yes	-	Auto-expire date
isActive	is_active	boolean	No	true	Active toggle
featuredBy	featured_by	text	No	-	Admin user ID
featuredAt	featured_at	timestamp	No	now()	-
createdAt	created_at	timestamp	No	now()	-
updatedAt	updated_at	timestamp	No	now()	-

Indexes

Name	Columns	Type
featured_items_item_slug_idx	itemSlug	B-tree
featured_items_featured_order_idx	featuredOrder	B-tree
featured_items_is_active_idx	isActive	B-tree
featured_items_featured_at_idx	featuredAt	B-tree
featured_items_featured_until_idx	featuredUntil	B-tree

TypeScript Types

export type FeaturedItem = typeof featuredItems.$inferSelect;
export type NewFeaturedItem = typeof featuredItems.$inferInsert;

---

Relations

// From relations.ts
export const favoritesRelations = relations(favorites, ({ one }) => ({
    user: one(users, {
        fields: [favorites.userId],
        references: [users.id]
    }),
}));

---

Relations Diagram

---

Favorites vs. Featured Items

Aspect	favorites	featured_items
Created by	End users	Admin users
Per-user	Yes (user-scoped)	No (global)
Ordering	By createdAt	By featuredOrder
Expiration	None	Optional featuredUntil
Active toggle	No (exists = active)	Yes (isActive flag)
Foreign key	users.id	None (stores admin ID as text)

---

Query Examples

Add item to favorites

import { db } from '@/lib/db/drizzle';
import { favorites } from '@/lib/db/schema';

await db.insert(favorites).values({
    userId,
    itemSlug: 'my-tool-slug',
    itemName: 'My Tool',
    itemIconUrl: '/icons/my-tool.png',
    itemCategory: 'Productivity',
}).onConflictDoNothing(); // Prevent duplicates

Remove item from favorites

import { eq, and } from 'drizzle-orm';

await db
    .delete(favorites)
    .where(
        and(
            eq(favorites.userId, userId),
            eq(favorites.itemSlug, 'my-tool-slug')
        )
    );

Check if item is favorited

const isFavorited = await db
    .select({ id: favorites.id })
    .from(favorites)
    .where(
        and(
            eq(favorites.userId, userId),
            eq(favorites.itemSlug, 'my-tool-slug')
        )
    )
    .limit(1);

Get user's favorites list

const userFavorites = await db
    .select()
    .from(favorites)
    .where(eq(favorites.userId, userId))
    .orderBy(desc(favorites.createdAt));

Get most favorited items

import { sql } from 'drizzle-orm';

const popular = await db
    .select({
        itemSlug: favorites.itemSlug,
        itemName: favorites.itemName,
        count: sql<number>`count(*)`,
    })
    .from(favorites)
    .groupBy(favorites.itemSlug, favorites.itemName)
    .orderBy(sql`count(*) desc`)
    .limit(10);

Add a featured item

import { featuredItems } from '@/lib/db/schema';

await db.insert(featuredItems).values({
    itemSlug: 'premium-tool',
    itemName: 'Premium Tool',
    itemCategory: 'Productivity',
    featuredOrder: 1,
    isActive: true,
    featuredBy: adminUserId,
    featuredUntil: new Date('2025-12-31'),
});

Get active featured items (not expired)

import { or, isNull, gte } from 'drizzle-orm';

const activeFeatured = await db
    .select()
    .from(featuredItems)
    .where(
        and(
            eq(featuredItems.isActive, true),
            or(
                isNull(featuredItems.featuredUntil),
                gte(featuredItems.featuredUntil, new Date())
            )
        )
    )
    .orderBy(asc(featuredItems.featuredOrder));

---

Design Notes

• Denormalized item data. Both tables store itemName, itemIconUrl, and itemCategory directly rather than looking up the Git CMS at read time. This makes list queries fast but means data can become stale if items are renamed.
• No collection grouping. Unlike a full "collection" system with folders/lists, favorites is a flat list per user. Items can be filtered by itemCategory for grouping.
• Featured items are global. They appear the same for all users, unlike favorites which are per-user.