Categories & Companies Schema Deep Dive

Overview

In the Ever Works Template, categories are defined in the Git-based CMS (content repository), not in the database. There is no categories database table. However, the database provides infrastructure to link items to companies and track company hierarchies, which serves a similar organizational purpose.

This page documents the companies table, the items_companies junction table, and how category/company references appear throughout the schema.

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

---

Table: companies

Stores company/organization records that can be linked to items.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
name	name	text	No	-	-
website	website	text	Yes	-	-
domain	domain	text	Yes	-	Unique
slug	slug	text	Yes	-	Unique
status	status	text (enum)	No	'active'	active, inactive
createdAt	created_at	timestamp	No	now()	-
updatedAt	updated_at	timestamp	No	now()	-

Indexes

Name	Columns	Type
companies_name_idx	name	B-tree
companies_status_idx	status	B-tree
companies_domain_unique_idx	domain	Unique
companies_slug_unique_idx	slug	Unique

TypeScript Types

export type Company = typeof companies.$inferSelect;
export type NewCompany = typeof companies.$inferInsert;

---

Table: items_companies

Junction table linking item slugs to company records. Each item slug can only be associated with one company (unique constraint on item_slug).

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

TypeScript Types

export type ItemCompany = typeof itemsCompanies.$inferSelect;
export type NewItemCompany = typeof itemsCompanies.$inferInsert;

---

Category References in Other Tables

While categories do not have a dedicated table, category data appears as denormalized fields in several tables:

Table	Column	Purpose
favorites	item_category	Cached category name for display
featured_items	item_category	Cached category name for display

These fields store the category string at the time the record is created, avoiding the need to look up the Git CMS at read time.

---

Relations Diagram

---

How Categories Work

1. Content repository defines categories. The .content/ directory (cloned from DATA_REPOSITORY) contains category definitions in markdown/YAML files.
2. Items belong to categories in Git. Each item's frontmatter specifies its category.
3. Database stores category strings. When favorites or featured items are created, the category name is copied from the content layer into the database as a denormalized field.
4. Companies provide organizational grouping. The companies + items_companies tables allow linking items to real-world organizations, separate from content categories.

---

Query Examples

Get all active companies

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

const activeCompanies = await db
    .select()
    .from(companies)
    .where(eq(companies.status, 'active'));

Find company by domain

const company = await db
    .select()
    .from(companies)
    .where(eq(companies.domain, 'example.com'))
    .limit(1);

Get items for a company

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

const companyItems = await db
    .select()
    .from(itemsCompanies)
    .innerJoin(companies, eq(itemsCompanies.companyId, companies.id))
    .where(eq(companies.slug, 'acme-corp'));

Link an item to a company

await db.insert(itemsCompanies).values({
    itemSlug: 'my-tool-slug',
    companyId: company.id,
});

---

Design Notes

• One item, one company. The unique constraint on item_slug in items_companies means each item can only belong to one company.
• Companies have unique domains and slugs. Both domain and slug have unique indexes for fast lookups and URL routing.
• Category data is read from Git at runtime. The database does not need to store category hierarchies or metadata -- this comes from the content layer.