food-database

# food-database Skill

Patterns for building and querying the local food database in nutrition-tracker. Covers search implementation, Zod schemas, macro computation, and Chart.js macro ring configuration.

## When to use

Use this skill when:
- Implementing or extending food CRUD routes
- Writing Zod validation schemas for nutrition fields
- Building the food search query
- Implementing macro computation from diary entries
- Configuring the MacroRing (Chart.js doughnut) component
- Computing calorie estimates from macros

---

## Food schema (Zod)

```typescript
import { z } from 'zod';

const nonNeg = z.number().min(0);

export const CreateFoodSchema = z.object({
  name:         z.string().min(1).max(200),
  brand:        z.string().max(100).optional(),
  serving_size: z.number().positive(),
  serving_unit: z.string().min(1).max(20).default('g'),
  calories:     nonNeg,
  protein_g:    nonNeg,
  carbs_g:      nonNeg,
  fat_g:        nonNeg,
  fiber_g:      nonNeg.default(0),
  sugar_g:      nonNeg.default(0),
  sodium_mg:    nonNeg.default(0),
});

export const UpdateFoodSchema = CreateFoodSchema.partial().refine(
  (data) => Object.keys(data).length > 0,
  { message: 'At least one field must be provided' }
);

export type CreateFoodInput = z.infer<typeof CreateFoodSchema>;
```

---

## Food search query

Substring match on `name`, case-insensitive via SQLite `LIKE`:

```typescript
// src/server/routes/foods.ts
import type { Request, Response } from 'express';
import { getDb } from '../db.js';

export async function searchFoods(req: Request, res: Response): Promise<void> {
  const q = typeof req.query['q'] === 'string' ? req.query['q'].trim() : '';
  const limit = Math.min(Number(req.query['limit']) || 20, 100);

  const db = getDb();
  const rows = db.prepare(`
    SELECT id, name, brand, serving_size, serving_unit,
           calories, protein_g, carbs_g, fat_g, fiber_g
    FROM foods
    WHERE name LIKE ?
    ORDER BY name ASC
    LIMIT ?
  `).all(`%${q}%`, limit);

  res.json(rows);
}
```

Never use string concatenation in SQL. The `%${q}%` is a parameterized value passed as a separate argument to `.all()`, not interpolated into the SQL string.

---

## Macro computation in SQL

Diary entries do not store macro values. They are always computed by joining with the foods table:

```typescript
// src/server/routes/diary.ts
export function getDiarySummary(date: string): DiarySum {
  const db = getDb();
  const row = db.prepare(`
    SELECT
      COALESCE(SUM(f.calories * e.quantity), 0)  AS calories,
      COALESCE(SUM(f.protein_g * e.quantity), 0) AS protein_g,
      COALESCE(SUM(f.carbs_g * e.quantity), 0)   AS carbs_g,
      COALESCE(SUM(f.fat_g * e.quantity), 0)     AS fat_g,
      COALESCE(SUM(f.fiber_g * e.quantity), 0)   AS fiber_g,
      COUNT(e.id)                                 AS entry_count
    FROM diary_entries e
    JOIN foods f ON f.id = e.food_id
    WHERE e.log_date = ?
  `).get(date) as DiarySum;

  return { date, ...row };
}
```

`quantity` is a multiplier of `serving_size`. A quantity of `0.8` with a serving_size of `100g` means the user consumed `80g` of that food.

---

## Calorie estimate from macros

The 4-4-9 rule. Use this to pre-fill or cross-check the calorie field:

```typescript
export function estimateCalories(protein_g: number, carbs_g: number, fat_g: number): number {
  return Math.round(protein_g * 4 + carbs_g * 4 + fat_g * 9);
}
```

Display this as a suggestion. Always accept the user-entered value from a nutrition label as the authoritative figure.

---

## MacroRing component (Chart.js doughnut)

```typescript
// src/client/components/MacroRing.tsx
import { useEffect, useRef } from 'react';
import Chart from 'chart.js/auto';

interface MacroRingProps {
  protein_g: number;
  carbs_g: number;
  fat_g: number;
  calories: number;
}

export function MacroRing({ protein_g, carbs_g, fat_g, calories }: MacroRingProps) {
  const canvasRef = useRef<HTMLCanvasElement>(null);
  const chartRef = useRef<Chart | null>(null);

  useEffect(() => {
    if (!canvasRef.current) return;

    const proteinCal = protein_g * 4;
    const carbsCal = carbs_g * 4;
    const fatCal = fat_g * 9;
    const total = proteinCal + carbsCal + fatCal || 1;

    if (chartRef.current) {
      chartRef.current.destroy();
    }

    chartRef.current = new Chart(canvasRef.current, {
      type: 'doughnut',
      data: {
        labels: ['Protein', 'Carbs', 'Fat'],
        datasets: [{
          data: [proteinCal, carbsCal, fatCal],
          backgroundColor: ['#0891b2', '#16a34a', '#d97706'],
          borderWidth: 0,
          hoverOffset: 4,
        }],
      },
      options: {
        cutout: '62%',
        plugins: {
          legend: { display: false },
          tooltip: {
            callbacks: {
              label(ctx) {
                const pct = Math.round((ctx.parsed / total) * 100);
                return `${ctx.label}: ${pct}%`;
              },
            },
          },
        },
      },
    });

    return () => { chartRef.current?.destroy(); };
  }, [protein_g, carbs_g, fat_g]);

  return (
    <figure
      aria-label={`Macro breakdown: Protein ${protein_g}g, Carbs ${carbs_g}g, Fat ${fat_g}g. Total ${calories} kcal.`}
      style={{ position: 'relative', width: 160, height: 160, margin: '0 auto' }}
    >
      <canvas ref={canvasRef} width={160} height={160} />
      <div style={{
        position: 'absolute', inset: 0,
        display: 'flex', flexDirection: 'column',
        alignItems: 'center', justifyContent: 'center',
        pointerEvents: 'none',
      }}>
        <span style={{ fontFamily: 'var(--font-mono)', fontSize: 20, fontWeight: 700 }}>
          {calories.toLocaleString()}
        </span>
        <span style={{ fontSize: 10, color: 'var(--text-muted)' }}>kcal</span>
      </div>
    </figure>
  );
}
```

The center overlay uses `pointerEvents: none` so Chart.js tooltips remain reachable.

---

## MacroBar component

```typescript
// src/client/components/MacroBar.tsx
interface MacroBarProps {
  label: string;
  color: string;
  current: number;
  target: number;
  unit?: string;
}

export function MacroBar({ label, color, current, target, unit = 'g' }: MacroBarProps) {
  const pct = target > 0 ? Math.min(Math.round((current / target) * 100), 100) : 0;
  const over = current > target;

  return (
    <div style={{ marginBottom: 10 }}>
      <div style={{ display: 'flex', justifyContent: 'space-between', fontSize: 12, marginBottom: 3 }}>
        <span style={{ color, fontWeight: 500 }}>{label}</span>
        <span style={{ fontFamily: 'var(--font-mono)' }}>
          {current} / {target} {unit}
        </span>
      </div>
      <div
        role="progressbar"
        aria-valuenow={pct}
        aria-valuemax={100}
        aria-label={`${label}: ${current} of ${target} ${unit}`}
        style={{ height: 6, background: 'var(--border)', borderRadius: 3, overflow: 'hidden' }}
      >
        <div style={{
          height: '100%',
          width: `${pct}%`,
          background: over ? 'var(--alert)' : color,
          borderRadius: 3,
          transition: 'width 0.3s ease',
        }} />
      </div>
    </div>
  );
}
```

---

## CalorieSummary component

```typescript
// src/client/components/CalorieSummary.tsx
interface CalorieSummaryProps {
  consumed: number;
  goal: number;
}

export function CalorieSummary({ consumed, goal }: CalorieSummaryProps) {
  const pct = goal > 0 ? Math.min(Math.round((consumed / goal) * 100), 100) : 0;
  const remaining = Math.max(goal - consumed, 0);
  const over = consumed > goal;

  return (
    <div style={{ marginBottom: 16 }}>
      <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center' }}>
        <span style={{ fontSize: 14, fontWeight: 600 }}>Calories</span>
        <span style={{ fontFamily: 'var(--font-mono)' }}>
          <strong style={{ fontSize: 20 }}>{consumed.toLocaleString()}</strong>
          <span style={{ fontSize: 13, color: 'var(--text-muted)' }}> / {goal.toLocaleString()} kcal</span>
        </span>
      </div>
      <div
        role="progressbar"
        aria-valuenow={pct}
        aria-valuemax={100}
        aria-label={`${consumed} of ${goal} kcal consumed`}
        style={{ height: 10, background: 'var(--border)', borderRadius: 5, overflow: 'hidden', margin: '8px 0 6px' }}
      >
        <div style={{
          height: '100%',
          width: `${pct}%`,
          background: over ? 'var(--alert)' : 'var(--primary)',
          borderRadius: 5,
        }} />
      </div>
      <div style={{ fontSize: 12, color: over ? 'var(--alert)' : 'var(--text-muted)' }}>
        {over
          ? `${(consumed - goal).toLocaleString()} kcal over goal`
          : `${remaining.toLocaleString()} kcal remaining`}
      </div>
    </div>
  );
}
```

---

## API client (typed fetch helper)

```typescript
// src/client/lib/api.ts
const BASE = '/api';

export async function apiFetch<T>(path: string, init?: RequestInit): Promise<T> {
  const res = await fetch(`${BASE}${path}`, {
    headers: { 'Content-Type': 'application/json', ...init?.headers },
    ...init,
  });
  if (!res.ok) {
    const body = await res.text();
    throw new Error(`API ${res.status}: ${body}`);
  }
  return res.json() as Promise<T>;
}

export const api = {
  foods: {
    search: (q: string) => apiFetch<Food[]>(`/foods?q=${encodeURIComponent(q)}`),
    get:    (id: number) => apiFetch<Food>(`/foods/${id}`),
    create: (body: CreateFoodInput) => apiFetch<Food>('/foods', { method: 'POST', body: JSON.stringify(body) }),
    update: (id: number, body: Partial<CreateFoodInput>) =>
      apiFetch<Food>(`/foods/${id}`, { method: 'PUT', body: JSON.stringify(body) }),
    delete: (id: number) => apiFetch<void>(`/foods/${id}`, { method: 'DELETE' }),
  },
  diary: {
    list:    (date: string) => apiFetch<DiaryEntry[]>(`/diary?date=${date}`),
    summary: (date: string) => apiFetch<DiarySummary>(`/diary/summary?date=${date}`),
    history: (from: string, to: string) =>
      apiFetch<DailyTotal[]>(`/diary/history?from=${from}&to=${to}`),
    add:    (body: AddEntryInput) => apiFetch<DiaryEntry>('/diary', { method: 'POST', body: JSON.stringify(body) }),
    update: (id: number, quantity: number) =>
      apiFetch<DiaryEntry>(`/diary/${id}`, { method: 'PUT', body: JSON.stringify({ quantity }) }),
    delete: (id: number) => apiFetch<void>(`/diary/${id}`, { method: 'DELETE' }),
  },
  goals: {
    list:   () => apiFetch<Goal[]>('/goals'),
    update: (goalType: string, target_value: number) =>
      apiFetch<Goal>(`/goals/${goalType}`, { method: 'PATCH', body: JSON.stringify({ target_value }) }),
  },
  stats: {
    weekly: () => apiFetch<WeeklyPoint[]>('/stats/weekly'),
    foods:  (limit = 10) => apiFetch<TopFood[]>(`/stats/foods?limit=${limit}`),
  },
};
```

---

## Troubleshooting

**MacroRing shows all grey / no segments**
All macro values are 0. Ensure diary entries exist for the date and the summary endpoint returns non-zero values.

**CalorieSummary bar stays at 0%**
Goal may be 0. Confirm the goals table has a `calories` row with a non-zero `target_value`.

**Food search returns no results**
The `q` parameter uses `LIKE %q%`. If the food database is empty, seed some foods first via `POST /api/foods`.

**Calorie estimate differs from label value**
The 4-4-9 estimate is a rough guide. Always use the value from the nutrition label as the authoritative source.