Items Schema Deep Dive

Overview

In the Ever Works Template, items are stored in a Git-based CMS (.content/ directory), not in a traditional database table. However, multiple database tables support item-related operations such as tracking views, auditing changes, indexing locations, managing favorites, featuring items, and linking items to companies.

This page documents every database table that references or supports items.

Source file: template/lib/db/schema.ts

---

Item-Supporting Tables

Table	Purpose
favorites	User-saved favorite items
featured_items	Admin-curated featured items
item_views	Per-day unique view tracking
item_audit_logs	Complete change history for admin panel
item_location_index	Geospatial index for "Near Me" filtering
items_companies	Links items to company records
location_index_meta	Singleton metadata for location index

---

Table: favorites

Stores user bookmark/favorite relationships to items, identified by slug.

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	-	-
itemName	item_name	text	No	-	-
itemIconUrl	item_icon_url	text	Yes	-	-
itemCategory	item_category	text	Yes	-	-
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

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 list of items to highlight on the site. Supports ordering and optional expiration.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
itemSlug	item_slug	text	No	-	-
itemName	item_name	text	No	-	-
itemIconUrl	item_icon_url	text	Yes	-	-
itemCategory	item_category	text	Yes	-	-
itemDescription	item_description	text	Yes	-	-
featuredOrder	featured_order	integer	No	0	Display ordering
featuredUntil	featured_until	timestamp	Yes	-	Optional expiration
isActive	is_active	boolean	No	true	-
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;

---

Table: item_views

Tracks unique daily views per item. Uses cookie-based anonymous viewer identification and UTC date deduplication. Does not store IP addresses for privacy.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
itemId	item_id	text	No	-	Item slug
viewerId	viewer_id	text	No	-	Anonymous cookie ID
viewedDateUtc	viewed_date_utc	text	No	-	YYYY-MM-DD format
viewedAt	viewed_at	timestamp (tz)	No	now()	Precise view time

Indexes

Name	Columns	Type
item_views_unique_daily_idx	(itemId, viewerId, viewedDateUtc)	Unique
item_views_item_date_idx	(itemId, viewedDateUtc)	Composite B-tree

TypeScript Types

export type ItemView = typeof itemViews.$inferSelect;
export type NewItemView = typeof itemViews.$inferInsert;

---

Table: item_audit_logs

Stores the complete change history for items managed through the admin panel. Since items live in Git, itemId is the slug (not a foreign key).

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
itemId	item_id	text	No	-	Item slug
itemName	item_name	text	No	-	Denormalized
action	action	text (enum)	No	-	See enum values below
previousStatus	previous_status	text	Yes	-	For status changes
newStatus	new_status	text	Yes	-	For status changes
changes	changes	jsonb	Yes	-	{ field: { old, new } }
performedBy	performed_by	text	Yes	-	FK -> users.id (SET NULL)
performedByName	performed_by_name	text	Yes	-	Denormalized
notes	notes	text	Yes	-	Review notes
metadata	metadata	jsonb	Yes	-	IP, user agent, etc.
createdAt	created_at	timestamp (tz)	No	now()	-

Action Enum Values

export const ItemAuditAction = {
    CREATED: 'created',
    UPDATED: 'updated',
    STATUS_CHANGED: 'status_changed',
    REVIEWED: 'reviewed',
    DELETED: 'deleted',
    RESTORED: 'restored'
} as const;

Indexes

Name	Columns	Type
item_audit_logs_item_id_idx	itemId	B-tree
item_audit_logs_action_idx	action	B-tree
item_audit_logs_performed_by_idx	performedBy	B-tree
item_audit_logs_created_at_idx	createdAt	B-tree
item_audit_logs_item_id_action_idx	(itemId, action)	Composite B-tree

TypeScript Types

export type ItemAuditLog = typeof itemAuditLogs.$inferSelect;
export type NewItemAuditLog = typeof itemAuditLogs.$inferInsert;
export type ItemAuditChanges = Record<string, { old: unknown; new: unknown }>;

---

Table: item_location_index

Geospatial index for items, enabling "Near Me" filtering and distance-based sorting. This is an index-only table -- the source of truth remains in the Git CMS.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
itemSlug	item_slug	text	No	-	Primary Key
latitude	latitude	doublePrecision	No	-	-
longitude	longitude	doublePrecision	No	-	-
address	address	text	Yes	-	-
city	city	text	Yes	-	-
state	state	text	Yes	-	-
country	country	text	Yes	-	-
cityNormalized	city_normalized	text	Yes	-	Lowercase, trimmed
countryNormalized	country_normalized	text	Yes	-	Lowercase, trimmed
postalCode	postal_code	text	Yes	-	-
serviceArea	service_area	text	Yes	-	-
isRemote	is_remote	boolean	No	false	-
indexedAt	indexed_at	timestamp (tz)	No	now()	-

Indexes

Name	Columns	Type
item_location_index_latitude_idx	latitude	B-tree
item_location_index_longitude_idx	longitude	B-tree
item_location_index_city_idx	city	B-tree
item_location_index_country_idx	country	B-tree
item_location_index_city_normalized_idx	cityNormalized	B-tree
item_location_index_country_normalized_idx	countryNormalized	B-tree
item_location_index_is_remote_idx	isRemote	B-tree
item_location_index_indexed_at_idx	indexedAt	B-tree
item_location_index_lat_long_idx	(latitude, longitude)	Composite B-tree

TypeScript Types

export type ItemLocationIndex = typeof itemLocationIndex.$inferSelect;
export type NewItemLocationIndex = typeof itemLocationIndex.$inferInsert;

---

Table: items_companies

Links item slugs to company database records.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
itemSlug	item_slug	text	No	-	Unique
companyId	company_id	text	No	-	FK -> companies.id (CASCADE)
createdAt	created_at	timestamp (tz)	No	now()	-
updatedAt	updated_at	timestamp (tz)	No	now()	-

Indexes

Name	Columns	Type
items_companies_company_id_idx	companyId	B-tree

---

Table: location_index_meta

Singleton table tracking location index rebuild metadata across deployments.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	'singleton'	Primary Key
lastRebuildAt	last_rebuild_at	timestamp (tz)	Yes	-	-
lastRebuildDurationMs	last_rebuild_duration_ms	integer	Yes	-	-
lastRebuildItemCount	last_rebuild_item_count	integer	Yes	-	-
updatedAt	updated_at	timestamp (tz)	No	now()	-

Indexes

Name	Columns	Type
location_index_meta_singleton_idx	id	Unique

---

Relations Diagram

---

Query Examples

Fetch user favorites

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

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

Record an item view

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

await db.insert(itemViews).values({
    itemId: 'my-item-slug',
    viewerId: cookieViewerId,
    viewedDateUtc: '2025-01-15',
}).onConflictDoNothing();

Get active featured items

import { featuredItems } from '@/lib/db/schema';
import { eq, asc, or, isNull, gte } from 'drizzle-orm';

const featured = await db
    .select()
    .from(featuredItems)
    .where(eq(featuredItems.isActive, true))
    .orderBy(asc(featuredItems.featuredOrder));

Find items near a location (bounding box)

import { itemLocationIndex } from '@/lib/db/schema';
import { and, between } from 'drizzle-orm';

const nearby = await db
    .select()
    .from(itemLocationIndex)
    .where(
        and(
            between(itemLocationIndex.latitude, minLat, maxLat),
            between(itemLocationIndex.longitude, minLng, maxLng)
        )
    );

Get audit history for an item

import { itemAuditLogs } from '@/lib/db/schema';
import { eq, desc } from 'drizzle-orm';

const history = await db
    .select()
    .from(itemAuditLogs)
    .where(eq(itemAuditLogs.itemId, 'my-item-slug'))
    .orderBy(desc(itemAuditLogs.createdAt));

---

Design Notes

• Items are NOT in the database. They live in a Git-based CMS cloned into .content/. The database only stores metadata, indexes, and relationships.
• Item identification is by slug. All item-supporting tables reference items via item_slug or item_id (which IS the slug), not via foreign keys.
• Denormalization is intentional. Tables like favorites and featured_items store item_name and item_icon_url to avoid cross-system lookups at read time.
• Privacy-first views. The item_views table uses anonymous cookie IDs and does not store IP addresses.