Votes Schema Deep Dive

Overview

The votes system implements an upvote/downvote mechanism for items. Each user (identified by their client_profiles record) can cast exactly one vote per item, enforced by a unique composite index. Votes can be toggled between upvote and downvote.

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

---

Table: votes

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
userId	userid	text	No	-	FK -> client_profiles.id (CASCADE)
itemId	item_id	text	No	-	Item slug
voteType	vote_type	text (enum)	No	'upvote'	upvote, downvote
createdAt	created_at	timestamp	No	now()	-
updatedAt	updated_at	timestamp	No	now()	-

Column Name Note

The userId property maps to the database column userid (lowercase, no underscore). This is intentional -- it matches the migration schema. Do not confuse this with other tables where the column is userId or user_id.

Foreign Keys

Column	References	On Delete
userid	client_profiles.id	CASCADE

Indexes

Name	Columns	Type
unique_user_item_vote_idx	(userid, item_id)	Unique
item_votes_idx	item_id	B-tree
votes_created_at_idx	created_at	B-tree

Key Constraints

• One vote per user per item: The unique_user_item_vote_idx unique index on (userid, item_id) ensures each client profile can only have one vote record per item.
• Vote type is exclusive: A user either has an upvote or a downvote, never both.

---

Vote Type Enum

export const VoteType = {
    UPVOTE: 'upvote',
    DOWNVOTE: 'downvote'
} as const;

export type VoteTypeValues = (typeof VoteType)[keyof typeof VoteType];

---

TypeScript Types

export type Vote = typeof votes.$inferSelect;
export type InsertVote = typeof votes.$inferInsert;

---

Relations

// From relations.ts
export const votesRelations = relations(votes, ({ one }) => ({
    clientProfile: one(clientProfiles, {
        fields: [votes.userid],
        references: [clientProfiles.id]
    }),
}));

---

Relations Diagram

---

Vote Flow

---

Query Examples

Cast a vote (upsert pattern)

import { db } from '@/lib/db/drizzle';
import { votes, VoteType } from '@/lib/db/schema';
import { eq, and } from 'drizzle-orm';

// Insert or update vote using onConflict
await db
    .insert(votes)
    .values({
        userId: clientProfileId,
        itemId: 'my-item-slug',
        voteType: VoteType.UPVOTE,
    })
    .onConflictDoUpdate({
        target: [votes.userId, votes.itemId],
        set: {
            voteType: VoteType.UPVOTE,
            updatedAt: new Date(),
        },
    });

Remove a vote

await db
    .delete(votes)
    .where(
        and(
            eq(votes.userId, clientProfileId),
            eq(votes.itemId, 'my-item-slug')
        )
    );

Count votes for an item

import { sql } from 'drizzle-orm';

const voteCounts = await db
    .select({
        upvotes: sql<number>`count(*) filter (where ${votes.voteType} = 'upvote')`,
        downvotes: sql<number>`count(*) filter (where ${votes.voteType} = 'downvote')`,
    })
    .from(votes)
    .where(eq(votes.itemId, 'my-item-slug'));

Get user's vote on an item

const userVote = await db
    .select()
    .from(votes)
    .where(
        and(
            eq(votes.userId, clientProfileId),
            eq(votes.itemId, 'my-item-slug')
        )
    )
    .limit(1);

Get all votes by a user

const userVotes = await db
    .select()
    .from(votes)
    .where(eq(votes.userId, clientProfileId))
    .orderBy(desc(votes.createdAt));

Get most upvoted items

const topItems = await db
    .select({
        itemId: votes.itemId,
        upvotes: sql<number>`count(*)`,
    })
    .from(votes)
    .where(eq(votes.voteType, 'upvote'))
    .groupBy(votes.itemId)
    .orderBy(sql`count(*) desc`)
    .limit(10);

---

Design Notes

• Votes reference client profiles, not users. This is consistent with the comments table. Users must have a client_profiles record to vote.
• Items are identified by slug. The item_id column stores the item slug from the Git CMS, not a database foreign key.
• No separate vote count table. Vote counts are aggregated at query time using count(). This trades query cost for consistency (no stale counters).
• The updatedAt field tracks vote changes. When a user switches from upvote to downvote, updatedAt is updated while createdAt preserves the original vote time.