Skip to main content

Collection (Company) Queries Deep Dive

Comprehensive reference for all company/collection CRUD operations, item-company associations, search, pagination, and statistics query functions.

Overview​

The collection query layer manages companies and their associations with items:

  • company.queries.ts -- Full company CRUD, search with pagination, item-company linking (1:1 relationship), statistics, and item listing by company

Companies in this system represent the organizations behind listed items. Each item can belong to at most one company (enforced by a unique constraint on itemSlug).

Source File​

lib/db/queries/company.queries.ts

Function Reference​

Company CRUD​

createCompany​

Creates a new company with normalized domain and slug fields.

async function createCompany(data: {
  name: string;
  website?: string;
  domain?: string;
  slug?: string;
  status?: 'active' | 'inactive';
}): Promise<Company>

Parameters:

ParameterTypeRequiredDefaultDescription
namestringYes--Company name
websitestringNo--Company website URL
domainstringNo--Company domain
slugstringNo--URL-safe identifier
status'active' | 'inactive'No'active'Company status

Normalization: domain and slug are automatically lowercased and trimmed. name is trimmed.

getCompanyById​

async function getCompanyById(id: string): Promise<Company | null>

getCompanyBySlug​

Gets a company by slug using case-insensitive matching.

async function getCompanyBySlug(slug: string): Promise<Company | null>

SQL Pattern:

SELECT * FROM companies WHERE lower(slug) = ? LIMIT 1;

getCompanyByDomain​

Gets a company by domain using case-insensitive matching.

async function getCompanyByDomain(domain: string): Promise<Company | null>

getCompanyByName​

Gets a company by exact name (case-insensitive).

async function getCompanyByName(name: string): Promise<Company | null>

updateCompany​

Updates company fields. Normalizes domain and slug if provided.

async function updateCompany(
  id: string,
  data: Partial<Omit<NewCompany, 'id'>>
): Promise<Company | null>

Note: Removes undefined values from the update payload to avoid overwriting fields with null.

deleteCompany​

Permanently deletes a company.

async function deleteCompany(id: string): Promise<boolean>

Returns: true if a record was deleted, false if company not found.

listCompanies​

Lists companies with pagination, search, filtering, and sorting.

async function listCompanies(params: {
  page?: number;
  limit?: number;
  search?: string;
  status?: 'active' | 'inactive';
  sortBy?: 'name' | 'createdAt' | 'updatedAt';
  sortOrder?: 'asc' | 'desc';
}): Promise<{
  companies: Company[];
  total: number;
  page: number;
  totalPages: number;
  limit: number;
  activeCount: number;
  inactiveCount: number;
}>

Search behavior: Uses ILIKE on both name and domain fields with proper escaping.

Additional data: Returns global activeCount and inactiveCount regardless of filters applied.

getCompaniesStats​

Gets company count statistics by status.

async function getCompaniesStats(): Promise<{
  total: number;
  active: number;
  inactive: number;
}>

getCompaniesWithItemCount​

Lists companies with the count of items associated with each, supporting sorting by item count.

async function getCompaniesWithItemCount(params: {
  page?: number;
  limit?: number;
  search?: string;
  status?: 'active' | 'inactive';
  sortBy?: 'name' | 'createdAt' | 'itemCount';
  sortOrder?: 'asc' | 'desc';
}): Promise<{
  companies: (Company & { itemCount: number })[];
  total: number;
  page: number;
  totalPages: number;
  limit: number;
}>

SQL Pattern:

SELECT companies.*, count(items_companies.item_slug) as item_count
FROM companies
LEFT JOIN items_companies ON companies.id = items_companies.company_id
WHERE ...
GROUP BY companies.id
ORDER BY item_count DESC
LIMIT ? OFFSET ?;

Item-Company Association​

linkItemToCompany​

Links an item to a company. Idempotent -- returns existing association if unchanged, updates if company changed, or creates new.

async function linkItemToCompany(
  itemSlug: string,
  companyId: string
): Promise<{
  association: ItemCompany;
  created: boolean;
  updated: boolean;
}>

Parameters:

ParameterTypeRequiredDescription
itemSlugstringYesItem slug (normalized to lowercase)
companyIdstringYesCompany ID

Behavior:

  1. Validates company exists (throws if not)
  2. Checks for existing association
  3. If same company: returns existing (no-op), created: false, updated: false
  4. If different company: updates association, created: false, updated: true
  5. If no association: creates new, created: true, updated: false

Error Handling:

  • Throws with friendly message if company does not exist
  • Handles unique constraint violations gracefully
  • Handles foreign key constraint errors

assignCompanyToItem​

Backward-compatible alias for linkItemToCompany. Returns only the association object.

async function assignCompanyToItem(
  itemSlug: string,
  companyId: string
): Promise<ItemCompany>

updateItemCompany​

Updates an existing item-company association to point to a different company.

async function updateItemCompany(
  itemSlug: string,
  companyId: string
): Promise<ItemCompany | null>

unlinkItemFromCompany​

Removes item-company association. Idempotent -- unlinking a non-existent mapping is a no-op.

async function unlinkItemFromCompany(
  itemSlug: string
): Promise<{ success: boolean; deleted: boolean }>

Returns:

  • success: Always true (idempotent)
  • deleted: true if an association was actually removed

removeCompanyFromItem​

Backward-compatible alias for unlinkItemFromCompany. Returns boolean.

async function removeCompanyFromItem(itemSlug: string): Promise<boolean>

getCompanyByItemSlug​

Gets the company associated with an item.

async function getCompanyByItemSlug(itemSlug: string): Promise<Company | null>

SQL Pattern:

SELECT companies.* FROM items_companies
INNER JOIN companies ON items_companies.company_id = companies.id
WHERE items_companies.item_slug = ?
LIMIT 1;

getCompanyForItem​

Backward-compatible alias for getCompanyByItemSlug.

itemHasCompany​

Checks if an item has a company assigned.

async function itemHasCompany(itemSlug: string): Promise<boolean>

listItemsByCompany​

Lists items associated with a company, with pagination.

async function listItemsByCompany(
  companyId: string,
  params?: { page?: number; limit?: number }
): Promise<{
  items: ItemCompany[];
  total: number;
  page: number;
  totalPages: number;
  limit: number;
}>

Default limit: 50 items per page.

getItemsForCompany​

Backward-compatible alias for listItemsByCompany.

Performance Notes​

  1. Case-insensitive lookups -- getCompanyBySlug, getCompanyByDomain, and getCompanyByName use lower() SQL function for case-insensitive matching. For high-volume lookups, consider adding a functional index on lower(slug).

  2. Idempotent operations -- linkItemToCompany and unlinkItemFromCompany are designed for safe retry behavior, returning consistent results regardless of how many times they are called.

  3. Search escaping -- All ILIKE search patterns properly escape SQL wildcards (%, _, \).

  4. LEFT JOIN for item counts -- getCompaniesWithItemCount uses LEFT JOIN to include companies with zero items in results.

  5. Slug normalization -- All item slug parameters are lowercased and trimmed before database operations.

Usage Examples​

Assigning a company to an item​

import { linkItemToCompany, getCompanyByDomain } from '@/lib/db/queries';

const company = await getCompanyByDomain('toggl.com');
if (company) {
  const result = await linkItemToCompany('toggl-track', company.id);
  if (result.created) {
    console.log('New association created');
  } else if (result.updated) {
    console.log('Company updated for this item');
  }
}

Listing companies with item counts​

import { getCompaniesWithItemCount } from '@/lib/db/queries';

const result = await getCompaniesWithItemCount({
  page: 1,
  limit: 20,
  sortBy: 'itemCount',
  sortOrder: 'desc',
  status: 'active',
});

result.companies.forEach(c => {
  console.log(`${c.name}: ${c.itemCount} items`);
});