Comments Schema Deep Dive

Overview

The comments system enables users to leave feedback and reviews on items. Comments are linked to client_profiles (not directly to users), include a rating field, and support soft deletion via deletedAt. The moderation subsystem (reports and moderation_history) provides content reporting and administrative action tracking.

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

---

Table: comments

Stores user-submitted comments/reviews on items.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
content	content	text	No	-	Comment body
userId	userId	text	No	-	FK -> client_profiles.id (CASCADE)
itemId	itemId	text	No	-	Item slug
rating	rating	integer	No	0	Numeric rating
createdAt	created_at	timestamp (tz)	No	now()	-
updatedAt	updated_at	timestamp (tz)	No	now()	-
editedAt	edited_at	timestamp (tz)	Yes	-	When last edited
deletedAt	deleted_at	timestamp (tz)	Yes	-	Soft delete

Foreign Keys

Column	References	On Delete
userId	client_profiles.id	CASCADE

TypeScript Types

export type Comment = typeof comments.$inferSelect;
export type NewComment = typeof comments.$inferInsert;

Note on Foreign Key

Comments reference client_profiles.id, not users.id. This means the comment author must have a client profile created before they can post comments.

---

Moderation Tables

Table: reports

Content reporting system for both items and comments.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
contentType	content_type	text (enum)	No	-	item, comment
contentId	content_id	text	No	-	ID of reported content
reason	reason	text (enum)	No	-	See enum below
details	details	text	Yes	-	User-provided details
status	status	text (enum)	No	'pending'	See enum below
resolution	resolution	text (enum)	Yes	-	See enum below
reportedBy	reported_by	text	No	-	FK -> client_profiles.id (CASCADE)
reviewedBy	reviewed_by	text	Yes	-	FK -> users.id (SET NULL)
reviewNote	review_note	text	Yes	-	Admin review note
createdAt	created_at	timestamp	No	now()	-
updatedAt	updated_at	timestamp	No	now()	-
reviewedAt	reviewed_at	timestamp	Yes	-	-
resolvedAt	resolved_at	timestamp	Yes	-	-

Report Enums

export const ReportContentType = {
    ITEM: 'item',
    COMMENT: 'comment'
} as const;

export const ReportReason = {
    SPAM: 'spam',
    HARASSMENT: 'harassment',
    INAPPROPRIATE: 'inappropriate',
    OTHER: 'other'
} as const;

export const ReportStatus = {
    PENDING: 'pending',
    REVIEWED: 'reviewed',
    RESOLVED: 'resolved',
    DISMISSED: 'dismissed'
} as const;

export const ReportResolution = {
    CONTENT_REMOVED: 'content_removed',
    USER_WARNED: 'user_warned',
    USER_SUSPENDED: 'user_suspended',
    USER_BANNED: 'user_banned',
    NO_ACTION: 'no_action'
} as const;

Indexes

Name	Columns	Type
reports_content_type_idx	contentType	B-tree
reports_content_id_idx	contentId	B-tree
reports_status_idx	status	B-tree
reports_reported_by_idx	reportedBy	B-tree
reports_created_at_idx	createdAt	B-tree
reports_content_type_content_id_idx	(contentType, contentId)	Composite B-tree

TypeScript Types

export type Report = typeof reports.$inferSelect;
export type NewReport = typeof reports.$inferInsert;

---

Table: moderation_history

Tracks all moderation actions taken against users.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
userId	user_id	text	No	-	FK -> client_profiles.id (CASCADE)
action	action	text (enum)	No	-	See enum below
reason	reason	text	Yes	-	-
reportId	report_id	text	Yes	-	FK -> reports.id (SET NULL)
performedBy	performed_by	text	Yes	-	FK -> users.id (SET NULL)
contentType	content_type	text (enum)	Yes	-	item, comment
contentId	content_id	text	Yes	-	-
details	details	jsonb	Yes	-	Additional context
createdAt	created_at	timestamp	No	now()	-

Moderation Action Enum

export const ModerationAction = {
    WARN: 'warn',
    SUSPEND: 'suspend',
    BAN: 'ban',
    UNSUSPEND: 'unsuspend',
    UNBAN: 'unban',
    CONTENT_REMOVED: 'content_removed'
} as const;

Indexes

Name	Columns	Type
moderation_history_user_id_idx	userId	B-tree
moderation_history_action_idx	action	B-tree
moderation_history_report_id_idx	reportId	B-tree
moderation_history_performed_by_idx	performedBy	B-tree
moderation_history_created_at_idx	createdAt	B-tree

TypeScript Types

export type ModerationHistoryRecord = typeof moderationHistory.$inferSelect;
export type NewModerationHistoryRecord = typeof moderationHistory.$inferInsert;

---

Relations Diagram

---

Report Lifecycle

---

Query Examples

Get comments for an item

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

const itemComments = await db
    .select()
    .from(comments)
    .where(
        and(
            eq(comments.itemId, 'my-item-slug'),
            isNull(comments.deletedAt)
        )
    )
    .orderBy(desc(comments.createdAt));

Create a comment

await db.insert(comments).values({
    content: 'Great tool, highly recommended!',
    userId: clientProfileId, // Note: client_profiles.id, NOT users.id
    itemId: 'my-item-slug',
    rating: 5,
});

Soft delete a comment

await db
    .update(comments)
    .set({ deletedAt: new Date() })
    .where(eq(comments.id, commentId));

Submit a report

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

await db.insert(reports).values({
    contentType: 'comment',
    contentId: commentId,
    reason: 'spam',
    details: 'This comment appears to be promotional spam.',
    reportedBy: clientProfileId,
});

Get pending reports

const pendingReports = await db
    .select()
    .from(reports)
    .where(eq(reports.status, 'pending'))
    .orderBy(desc(reports.createdAt));

Record a moderation action

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

await db.insert(moderationHistory).values({
    userId: targetClientProfileId,
    action: 'warn',
    reason: 'Posting spam content',
    reportId: reportId,
    performedBy: adminUserId,
    contentType: 'comment',
    contentId: commentId,
});