epaper-display/display/docs/superpowers/plans/2026-04-06-epaper-display-system.md

# ESP32 E-Paper Display System — Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Build a middleware + web interface + ESP32 firmware for displaying MQTT/PostgreSQL data on a Waveshare 7.5" e-paper display.

**Architecture:** Modular monolith — single Express/TypeScript server with internal modules (MQTT, PostgreSQL, Datapoints, Display). React SPA served as static files. ESP32 fetches JSON via REST API and renders on e-paper using GxEPD2.

**Tech Stack:** Node.js, Express, TypeScript, SQLite (better-sqlite3), React, Vite, mqtt.js, pg, bcrypt, jsonwebtoken, PlatformIO, GxEPD2, ArduinoJson

**Spec:** `docs/superpowers/specs/2026-04-06-epaper-display-system-design.md`

---

## File Structure

```
display/
├── middleware/
│   ├── package.json
│   ├── tsconfig.json
│   ├── src/
│   │   ├── index.ts                          # Entry point: start server + modules
│   │   ├── db/
│   │   │   ├── index.ts                      # SQLite connection (better-sqlite3)
│   │   │   └── migrations/
│   │   │       └── 001-initial.ts            # All tables
│   │   ├── modules/
│   │   │   ├── mqtt/
│   │   │   │   ├── broker-manager.ts         # Connect/disconnect/reconnect brokers
│   │   │   │   ├── topic-discovery.ts        # Subscribe wildcard/prefix, collect topics
│   │   │   │   └── payload-parser.ts         # JSON path extraction
│   │   │   ├── postgres/
│   │   │   │   ├── source-manager.ts         # PG connection pool management
│   │   │   │   └── query-scheduler.ts        # Periodic query execution
│   │   │   ├── datapoints/
│   │   │   │   ├── datapoint-manager.ts      # Central registry, value updates, transforms
│   │   │   │   └── transforms.ts             # round, scale, map functions
│   │   │   └── display/
│   │   │       ├── layout-manager.ts         # Layout + element CRUD
│   │   │       └── display-builder.ts        # Build ESP32 JSON response
│   │   └── api/
│   │       ├── index.ts                      # Express app setup, static files
│   │       ├── middleware/
│   │       │   └── auth.ts                   # JWT verify middleware
│   │       └── routes/
│   │           ├── auth.ts                   # POST /api/auth/login
│   │           ├── mqtt-brokers.ts           # CRUD /api/mqtt/brokers
│   │           ├── mqtt-topics.ts            # Topics + discovery
│   │           ├── pg-sources.ts             # CRUD /api/pg/sources + queries
│   │           ├── datapoints.ts             # GET/PUT /api/datapoints
│   │           ├── displays.ts               # CRUD /api/displays
│   │           ├── layouts.ts                # CRUD /api/layouts + elements
│   │           └── display-api.ts            # GET /api/display/:id (ESP32)
│   └── tests/
│       ├── db.test.ts
│       ├── transforms.test.ts
│       ├── display-builder.test.ts
│       ├── payload-parser.test.ts
│       └── api/
│           ├── auth.test.ts
│           └── display-api.test.ts
├── frontend/
│   ├── package.json
│   ├── tsconfig.json
│   ├── vite.config.ts
│   ├── index.html
│   └── src/
│       ├── main.tsx                          # React entry
│       ├── App.tsx                           # Router + layout
│       ├── api/
│       │   └── client.ts                     # Fetch wrapper with JWT
│       ├── components/
│       │   ├── Sidebar.tsx                   # Navigation sidebar
│       │   ├── StatusBadge.tsx               # Connection status indicator
│       │   └── EpaperPreview.tsx             # E-Paper display simulation
│       └── pages/
│           ├── Login.tsx
│           ├── Dashboard.tsx
│           ├── MqttBrokers.tsx
│           ├── MqttTopics.tsx
│           ├── PgSources.tsx
│           ├── Datapoints.tsx
│           ├── Displays.tsx
│           └── Layouts.tsx
├── esp32/
│   ├── platformio.ini
│   ├── src/
│   │   └── main.cpp                          # WiFi + HTTP + Render + Deep Sleep
│   └── include/
│       ├── config.h                          # WiFi/API config from NVS
│       ├── renderer.h                        # Element rendering functions
│       ├── icons.h                           # 12 icon bitmaps (24x24)
│       └── captive_portal.h                  # AP + config web page
└── docs/
```

---

## Task 1: Middleware Project Scaffolding

**Files:**
- Create: `middleware/package.json`
- Create: `middleware/tsconfig.json`
- Create: `middleware/src/index.ts`

- [ ] **Step 1: Create middleware/package.json**

```json
{
  "name": "epaper-middleware",
  "version": "1.0.0",
  "private": true,
  "scripts": {
    "dev": "tsx watch src/index.ts",
    "build": "tsc",
    "start": "node dist/index.js",
    "test": "vitest run",
    "test:watch": "vitest"
  },
  "dependencies": {
    "express": "^4.21.0",
    "better-sqlite3": "^11.6.0",
    "mqtt": "^5.10.0",
    "pg": "^8.13.0",
    "bcrypt": "^5.1.1",
    "jsonwebtoken": "^9.0.2",
    "cors": "^2.8.5"
  },
  "devDependencies": {
    "@types/express": "^5.0.0",
    "@types/better-sqlite3": "^7.6.12",
    "@types/pg": "^8.11.10",
    "@types/bcrypt": "^5.0.2",
    "@types/jsonwebtoken": "^9.0.7",
    "@types/cors": "^2.8.17",
    "typescript": "^5.6.0",
    "tsx": "^4.19.0",
    "vitest": "^2.1.0"
  }
}
```

- [ ] **Step 2: Create middleware/tsconfig.json**

```json
{
  "compilerOptions": {
    "target": "ES2022",
    "module": "commonjs",
    "lib": ["ES2022"],
    "outDir": "./dist",
    "rootDir": "./src",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "forceConsistentCasingInFileNames": true,
    "resolveJsonModule": true,
    "declaration": true
  },
  "include": ["src/**/*"],
  "exclude": ["node_modules", "dist", "tests"]
}
```

- [ ] **Step 3: Create minimal src/index.ts**

```typescript
import express from 'express';

const app = express();
const PORT = process.env.PORT || 4000;

app.use(express.json());

app.get('/api/health', (_req, res) => {
  res.json({ status: 'ok', timestamp: new Date().toISOString() });
});

app.listen(PORT, () => {
  console.log(`EPaper Middleware running on port ${PORT}`);
});

export default app;
```

- [ ] **Step 4: Install dependencies and verify**

Run: `cd middleware && npm install`
Expected: node_modules created, no errors

Run: `cd middleware && npx tsx src/index.ts &` then `curl http://localhost:4000/api/health`
Expected: `{"status":"ok","timestamp":"..."}`

- [ ] **Step 5: Commit**

```bash
git add middleware/package.json middleware/tsconfig.json middleware/src/index.ts
git commit -m "feat: scaffold middleware project with Express/TypeScript"
```

---

## Task 2: SQLite Database + Migrations

**Files:**
- Create: `middleware/src/db/index.ts`
- Create: `middleware/src/db/migrations/001-initial.ts`
- Create: `middleware/tests/db.test.ts`

- [ ] **Step 1: Write db test**

```typescript
// middleware/tests/db.test.ts
import { describe, it, expect, beforeEach, afterEach } from 'vitest';
import { initDb, getDb } from '../src/db/index';
import Database from 'better-sqlite3';

describe('Database', () => {
  let db: Database.Database;

  beforeEach(() => {
    db = initDb(':memory:');
  });

  afterEach(() => {
    db.close();
  });

  it('should create all tables', () => {
    const tables = db.prepare(
      "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
    ).all() as { name: string }[];
    const names = tables.map(t => t.name);
    expect(names).toContain('mqtt_brokers');
    expect(names).toContain('mqtt_topics');
    expect(names).toContain('pg_sources');
    expect(names).toContain('pg_queries');
    expect(names).toContain('datapoints');
    expect(names).toContain('displays');
    expect(names).toContain('layouts');
    expect(names).toContain('layout_elements');
    expect(names).toContain('users');
  });

  it('should enable WAL mode', () => {
    const result = db.pragma('journal_mode', { simple: true }) as string;
    expect(result).toBe('wal');
  });

  it('should insert and retrieve a broker', () => {
    db.prepare(
      `INSERT INTO mqtt_brokers (name, host, port) VALUES (?, ?, ?)`
    ).run('Test Broker', 'localhost', 1883);
    const broker = db.prepare('SELECT * FROM mqtt_brokers WHERE id = 1').get() as any;
    expect(broker.name).toBe('Test Broker');
    expect(broker.host).toBe('localhost');
    expect(broker.enabled).toBe(1);
  });
});
```

- [ ] **Step 2: Run test to verify it fails**

Run: `cd middleware && npx vitest run tests/db.test.ts`
Expected: FAIL — module `../src/db/index` not found

- [ ] **Step 3: Create db/index.ts**

```typescript
// middleware/src/db/index.ts
import Database from 'better-sqlite3';
import { runMigrations } from './migrations/001-initial';

let db: Database.Database;

export function initDb(dbPath: string = './data/epaper.db'): Database.Database {
  db = new Database(dbPath);
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');
  runMigrations(db);
  return db;
}

export function getDb(): Database.Database {
  if (!db) throw new Error('Database not initialized. Call initDb() first.');
  return db;
}
```

- [ ] **Step 4: Create migrations/001-initial.ts**

```typescript
// middleware/src/db/migrations/001-initial.ts
import Database from 'better-sqlite3';

export function runMigrations(db: Database.Database): void {
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT UNIQUE NOT NULL,
      password_hash TEXT NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS mqtt_brokers (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      host TEXT NOT NULL,
      port INTEGER DEFAULT 1883,
      username TEXT,
      password TEXT,
      use_tls INTEGER DEFAULT 0,
      enabled INTEGER DEFAULT 1,
      status TEXT DEFAULT 'disconnected',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS mqtt_topics (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      broker_id INTEGER NOT NULL REFERENCES mqtt_brokers(id) ON DELETE CASCADE,
      topic TEXT NOT NULL,
      json_path TEXT,
      selected INTEGER DEFAULT 0,
      ignored INTEGER DEFAULT 0,
      last_payload TEXT,
      discovered_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS pg_sources (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      host TEXT NOT NULL,
      port INTEGER DEFAULT 5432,
      database_name TEXT NOT NULL,
      username TEXT NOT NULL,
      password TEXT NOT NULL,
      use_tls INTEGER DEFAULT 0,
      enabled INTEGER DEFAULT 1,
      status TEXT DEFAULT 'disconnected',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS pg_queries (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      source_id INTEGER NOT NULL REFERENCES pg_sources(id) ON DELETE CASCADE,
      name TEXT NOT NULL,
      query TEXT NOT NULL,
      interval_sec INTEGER DEFAULT 60,
      result_column TEXT NOT NULL,
      last_result TEXT,
      last_run_at DATETIME
    );

    CREATE TABLE IF NOT EXISTS datapoints (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      label TEXT NOT NULL,
      unit TEXT,
      value TEXT,
      status TEXT DEFAULT 'unknown',
      source_type TEXT NOT NULL CHECK(source_type IN ('mqtt', 'postgresql')),
      source_id INTEGER NOT NULL,
      topic_id INTEGER REFERENCES mqtt_topics(id) ON DELETE SET NULL,
      query_id INTEGER REFERENCES pg_queries(id) ON DELETE SET NULL,
      transform TEXT,
      enabled INTEGER DEFAULT 1,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS layouts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      type TEXT DEFAULT 'free' CHECK(type IN ('free', 'grid')),
      width INTEGER DEFAULT 800,
      height INTEGER DEFAULT 480,
      grid_cols INTEGER,
      grid_rows INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS layout_elements (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      layout_id INTEGER NOT NULL REFERENCES layouts(id) ON DELETE CASCADE,
      datapoint_id INTEGER REFERENCES datapoints(id) ON DELETE SET NULL,
      type TEXT NOT NULL CHECK(type IN ('value', 'label', 'line', 'rect')),
      x INTEGER NOT NULL DEFAULT 0,
      y INTEGER NOT NULL DEFAULT 0,
      width INTEGER,
      height INTEGER,
      font_size INTEGER DEFAULT 24,
      font_weight TEXT DEFAULT 'normal',
      text_align TEXT DEFAULT 'left',
      icon TEXT,
      style TEXT DEFAULT 'plain' CHECK(style IN ('plain', 'card')),
      static_text TEXT,
      grid_col INTEGER,
      grid_row INTEGER,
      sort_order INTEGER DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS displays (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      description TEXT,
      layout_id INTEGER REFERENCES layouts(id) ON DELETE SET NULL,
      api_token TEXT,
      last_seen DATETIME,
      refresh_sec INTEGER DEFAULT 300,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );
  `);
}
```

- [ ] **Step 5: Run tests**

Run: `cd middleware && npx vitest run tests/db.test.ts`
Expected: All 3 tests PASS

- [ ] **Step 6: Commit**

```bash
git add middleware/src/db/ middleware/tests/db.test.ts
git commit -m "feat: add SQLite database with migrations for all tables"
```

---

## Task 3: Auth Module (JWT + bcrypt)

**Files:**
- Create: `middleware/src/api/middleware/auth.ts`
- Create: `middleware/src/api/routes/auth.ts`
- Create: `middleware/tests/api/auth.test.ts`

- [ ] **Step 1: Write auth test**

```typescript
// middleware/tests/api/auth.test.ts
import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import express from 'express';
import request from 'supertest';
import { initDb } from '../../src/db/index';
import bcrypt from 'bcrypt';
import { authRouter } from '../../src/api/routes/auth';
import { authMiddleware } from '../../src/api/middleware/auth';
import Database from 'better-sqlite3';

describe('Auth API', () => {
  let app: express.Express;
  let db: Database.Database;

  beforeAll(async () => {
    db = initDb(':memory:');
    const hash = await bcrypt.hash('admin123', 10);
    db.prepare('INSERT INTO users (username, password_hash) VALUES (?, ?)').run('admin', hash);

    app = express();
    app.use(express.json());
    app.use('/api/auth', authRouter);
    app.get('/api/protected', authMiddleware, (_req, res) => {
      res.json({ ok: true });
    });
  });

  afterAll(() => db.close());

  it('should login with correct credentials', async () => {
    const res = await request(app)
      .post('/api/auth/login')
      .send({ username: 'admin', password: 'admin123' });
    expect(res.status).toBe(200);
    expect(res.body.token).toBeDefined();
  });

  it('should reject wrong password', async () => {
    const res = await request(app)
      .post('/api/auth/login')
      .send({ username: 'admin', password: 'wrong' });
    expect(res.status).toBe(401);
  });

  it('should reject request without token', async () => {
    const res = await request(app).get('/api/protected');
    expect(res.status).toBe(401);
  });

  it('should allow request with valid token', async () => {
    const loginRes = await request(app)
      .post('/api/auth/login')
      .send({ username: 'admin', password: 'admin123' });
    const res = await request(app)
      .get('/api/protected')
      .set('Authorization', `Bearer ${loginRes.body.token}`);
    expect(res.status).toBe(200);
    expect(res.body.ok).toBe(true);
  });
});
```

- [ ] **Step 2: Add supertest dev dependency**

Run: `cd middleware && npm install -D supertest @types/supertest`

- [ ] **Step 3: Run test to verify it fails**

Run: `cd middleware && npx vitest run tests/api/auth.test.ts`
Expected: FAIL — modules not found

- [ ] **Step 4: Create auth middleware**

```typescript
// middleware/src/api/middleware/auth.ts
import { Request, Response, NextFunction } from 'express';
import jwt from 'jsonwebtoken';

const JWT_SECRET = process.env.JWT_SECRET || 'epaper-secret-change-me';

export function signToken(userId: number, username: string): string {
  return jwt.sign({ userId, username }, JWT_SECRET, { expiresIn: '24h' });
}

export function authMiddleware(req: Request, res: Response, next: NextFunction): void {
  const header = req.headers.authorization;
  if (!header?.startsWith('Bearer ')) {
    res.status(401).json({ error: 'No token provided' });
    return;
  }
  try {
    const decoded = jwt.verify(header.slice(7), JWT_SECRET);
    (req as any).user = decoded;
    next();
  } catch {
    res.status(401).json({ error: 'Invalid token' });
  }
}
```

- [ ] **Step 5: Create auth route**

```typescript
// middleware/src/api/routes/auth.ts
import { Router } from 'express';
import bcrypt from 'bcrypt';
import { getDb } from '../../db/index';
import { signToken } from '../middleware/auth';

export const authRouter = Router();

authRouter.post('/login', async (req, res) => {
  const { username, password } = req.body;
  if (!username || !password) {
    res.status(400).json({ error: 'Username and password required' });
    return;
  }
  const user = getDb().prepare('SELECT * FROM users WHERE username = ?').get(username) as any;
  if (!user) {
    res.status(401).json({ error: 'Invalid credentials' });
    return;
  }
  const valid = await bcrypt.compare(password, user.password_hash);
  if (!valid) {
    res.status(401).json({ error: 'Invalid credentials' });
    return;
  }
  const token = signToken(user.id, user.username);
  res.json({ token, username: user.username });
});
```

- [ ] **Step 6: Run tests**

Run: `cd middleware && npx vitest run tests/api/auth.test.ts`
Expected: All 4 tests PASS

- [ ] **Step 7: Commit**

```bash
git add middleware/src/api/middleware/auth.ts middleware/src/api/routes/auth.ts middleware/tests/api/auth.test.ts middleware/package.json
git commit -m "feat: add JWT auth with login endpoint and middleware"
```

---

## Task 4: Datapoint Transforms

**Files:**
- Create: `middleware/src/modules/datapoints/transforms.ts`
- Create: `middleware/tests/transforms.test.ts`

- [ ] **Step 1: Write transforms test**

```typescript
// middleware/tests/transforms.test.ts
import { describe, it, expect } from 'vitest';
import { applyTransform } from '../src/modules/datapoints/transforms';

describe('Transforms', () => {
  it('should round values', () => {
    expect(applyTransform('23.456', { round: 1 })).toBe('23.5');
    expect(applyTransform('23.456', { round: 0 })).toBe('23');
    expect(applyTransform('23.456', { round: 2 })).toBe('23.46');
  });

  it('should scale values', () => {
    expect(applyTransform('100', { scale: 0.1 })).toBe('10');
    expect(applyTransform('25', { scale: 1.8, round: 1 })).toBe('45');
  });

  it('should map values', () => {
    expect(applyTransform('1', { map: { '1': 'ON', '0': 'OFF' } })).toBe('ON');
    expect(applyTransform('0', { map: { '1': 'ON', '0': 'OFF' } })).toBe('OFF');
    expect(applyTransform('2', { map: { '1': 'ON', '0': 'OFF' } })).toBe('2');
  });

  it('should apply scale then round', () => {
    expect(applyTransform('3.7', { scale: 2.0, round: 0 })).toBe('7');
  });

  it('should return original value if no transform', () => {
    expect(applyTransform('hello', {})).toBe('hello');
    expect(applyTransform('42', undefined)).toBe('42');
  });
});
```

- [ ] **Step 2: Run test to verify it fails**

Run: `cd middleware && npx vitest run tests/transforms.test.ts`
Expected: FAIL

- [ ] **Step 3: Implement transforms**

```typescript
// middleware/src/modules/datapoints/transforms.ts
export interface TransformConfig {
  round?: number;
  scale?: number;
  map?: Record<string, string>;
}

export function applyTransform(value: string, config: TransformConfig | undefined | null): string {
  if (!config) return value;

  if (config.map && config.map[value] !== undefined) {
    return config.map[value];
  }

  let num = parseFloat(value);
  if (isNaN(num)) return value;

  if (config.scale !== undefined) {
    num = num * config.scale;
  }
  if (config.round !== undefined) {
    num = parseFloat(num.toFixed(config.round));
  }

  return String(num);
}
```

- [ ] **Step 4: Run tests**

Run: `cd middleware && npx vitest run tests/transforms.test.ts`
Expected: All 5 tests PASS

- [ ] **Step 5: Commit**

```bash
git add middleware/src/modules/datapoints/transforms.ts middleware/tests/transforms.test.ts
git commit -m "feat: add datapoint value transforms (round, scale, map)"
```

---

## Task 5: Payload Parser (JSON Path Extraction)

**Files:**
- Create: `middleware/src/modules/mqtt/payload-parser.ts`
- Create: `middleware/tests/payload-parser.test.ts`

- [ ] **Step 1: Write payload parser test**

```typescript
// middleware/tests/payload-parser.test.ts
import { describe, it, expect } from 'vitest';
import { extractValue } from '../src/modules/mqtt/payload-parser';

describe('Payload Parser', () => {
  it('should return raw string if no json_path', () => {
    expect(extractValue('42.5', null)).toBe('42.5');
  });

  it('should extract simple JSON field', () => {
    expect(extractValue('{"value": 23.5}', '$.value')).toBe('23.5');
  });

  it('should extract nested JSON field', () => {
    expect(extractValue('{"sensor": {"temp": 21.3}}', '$.sensor.temp')).toBe('21.3');
  });

  it('should extract string values', () => {
    expect(extractValue('{"status": "online"}', '$.status')).toBe('online');
  });

  it('should return null for missing path', () => {
    expect(extractValue('{"a": 1}', '$.b')).toBeNull();
  });

  it('should return null for invalid JSON', () => {
    expect(extractValue('not json', '$.value')).toBeNull();
  });
});
```

- [ ] **Step 2: Run test to verify it fails**

Run: `cd middleware && npx vitest run tests/payload-parser.test.ts`
Expected: FAIL

- [ ] **Step 3: Implement payload parser**

```typescript
// middleware/src/modules/mqtt/payload-parser.ts
export function extractValue(payload: string, jsonPath: string | null): string | null {
  if (!jsonPath) return payload;

  let parsed: any;
  try {
    parsed = JSON.parse(payload);
  } catch {
    return null;
  }

  const parts = jsonPath
    .replace(/^\$\.?/, '')
    .split('.')
    .filter(p => p.length > 0);

  let current = parsed;
  for (const part of parts) {
    if (current === null || current === undefined || typeof current !== 'object') {
      return null;
    }
    current = current[part];
  }

  if (current === null || current === undefined) return null;
  return String(current);
}
```

- [ ] **Step 4: Run tests**

Run: `cd middleware && npx vitest run tests/payload-parser.test.ts`
Expected: All 6 tests PASS

- [ ] **Step 5: Commit**

```bash
git add middleware/src/modules/mqtt/payload-parser.ts middleware/tests/payload-parser.test.ts
git commit -m "feat: add MQTT payload parser with JSON path extraction"
```

---

## Task 6: Datapoint Manager

**Files:**
- Create: `middleware/src/modules/datapoints/datapoint-manager.ts`

- [ ] **Step 1: Implement datapoint manager**

```typescript
// middleware/src/modules/datapoints/datapoint-manager.ts
import { getDb } from '../../db/index';
import { applyTransform, TransformConfig } from './transforms';

export interface Datapoint {
  id: number;
  name: string;
  label: string;
  unit: string | null;
  value: string | null;
  status: string;
  source_type: string;
  source_id: number;
  topic_id: number | null;
  query_id: number | null;
  transform: string | null;
  enabled: number;
  updated_at: string;
}

export function getAllDatapoints(): Datapoint[] {
  return getDb().prepare('SELECT * FROM datapoints WHERE enabled = 1').all() as Datapoint[];
}

export function getDatapointById(id: number): Datapoint | undefined {
  return getDb().prepare('SELECT * FROM datapoints WHERE id = ?').get(id) as Datapoint | undefined;
}

export function updateDatapointValue(id: number, rawValue: string): void {
  const dp = getDatapointById(id);
  if (!dp) return;

  const transformConfig: TransformConfig | null = dp.transform ? JSON.parse(dp.transform) : null;
  const value = applyTransform(rawValue, transformConfig);

  getDb().prepare(
    'UPDATE datapoints SET value = ?, status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?'
  ).run(value, 'ok', id);
}

export function updateDatapoint(id: number, data: Partial<Pick<Datapoint, 'label' | 'unit' | 'transform' | 'enabled'>>): void {
  const fields: string[] = [];
  const values: any[] = [];

  if (data.label !== undefined) { fields.push('label = ?'); values.push(data.label); }
  if (data.unit !== undefined) { fields.push('unit = ?'); values.push(data.unit); }
  if (data.transform !== undefined) { fields.push('transform = ?'); values.push(data.transform); }
  if (data.enabled !== undefined) { fields.push('enabled = ?'); values.push(data.enabled); }

  if (fields.length === 0) return;
  values.push(id);
  getDb().prepare(`UPDATE datapoints SET ${fields.join(', ')} WHERE id = ?`).run(...values);
}

export function createDatapointFromMqtt(brokerId: number, topicId: number, topicPath: string): number {
  const result = getDb().prepare(
    `INSERT INTO datapoints (name, label, source_type, source_id, topic_id)
     VALUES (?, ?, 'mqtt', ?, ?)`
  ).run(topicPath, topicPath, brokerId, topicId);
  return Number(result.lastInsertRowid);
}

export function createDatapointFromPg(sourceId: number, queryId: number, queryName: string): number {
  const result = getDb().prepare(
    `INSERT INTO datapoints (name, label, source_type, source_id, query_id)
     VALUES (?, ?, 'postgresql', ?, ?)`
  ).run(queryName, queryName, sourceId, queryId);
  return Number(result.lastInsertRowid);
}

export function getDatapointByTopicId(topicId: number): Datapoint | undefined {
  return getDb().prepare('SELECT * FROM datapoints WHERE topic_id = ?').get(topicId) as Datapoint | undefined;
}

export function getDatapointByQueryId(queryId: number): Datapoint | undefined {
  return getDb().prepare('SELECT * FROM datapoints WHERE query_id = ?').get(queryId) as Datapoint | undefined;
}
```

- [ ] **Step 2: Commit**

```bash
git add middleware/src/modules/datapoints/datapoint-manager.ts
git commit -m "feat: add datapoint manager with CRUD and value updates"
```

---

## Task 7: Display Builder (ESP32 JSON)

**Files:**
- Create: `middleware/src/modules/display/display-builder.ts`
- Create: `middleware/tests/display-builder.test.ts`

- [ ] **Step 1: Write display builder test**

```typescript
// middleware/tests/display-builder.test.ts
import { describe, it, expect, beforeEach, afterEach } from 'vitest';
import { initDb } from '../src/db/index';
import { buildDisplayJson } from '../src/modules/display/display-builder';
import Database from 'better-sqlite3';

describe('Display Builder', () => {
  let db: Database.Database;

  beforeEach(() => {
    db = initDb(':memory:');
    db.prepare(`INSERT INTO layouts (id, name, type, width, height) VALUES (1, 'Test Layout', 'free', 800, 480)`).run();
    db.prepare(`INSERT INTO displays (id, name, layout_id, refresh_sec) VALUES (1, 'Werkstatt', 1, 300)`).run();
    db.prepare(`INSERT INTO datapoints (id, name, label, unit, value, status, source_type, source_id) VALUES (1, 'temp', 'Temperatur', '°C', '23.5', 'ok', 'mqtt', 1)`).run();
    db.prepare(`INSERT INTO layout_elements (layout_id, type, x, y, font_size, font_weight, text_align, static_text, sort_order) VALUES (1, 'label', 400, 20, 28, 'bold', 'center', 'Werkstatt Monitor', 0)`).run();
    db.prepare(`INSERT INTO layout_elements (layout_id, datapoint_id, type, x, y, width, height, font_size, font_weight, icon, style, sort_order) VALUES (1, 1, 'value', 20, 80, 380, 90, 32, 'bold', 'thermometer', 'card', 1)`).run();
  });

  afterEach(() => db.close());

  it('should build complete display JSON', () => {
    const result = buildDisplayJson(1);
    expect(result).not.toBeNull();
    expect(result!.display.id).toBe(1);
    expect(result!.display.name).toBe('Werkstatt');
    expect(result!.display.width).toBe(800);
    expect(result!.display.height).toBe(480);
    expect(result!.display.refresh_sec).toBe(300);
    expect(result!.layout.type).toBe('free');
    expect(result!.elements).toHaveLength(2);
  });

  it('should include label element with static text', () => {
    const result = buildDisplayJson(1)!;
    const label = result.elements[0];
    expect(label.type).toBe('label');
    expect(label.text).toBe('Werkstatt Monitor');
  });

  it('should include value element with datapoint data', () => {
    const result = buildDisplayJson(1)!;
    const value = result.elements[1];
    expect(value.type).toBe('value');
    expect(value.label).toBe('Temperatur');
    expect(value.value).toBe('23.5');
    expect(value.unit).toBe('°C');
    expect(value.icon).toBe('thermometer');
    expect(value.style).toBe('card');
  });

  it('should return null for non-existent display', () => {
    expect(buildDisplayJson(999)).toBeNull();
  });
});
```

- [ ] **Step 2: Run test to verify it fails**

Run: `cd middleware && npx vitest run tests/display-builder.test.ts`
Expected: FAIL

- [ ] **Step 3: Implement display builder**

```typescript
// middleware/src/modules/display/display-builder.ts
import { getDb } from '../../db/index';

interface DisplayJson {
  display: { id: number; name: string; width: number; height: number; refresh_sec: number };
  layout: { type: string; grid_cols?: number; grid_rows?: number };
  elements: DisplayElement[];
}

interface DisplayElement {
  type: string; x: number; y: number;
  width?: number; height?: number; font_size?: number; font_weight?: string;
  text_align?: string; icon?: string; style?: string;
  text?: string; label?: string; value?: string; unit?: string;
}

export function buildDisplayJson(displayId: number): DisplayJson | null {
  const db = getDb();

  const display = db.prepare(
    'SELECT d.*, l.type as layout_type, l.width, l.height, l.grid_cols, l.grid_rows FROM displays d LEFT JOIN layouts l ON d.layout_id = l.id WHERE d.id = ?'
  ).get(displayId) as any;

  if (!display || !display.layout_id) return null;

  const elements = db.prepare(
    `SELECT le.*, dp.label as dp_label, dp.value as dp_value, dp.unit as dp_unit
     FROM layout_elements le
     LEFT JOIN datapoints dp ON le.datapoint_id = dp.id
     WHERE le.layout_id = ?
     ORDER BY le.sort_order`
  ).all(display.layout_id) as any[];

  const mappedElements: DisplayElement[] = elements.map(el => {
    const base: DisplayElement = { type: el.type, x: el.x, y: el.y };

    if (el.width) base.width = el.width;
    if (el.height) base.height = el.height;
    if (el.font_size) base.font_size = el.font_size;
    if (el.font_weight && el.font_weight !== 'normal') base.font_weight = el.font_weight;
    if (el.text_align && el.text_align !== 'left') base.text_align = el.text_align;
    if (el.icon) base.icon = el.icon;
    if (el.style && el.style !== 'plain') base.style = el.style;

    if (el.type === 'label') {
      base.text = el.static_text || '';
    } else if (el.type === 'value') {
      base.label = el.dp_label || el.static_text || '';
      base.value = el.dp_value || '--';
      base.unit = el.dp_unit || '';
    }

    return base;
  });

  return {
    display: { id: display.id, name: display.name, width: display.width, height: display.height, refresh_sec: display.refresh_sec },
    layout: { type: display.layout_type, ...(display.grid_cols && { grid_cols: display.grid_cols }), ...(display.grid_rows && { grid_rows: display.grid_rows }) },
    elements: mappedElements,
  };
}
```

- [ ] **Step 4: Run tests**

Run: `cd middleware && npx vitest run tests/display-builder.test.ts`
Expected: All 4 tests PASS

- [ ] **Step 5: Commit**

```bash
git add middleware/src/modules/display/display-builder.ts middleware/tests/display-builder.test.ts
git commit -m "feat: add display JSON builder for ESP32 API"
```

---

## Task 8: Layout Manager

**Files:**
- Create: `middleware/src/modules/display/layout-manager.ts`

- [ ] **Step 1: Implement layout manager**

```typescript
// middleware/src/modules/display/layout-manager.ts
import { getDb } from '../../db/index';

export interface Layout {
  id: number; name: string; type: string; width: number; height: number;
  grid_cols: number | null; grid_rows: number | null; created_at: string;
}

export interface LayoutElement {
  id: number; layout_id: number; datapoint_id: number | null; type: string;
  x: number; y: number; width: number | null; height: number | null;
  font_size: number; font_weight: string; text_align: string; icon: string | null;
  style: string; static_text: string | null; grid_col: number | null;
  grid_row: number | null; sort_order: number;
}

export function getAllLayouts(): Layout[] {
  return getDb().prepare('SELECT * FROM layouts ORDER BY created_at DESC').all() as Layout[];
}

export function getLayoutById(id: number): (Layout & { elements: LayoutElement[] }) | null {
  const layout = getDb().prepare('SELECT * FROM layouts WHERE id = ?').get(id) as Layout | undefined;
  if (!layout) return null;
  const elements = getDb().prepare('SELECT * FROM layout_elements WHERE layout_id = ? ORDER BY sort_order').all(id) as LayoutElement[];
  return { ...layout, elements };
}

export function createLayout(data: { name: string; type: string; width?: number; height?: number; grid_cols?: number; grid_rows?: number }): number {
  const result = getDb().prepare(
    'INSERT INTO layouts (name, type, width, height, grid_cols, grid_rows) VALUES (?, ?, ?, ?, ?, ?)'
  ).run(data.name, data.type, data.width ?? 800, data.height ?? 480, data.grid_cols ?? null, data.grid_rows ?? null);
  return Number(result.lastInsertRowid);
}

export function updateLayout(id: number, data: Partial<{ name: string; type: string; grid_cols: number; grid_rows: number }>): void {
  const fields: string[] = [];
  const values: any[] = [];
  if (data.name !== undefined) { fields.push('name = ?'); values.push(data.name); }
  if (data.type !== undefined) { fields.push('type = ?'); values.push(data.type); }
  if (data.grid_cols !== undefined) { fields.push('grid_cols = ?'); values.push(data.grid_cols); }
  if (data.grid_rows !== undefined) { fields.push('grid_rows = ?'); values.push(data.grid_rows); }
  if (fields.length === 0) return;
  values.push(id);
  getDb().prepare(`UPDATE layouts SET ${fields.join(', ')} WHERE id = ?`).run(...values);
}

export function deleteLayout(id: number): void {
  getDb().prepare('DELETE FROM layouts WHERE id = ?').run(id);
}

export function addElement(layoutId: number, data: Omit<LayoutElement, 'id' | 'layout_id'>): number {
  const result = getDb().prepare(
    `INSERT INTO layout_elements (layout_id, datapoint_id, type, x, y, width, height, font_size, font_weight, text_align, icon, style, static_text, grid_col, grid_row, sort_order)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
  ).run(layoutId, data.datapoint_id, data.type, data.x, data.y, data.width, data.height,
    data.font_size, data.font_weight, data.text_align, data.icon, data.style,
    data.static_text, data.grid_col, data.grid_row, data.sort_order);
  return Number(result.lastInsertRowid);
}

export function updateElement(id: number, data: Partial<LayoutElement>): void {
  const allowedFields = ['datapoint_id', 'type', 'x', 'y', 'width', 'height', 'font_size', 'font_weight', 'text_align', 'icon', 'style', 'static_text', 'grid_col', 'grid_row', 'sort_order'];
  const fields: string[] = [];
  const values: any[] = [];
  for (const key of allowedFields) {
    if ((data as any)[key] !== undefined) { fields.push(`${key} = ?`); values.push((data as any)[key]); }
  }
  if (fields.length === 0) return;
  values.push(id);
  getDb().prepare(`UPDATE layout_elements SET ${fields.join(', ')} WHERE id = ?`).run(...values);
}

export function deleteElement(id: number): void {
  getDb().prepare('DELETE FROM layout_elements WHERE id = ?').run(id);
}
```

- [ ] **Step 2: Commit**

```bash
git add middleware/src/modules/display/layout-manager.ts
git commit -m "feat: add layout manager with element CRUD"
```

---

## Task 9: MQTT Broker Manager

**Files:**
- Create: `middleware/src/modules/mqtt/broker-manager.ts`

- [ ] **Step 1: Implement broker manager**

```typescript
// middleware/src/modules/mqtt/broker-manager.ts
import mqtt, { MqttClient } from 'mqtt';
import { getDb } from '../../db/index';
import { extractValue } from './payload-parser';
import { updateDatapointValue, getDatapointByTopicId } from '../datapoints/datapoint-manager';

const connections: Map<number, MqttClient> = new Map();

export interface MqttBroker {
  id: number; name: string; host: string; port: number;
  username: string | null; password: string | null;
  use_tls: number; enabled: number; status: string;
  created_at: string; updated_at: string;
}

export function getAllBrokers(): MqttBroker[] {
  return getDb().prepare('SELECT * FROM mqtt_brokers ORDER BY created_at DESC').all() as MqttBroker[];
}

export function getBrokerById(id: number): MqttBroker | undefined {
  return getDb().prepare('SELECT * FROM mqtt_brokers WHERE id = ?').get(id) as MqttBroker | undefined;
}

export function createBroker(data: { name: string; host: string; port?: number; username?: string; password?: string; use_tls?: boolean }): number {
  const result = getDb().prepare(
    'INSERT INTO mqtt_brokers (name, host, port, username, password, use_tls) VALUES (?, ?, ?, ?, ?, ?)'
  ).run(data.name, data.host, data.port ?? 1883, data.username ?? null, data.password ?? null, data.use_tls ? 1 : 0);
  return Number(result.lastInsertRowid);
}

export function updateBroker(id: number, data: Partial<{ name: string; host: string; port: number; username: string; password: string; use_tls: boolean; enabled: boolean }>): void {
  const fields: string[] = ['updated_at = CURRENT_TIMESTAMP'];
  const values: any[] = [];
  if (data.name !== undefined) { fields.push('name = ?'); values.push(data.name); }
  if (data.host !== undefined) { fields.push('host = ?'); values.push(data.host); }
  if (data.port !== undefined) { fields.push('port = ?'); values.push(data.port); }
  if (data.username !== undefined) { fields.push('username = ?'); values.push(data.username); }
  if (data.password !== undefined) { fields.push('password = ?'); values.push(data.password); }
  if (data.use_tls !== undefined) { fields.push('use_tls = ?'); values.push(data.use_tls ? 1 : 0); }
  if (data.enabled !== undefined) { fields.push('enabled = ?'); values.push(data.enabled ? 1 : 0); }
  values.push(id);
  getDb().prepare(`UPDATE mqtt_brokers SET ${fields.join(', ')} WHERE id = ?`).run(...values);
}

export function deleteBroker(id: number): void {
  disconnectBroker(id);
  getDb().prepare('DELETE FROM mqtt_brokers WHERE id = ?').run(id);
}

function setStatus(id: number, status: string): void {
  getDb().prepare('UPDATE mqtt_brokers SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?').run(status, id);
}

export function connectBroker(id: number): void {
  const broker = getBrokerById(id);
  if (!broker || !broker.enabled) return;
  if (connections.has(id)) disconnectBroker(id);

  const protocol = broker.use_tls ? 'mqtts' : 'mqtt';
  const client = mqtt.connect(`${protocol}://${broker.host}:${broker.port}`, {
    username: broker.username || undefined,
    password: broker.password || undefined,
    reconnectPeriod: 5000,
  });

  client.on('connect', () => {
    setStatus(id, 'connected');
    const topics = getDb().prepare(
      'SELECT * FROM mqtt_topics WHERE broker_id = ? AND selected = 1 AND ignored = 0'
    ).all(id) as any[];
    for (const t of topics) client.subscribe(t.topic);
  });

  client.on('error', () => setStatus(id, 'error'));
  client.on('close', () => setStatus(id, 'disconnected'));

  client.on('message', (topic, payload) => {
    const payloadStr = payload.toString();
    getDb().prepare('UPDATE mqtt_topics SET last_payload = ? WHERE broker_id = ? AND topic = ?').run(payloadStr, id, topic);

    const topicRow = getDb().prepare(
      'SELECT * FROM mqtt_topics WHERE broker_id = ? AND topic = ? AND selected = 1'
    ).get(id, topic) as any;
    if (topicRow) {
      const dp = getDatapointByTopicId(topicRow.id);
      if (dp) {
        const value = extractValue(payloadStr, topicRow.json_path);
        if (value !== null) updateDatapointValue(dp.id, value);
      }
    }
  });

  connections.set(id, client);
}

export function disconnectBroker(id: number): void {
  const client = connections.get(id);
  if (client) { client.end(true); connections.delete(id); setStatus(id, 'disconnected'); }
}

export async function testBroker(data: { host: string; port: number; username?: string; password?: string; use_tls?: number }): Promise<{ success: boolean; error?: string }> {
  return new Promise((resolve) => {
    const protocol = data.use_tls ? 'mqtts' : 'mqtt';
    const client = mqtt.connect(`${protocol}://${data.host}:${data.port}`, {
      username: data.username || undefined, password: data.password || undefined, connectTimeout: 5000,
    });
    const timeout = setTimeout(() => { client.end(true); resolve({ success: false, error: 'Connection timeout' }); }, 5000);
    client.on('connect', () => { clearTimeout(timeout); client.end(true); resolve({ success: true }); });
    client.on('error', (err) => { clearTimeout(timeout); client.end(true); resolve({ success: false, error: err.message }); });
  });
}

export function connectAllEnabledBrokers(): void {
  const brokers = getDb().prepare('SELECT id FROM mqtt_brokers WHERE enabled = 1').all() as { id: number }[];
  for (const b of brokers) connectBroker(b.id);
}

export function getConnection(id: number): MqttClient | undefined {
  return connections.get(id);
}
```

- [ ] **Step 2: Commit**

```bash
git add middleware/src/modules/mqtt/broker-manager.ts
git commit -m "feat: add MQTT broker manager with connect/disconnect/test"
```

---

## Task 10: MQTT Topic Discovery

**Files:**
- Create: `middleware/src/modules/mqtt/topic-discovery.ts`

- [ ] **Step 1: Implement topic discovery**

```typescript
// middleware/src/modules/mqtt/topic-discovery.ts
import { getDb } from '../../db/index';
import { getConnection } from './broker-manager';

export interface MqttTopic {
  id: number; broker_id: number; topic: string; json_path: string | null;
  selected: number; ignored: number; last_payload: string | null; discovered_at: string;
}

export function getTopicsForBroker(brokerId: number): MqttTopic[] {
  return getDb().prepare('SELECT * FROM mqtt_topics WHERE broker_id = ? ORDER BY topic').all(brokerId) as MqttTopic[];
}

export function updateTopic(id: number, data: Partial<{ json_path: string; selected: boolean; ignored: boolean }>): void {
  const fields: string[] = [];
  const values: any[] = [];
  if (data.json_path !== undefined) { fields.push('json_path = ?'); values.push(data.json_path); }
  if (data.selected !== undefined) { fields.push('selected = ?'); values.push(data.selected ? 1 : 0); }
  if (data.ignored !== undefined) { fields.push('ignored = ?'); values.push(data.ignored ? 1 : 0); }
  if (fields.length === 0) return;
  values.push(id);
  getDb().prepare(`UPDATE mqtt_topics SET ${fields.join(', ')} WHERE id = ?`).run(...values);
}

export function startDiscovery(brokerId: number, prefix?: string): { success: boolean; error?: string } {
  const client = getConnection(brokerId);
  if (!client || !client.connected) return { success: false, error: 'Broker not connected' };

  const pattern = prefix ? `${prefix}#` : '#';
  client.subscribe(pattern);

  const handler = (topic: string, payload: Buffer) => {
    const existing = getDb().prepare('SELECT id FROM mqtt_topics WHERE broker_id = ? AND topic = ?').get(brokerId, topic) as any;
    if (!existing) {
      getDb().prepare('INSERT INTO mqtt_topics (broker_id, topic, last_payload) VALUES (?, ?, ?)').run(brokerId, topic, payload.toString());
    } else {
      getDb().prepare('UPDATE mqtt_topics SET last_payload = ? WHERE id = ?').run(payload.toString(), existing.id);
    }
  };

  client.on('message', handler);
  setTimeout(() => { client.unsubscribe(pattern); client.removeListener('message', handler); }, 30000);

  return { success: true };
}

export function addManualTopic(brokerId: number, topic: string): number {
  const result = getDb().prepare('INSERT INTO mqtt_topics (broker_id, topic) VALUES (?, ?)').run(brokerId, topic);
  return Number(result.lastInsertRowid);
}
```

- [ ] **Step 2: Commit**

```bash
git add middleware/src/modules/mqtt/topic-discovery.ts
git commit -m "feat: add MQTT topic discovery with prefix/wildcard support"
```

---

## Task 11: PostgreSQL Source Manager + Query Scheduler

**Files:**
- Create: `middleware/src/modules/postgres/source-manager.ts`
- Create: `middleware/src/modules/postgres/query-scheduler.ts`

- [ ] **Step 1: Implement source manager**

```typescript
// middleware/src/modules/postgres/source-manager.ts
import { Pool } from 'pg';
import { getDb } from '../../db/index';

const pools: Map<number, Pool> = new Map();

export interface PgSource {
  id: number; name: string; host: string; port: number; database_name: string;
  username: string; password: string; use_tls: number; enabled: number;
  status: string; created_at: string;
}

export function getAllSources(): PgSource[] {
  return getDb().prepare('SELECT * FROM pg_sources ORDER BY created_at DESC').all() as PgSource[];
}

export function getSourceById(id: number): PgSource | undefined {
  return getDb().prepare('SELECT * FROM pg_sources WHERE id = ?').get(id) as PgSource | undefined;
}

export function createSource(data: { name: string; host: string; port?: number; database_name: string; username: string; password: string; use_tls?: boolean }): number {
  const result = getDb().prepare(
    'INSERT INTO pg_sources (name, host, port, database_name, username, password, use_tls) VALUES (?, ?, ?, ?, ?, ?, ?)'
  ).run(data.name, data.host, data.port ?? 5432, data.database_name, data.username, data.password, data.use_tls ? 1 : 0);
  return Number(result.lastInsertRowid);
}

export function updateSource(id: number, data: Partial<{ name: string; host: string; port: number; database_name: string; username: string; password: string; use_tls: boolean; enabled: boolean }>): void {
  const fields: string[] = [];
  const values: any[] = [];
  if (data.name !== undefined) { fields.push('name = ?'); values.push(data.name); }
  if (data.host !== undefined) { fields.push('host = ?'); values.push(data.host); }
  if (data.port !== undefined) { fields.push('port = ?'); values.push(data.port); }
  if (data.database_name !== undefined) { fields.push('database_name = ?'); values.push(data.database_name); }
  if (data.username !== undefined) { fields.push('username = ?'); values.push(data.username); }
  if (data.password !== undefined) { fields.push('password = ?'); values.push(data.password); }
  if (data.use_tls !== undefined) { fields.push('use_tls = ?'); values.push(data.use_tls ? 1 : 0); }
  if (data.enabled !== undefined) { fields.push('enabled = ?'); values.push(data.enabled ? 1 : 0); }
  if (fields.length === 0) return;
  values.push(id);
  getDb().prepare(`UPDATE pg_sources SET ${fields.join(', ')} WHERE id = ?`).run(...values);
}

export function deleteSource(id: number): void {
  disconnectSource(id);
  getDb().prepare('DELETE FROM pg_sources WHERE id = ?').run(id);
}

export function connectSource(id: number): void {
  const source = getSourceById(id);
  if (!source || !source.enabled) return;
  if (pools.has(id)) disconnectSource(id);
  const pool = new Pool({
    host: source.host, port: source.port, database: source.database_name,
    user: source.username, password: source.password,
    ssl: source.use_tls ? { rejectUnauthorized: false } : false, max: 3,
  });
  pool.on('error', () => getDb().prepare('UPDATE pg_sources SET status = ? WHERE id = ?').run('error', id));
  pools.set(id, pool);
  getDb().prepare('UPDATE pg_sources SET status = ? WHERE id = ?').run('connected', id);
}

export function disconnectSource(id: number): void {
  const pool = pools.get(id);
  if (pool) { pool.end(); pools.delete(id); getDb().prepare('UPDATE pg_sources SET status = ? WHERE id = ?').run('disconnected', id); }
}

export function getPool(id: number): Pool | undefined { return pools.get(id); }

export async function testSource(data: { host: string; port: number; database_name: string; username: string; password: string; use_tls?: number }): Promise<{ success: boolean; error?: string }> {
  const pool = new Pool({
    host: data.host, port: data.port, database: data.database_name,
    user: data.username, password: data.password,
    ssl: data.use_tls ? { rejectUnauthorized: false } : false, connectionTimeoutMillis: 5000,
  });
  try {
    const client = await pool.connect(); await client.query('SELECT 1'); client.release(); await pool.end();
    return { success: true };
  } catch (err: any) { await pool.end(); return { success: false, error: err.message }; }
}

export function connectAllEnabledSources(): void {
  const sources = getDb().prepare('SELECT id FROM pg_sources WHERE enabled = 1').all() as { id: number }[];
  for (const s of sources) connectSource(s.id);
}
```

- [ ] **Step 2: Implement query scheduler**

```typescript
// middleware/src/modules/postgres/query-scheduler.ts
import { getDb } from '../../db/index';
import { getPool } from './source-manager';
import { updateDatapointValue, getDatapointByQueryId } from '../datapoints/datapoint-manager';

export interface PgQuery {
  id: number; source_id: number; name: string; query: string;
  interval_sec: number; result_column: string; last_result: string | null; last_run_at: string | null;
}

const timers: Map<number, NodeJS.Timeout> = new Map();

export function getAllQueries(sourceId: number): PgQuery[] {
  return getDb().prepare('SELECT * FROM pg_queries WHERE source_id = ?').all(sourceId) as PgQuery[];
}

export function getQueryById(id: number): PgQuery | undefined {
  return getDb().prepare('SELECT * FROM pg_queries WHERE id = ?').get(id) as PgQuery | undefined;
}

export function createQuery(data: { source_id: number; name: string; query: string; interval_sec?: number; result_column: string }): number {
  const result = getDb().prepare(
    'INSERT INTO pg_queries (source_id, name, query, interval_sec, result_column) VALUES (?, ?, ?, ?, ?)'
  ).run(data.source_id, data.name, data.query, data.interval_sec ?? 60, data.result_column);
  return Number(result.lastInsertRowid);
}

export function updateQuery(id: number, data: Partial<{ name: string; query: string; interval_sec: number; result_column: string }>): void {
  const fields: string[] = [];
  const values: any[] = [];
  if (data.name !== undefined) { fields.push('name = ?'); values.push(data.name); }
  if (data.query !== undefined) { fields.push('query = ?'); values.push(data.query); }
  if (data.interval_sec !== undefined) { fields.push('interval_sec = ?'); values.push(data.interval_sec); }
  if (data.result_column !== undefined) { fields.push('result_column = ?'); values.push(data.result_column); }
  if (fields.length === 0) return;
  values.push(id);
  getDb().prepare(`UPDATE pg_queries SET ${fields.join(', ')} WHERE id = ?`).run(...values);
}

export function deleteQuery(id: number): void {
  stopQueryTimer(id);
  getDb().prepare('DELETE FROM pg_queries WHERE id = ?').run(id);
}

async function executeQuery(queryId: number): Promise<void> {
  const q = getQueryById(queryId);
  if (!q) return;
  const pool = getPool(q.source_id);
  if (!pool) return;
  try {
    const result = await pool.query(q.query);
    if (result.rows.length > 0 && result.rows[0][q.result_column] !== undefined) {
      const value = String(result.rows[0][q.result_column]);
      getDb().prepare('UPDATE pg_queries SET last_result = ?, last_run_at = CURRENT_TIMESTAMP WHERE id = ?').run(value, queryId);
      const dp = getDatapointByQueryId(queryId);
      if (dp) updateDatapointValue(dp.id, value);
    }
  } catch (err) { console.error(`PG Query ${queryId} failed:`, err); }
}

export function startQueryTimer(queryId: number): void {
  const q = getQueryById(queryId);
  if (!q) return;
  stopQueryTimer(queryId);
  executeQuery(queryId);
  const timer = setInterval(() => executeQuery(queryId), q.interval_sec * 1000);
  timers.set(queryId, timer);
}

export function stopQueryTimer(queryId: number): void {
  const timer = timers.get(queryId);
  if (timer) { clearInterval(timer); timers.delete(queryId); }
}

export function startAllQueryTimers(): void {
  const queries = getDb().prepare(
    'SELECT pq.id FROM pg_queries pq JOIN pg_sources ps ON pq.source_id = ps.id WHERE ps.enabled = 1'
  ).all() as { id: number }[];
  for (const q of queries) startQueryTimer(q.id);
}
```

- [ ] **Step 3: Commit**

```bash
git add middleware/src/modules/postgres/
git commit -m "feat: add PostgreSQL source manager and query scheduler"
```

---

## Task 12: All API Routes

**Files:**
- Create: `middleware/src/api/routes/mqtt-brokers.ts`
- Create: `middleware/src/api/routes/mqtt-topics.ts`
- Create: `middleware/src/api/routes/pg-sources.ts`
- Create: `middleware/src/api/routes/datapoints.ts`
- Create: `middleware/src/api/routes/displays.ts`
- Create: `middleware/src/api/routes/layouts.ts`
- Create: `middleware/src/api/routes/display-api.ts`
- Create: `middleware/tests/api/display-api.test.ts`

- [ ] **Step 1: Create mqtt-brokers route**

```typescript
// middleware/src/api/routes/mqtt-brokers.ts
import { Router } from 'express';
import { getAllBrokers, getBrokerById, createBroker, updateBroker, deleteBroker, testBroker, connectBroker, disconnectBroker } from '../../modules/mqtt/broker-manager';

export const mqttBrokersRouter = Router();

mqttBrokersRouter.get('/', (_req, res) => res.json(getAllBrokers()));

mqttBrokersRouter.get('/:id', (req, res) => {
  const broker = getBrokerById(Number(req.params.id));
  if (!broker) { res.status(404).json({ error: 'Not found' }); return; }
  res.json(broker);
});

mqttBrokersRouter.post('/', (req, res) => {
  const { name, host, port, username, password, use_tls } = req.body;
  if (!name || !host) { res.status(400).json({ error: 'name and host required' }); return; }
  const id = createBroker({ name, host, port, username, password, use_tls });
  connectBroker(id);
  res.status(201).json({ id });
});

mqttBrokersRouter.put('/:id', (req, res) => {
  const id = Number(req.params.id);
  updateBroker(id, req.body);
  if (req.body.enabled !== undefined) { req.body.enabled ? connectBroker(id) : disconnectBroker(id); }
  res.json({ ok: true });
});

mqttBrokersRouter.delete('/:id', (req, res) => { deleteBroker(Number(req.params.id)); res.json({ ok: true }); });

mqttBrokersRouter.post('/:id/test', async (req, res) => {
  const broker = getBrokerById(Number(req.params.id));
  if (!broker) { res.status(404).json({ error: 'Not found' }); return; }
  const result = await testBroker(broker);
  res.json(result);
});
```

- [ ] **Step 2: Create mqtt-topics route**

```typescript
// middleware/src/api/routes/mqtt-topics.ts
import { Router } from 'express';
import { getTopicsForBroker, updateTopic, startDiscovery, addManualTopic } from '../../modules/mqtt/topic-discovery';
import { createDatapointFromMqtt } from '../../modules/datapoints/datapoint-manager';
import { getDb } from '../../db/index';
import { getConnection } from '../../modules/mqtt/broker-manager';

export const mqttTopicsRouter = Router();

mqttTopicsRouter.get('/brokers/:id/topics', (req, res) => res.json(getTopicsForBroker(Number(req.params.id))));

mqttTopicsRouter.put('/topics/:id', (req, res) => {
  const topicId = Number(req.params.id);
  const topic = getDb().prepare('SELECT * FROM mqtt_topics WHERE id = ?').get(topicId) as any;
  if (!topic) { res.status(404).json({ error: 'Not found' }); return; }
  updateTopic(topicId, req.body);
  if (req.body.selected === true) {
    const existing = getDb().prepare('SELECT id FROM datapoints WHERE topic_id = ?').get(topicId);
    if (!existing) createDatapointFromMqtt(topic.broker_id, topicId, topic.topic);
    const client = getConnection(topic.broker_id);
    if (client?.connected) client.subscribe(topic.topic);
  }
  res.json({ ok: true });
});

mqttTopicsRouter.post('/brokers/:id/discover', (req, res) => res.json(startDiscovery(Number(req.params.id), req.body.prefix)));

mqttTopicsRouter.post('/brokers/:id/topics', (req, res) => {
  const { topic } = req.body;
  if (!topic) { res.status(400).json({ error: 'topic required' }); return; }
  const id = addManualTopic(Number(req.params.id), topic);
  res.status(201).json({ id });
});
```

- [ ] **Step 3: Create pg-sources route**

```typescript
// middleware/src/api/routes/pg-sources.ts
import { Router } from 'express';
import { getAllSources, getSourceById, createSource, updateSource, deleteSource, testSource, connectSource } from '../../modules/postgres/source-manager';
import { getAllQueries, createQuery, updateQuery, deleteQuery, startQueryTimer } from '../../modules/postgres/query-scheduler';
import { createDatapointFromPg } from '../../modules/datapoints/datapoint-manager';

export const pgSourcesRouter = Router();

pgSourcesRouter.get('/', (_req, res) => res.json(getAllSources()));
pgSourcesRouter.get('/:id', (req, res) => {
  const source = getSourceById(Number(req.params.id));
  if (!source) { res.status(404).json({ error: 'Not found' }); return; }
  res.json(source);
});
pgSourcesRouter.post('/', (req, res) => {
  const { name, host, port, database_name, username, password, use_tls } = req.body;
  if (!name || !host || !database_name || !username || !password) { res.status(400).json({ error: 'Missing required fields' }); return; }
  const id = createSource({ name, host, port, database_name, username, password, use_tls });
  connectSource(id);
  res.status(201).json({ id });
});
pgSourcesRouter.put('/:id', (req, res) => { updateSource(Number(req.params.id), req.body); res.json({ ok: true }); });
pgSourcesRouter.delete('/:id', (req, res) => { deleteSource(Number(req.params.id)); res.json({ ok: true }); });
pgSourcesRouter.post('/:id/test', async (req, res) => {
  const source = getSourceById(Number(req.params.id));
  if (!source) { res.status(404).json({ error: 'Not found' }); return; }
  const result = await testSource(source);
  res.json(result);
});
pgSourcesRouter.get('/:id/queries', (req, res) => res.json(getAllQueries(Number(req.params.id))));
pgSourcesRouter.post('/:id/queries', (req, res) => {
  const sourceId = Number(req.params.id);
  const { name, query, interval_sec, result_column } = req.body;
  if (!name || !query || !result_column) { res.status(400).json({ error: 'name, query, result_column required' }); return; }
  const id = createQuery({ source_id: sourceId, name, query, interval_sec, result_column });
  createDatapointFromPg(sourceId, id, name);
  startQueryTimer(id);
  res.status(201).json({ id });
});
pgSourcesRouter.put('/queries/:id', (req, res) => { updateQuery(Number(req.params.id), req.body); res.json({ ok: true }); });
pgSourcesRouter.delete('/queries/:id', (req, res) => { deleteQuery(Number(req.params.id)); res.json({ ok: true }); });
```

- [ ] **Step 4: Create datapoints, displays, layouts, display-api routes**

```typescript
// middleware/src/api/routes/datapoints.ts
import { Router } from 'express';
import { getAllDatapoints, updateDatapoint } from '../../modules/datapoints/datapoint-manager';

export const datapointsRouter = Router();
datapointsRouter.get('/', (_req, res) => res.json(getAllDatapoints()));
datapointsRouter.put('/:id', (req, res) => { updateDatapoint(Number(req.params.id), req.body); res.json({ ok: true }); });
```

```typescript
// middleware/src/api/routes/displays.ts
import { Router } from 'express';
import { getDb } from '../../db/index';

export const displaysRouter = Router();
displaysRouter.get('/', (_req, res) => res.json(getDb().prepare('SELECT * FROM displays ORDER BY created_at DESC').all()));
displaysRouter.get('/:id', (req, res) => {
  const d = getDb().prepare('SELECT * FROM displays WHERE id = ?').get(Number(req.params.id));
  if (!d) { res.status(404).json({ error: 'Not found' }); return; }
  res.json(d);
});
displaysRouter.post('/', (req, res) => {
  const { name, description, layout_id, api_token, refresh_sec } = req.body;
  if (!name) { res.status(400).json({ error: 'name required' }); return; }
  const result = getDb().prepare('INSERT INTO displays (name, description, layout_id, api_token, refresh_sec) VALUES (?, ?, ?, ?, ?)').run(name, description || null, layout_id || null, api_token || null, refresh_sec || 300);
  res.status(201).json({ id: Number(result.lastInsertRowid) });
});
displaysRouter.put('/:id', (req, res) => {
  const fields: string[] = []; const values: any[] = [];
  for (const key of ['name', 'description', 'layout_id', 'api_token', 'refresh_sec']) {
    if (req.body[key] !== undefined) { fields.push(`${key} = ?`); values.push(req.body[key]); }
  }
  if (fields.length > 0) { values.push(Number(req.params.id)); getDb().prepare(`UPDATE displays SET ${fields.join(', ')} WHERE id = ?`).run(...values); }
  res.json({ ok: true });
});
displaysRouter.delete('/:id', (req, res) => { getDb().prepare('DELETE FROM displays WHERE id = ?').run(Number(req.params.id)); res.json({ ok: true }); });
```

```typescript
// middleware/src/api/routes/layouts.ts
import { Router } from 'express';
import { getAllLayouts, getLayoutById, createLayout, updateLayout, deleteLayout, addElement, updateElement, deleteElement } from '../../modules/display/layout-manager';

export const layoutsRouter = Router();
layoutsRouter.get('/', (_req, res) => res.json(getAllLayouts()));
layoutsRouter.get('/:id', (req, res) => { const l = getLayoutById(Number(req.params.id)); if (!l) { res.status(404).json({ error: 'Not found' }); return; } res.json(l); });
layoutsRouter.post('/', (req, res) => { const { name, type, width, height, grid_cols, grid_rows } = req.body; if (!name) { res.status(400).json({ error: 'name required' }); return; } const id = createLayout({ name, type: type || 'free', width, height, grid_cols, grid_rows }); res.status(201).json({ id }); });
layoutsRouter.put('/:id', (req, res) => { updateLayout(Number(req.params.id), req.body); res.json({ ok: true }); });
layoutsRouter.delete('/:id', (req, res) => { deleteLayout(Number(req.params.id)); res.json({ ok: true }); });
layoutsRouter.post('/:id/elements', (req, res) => {
  const data = { datapoint_id: req.body.datapoint_id || null, type: req.body.type || 'label', x: req.body.x || 0, y: req.body.y || 0, width: req.body.width || null, height: req.body.height || null, font_size: req.body.font_size || 24, font_weight: req.body.font_weight || 'normal', text_align: req.body.text_align || 'left', icon: req.body.icon || null, style: req.body.style || 'plain', static_text: req.body.static_text || null, grid_col: req.body.grid_col || null, grid_row: req.body.grid_row || null, sort_order: req.body.sort_order || 0 };
  const id = addElement(Number(req.params.id), data);
  res.status(201).json({ id });
});
layoutsRouter.put('/elements/:id', (req, res) => { updateElement(Number(req.params.id), req.body); res.json({ ok: true }); });
layoutsRouter.delete('/elements/:id', (req, res) => { deleteElement(Number(req.params.id)); res.json({ ok: true }); });
```

```typescript
// middleware/src/api/routes/display-api.ts
import { Router } from 'express';
import { buildDisplayJson } from '../../modules/display/display-builder';
import { getDb } from '../../db/index';

export const displayApiRouter = Router();
displayApiRouter.get('/:id', (req, res) => {
  const displayId = Number(req.params.id);
  const display = getDb().prepare('SELECT * FROM displays WHERE id = ?').get(displayId) as any;
  if (!display) { res.status(404).json({ error: 'Display not found' }); return; }
  if (display.api_token) { if (req.query.token !== display.api_token) { res.status(401).json({ error: 'Invalid token' }); return; } }
  const json = buildDisplayJson(displayId);
  if (!json) { res.status(404).json({ error: 'Display has no layout' }); return; }
  getDb().prepare('UPDATE displays SET last_seen = CURRENT_TIMESTAMP WHERE id = ?').run(displayId);
  res.json(json);
});
```

- [ ] **Step 5: Write display-api test**

```typescript
// middleware/tests/api/display-api.test.ts
import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import express from 'express';
import request from 'supertest';
import { initDb } from '../../src/db/index';
import { displayApiRouter } from '../../src/api/routes/display-api';
import Database from 'better-sqlite3';

describe('Display API (ESP32)', () => {
  let app: express.Express;
  let db: Database.Database;

  beforeAll(() => {
    db = initDb(':memory:');
    db.prepare(`INSERT INTO layouts (id, name, type) VALUES (1, 'Test', 'free')`).run();
    db.prepare(`INSERT INTO displays (id, name, layout_id, refresh_sec) VALUES (1, 'Test Display', 1, 300)`).run();
    db.prepare(`INSERT INTO displays (id, name, layout_id, api_token) VALUES (2, 'Secured', 1, 'secret123')`).run();
    db.prepare(`INSERT INTO layout_elements (layout_id, type, x, y, static_text, sort_order) VALUES (1, 'label', 400, 20, 'Hello', 0)`).run();
    app = express();
    app.use('/api/display', displayApiRouter);
  });

  afterAll(() => db.close());

  it('should return display JSON', async () => { const res = await request(app).get('/api/display/1'); expect(res.status).toBe(200); expect(res.body.display.name).toBe('Test Display'); });
  it('should reject without token for secured display', async () => { const res = await request(app).get('/api/display/2'); expect(res.status).toBe(401); });
  it('should allow with correct token', async () => { const res = await request(app).get('/api/display/2?token=secret123'); expect(res.status).toBe(200); });
  it('should return 404 for non-existent', async () => { const res = await request(app).get('/api/display/999'); expect(res.status).toBe(404); });
});
```

- [ ] **Step 6: Run tests**

Run: `cd middleware && npx vitest run`
Expected: All tests PASS

- [ ] **Step 7: Commit**

```bash
git add middleware/src/api/routes/ middleware/tests/api/display-api.test.ts
git commit -m "feat: add all REST API routes including ESP32 display endpoint"
```

---

## Task 13: Wire Up Express App + Entry Point

**Files:**
- Create: `middleware/src/api/index.ts`
- Modify: `middleware/src/index.ts`

- [ ] **Step 1: Create api/index.ts**

```typescript
// middleware/src/api/index.ts
import express from 'express';
import cors from 'cors';
import path from 'path';
import { authMiddleware } from './middleware/auth';
import { authRouter } from './routes/auth';
import { mqttBrokersRouter } from './routes/mqtt-brokers';
import { mqttTopicsRouter } from './routes/mqtt-topics';
import { pgSourcesRouter } from './routes/pg-sources';
import { datapointsRouter } from './routes/datapoints';
import { displaysRouter } from './routes/displays';
import { layoutsRouter } from './routes/layouts';
import { displayApiRouter } from './routes/display-api';

export function createApp(): express.Express {
  const app = express();
  app.use(cors());
  app.use(express.json());

  app.get('/api/health', (_req, res) => res.json({ status: 'ok', timestamp: new Date().toISOString() }));

  // Public routes
  app.use('/api/auth', authRouter);
  app.use('/api/display', displayApiRouter);

  // Protected routes
  app.use('/api/mqtt/brokers', authMiddleware, mqttBrokersRouter);
  app.use('/api/mqtt', authMiddleware, mqttTopicsRouter);
  app.use('/api/pg/sources', authMiddleware, pgSourcesRouter);
  app.use('/api/pg', authMiddleware, pgSourcesRouter);
  app.use('/api/datapoints', authMiddleware, datapointsRouter);
  app.use('/api/displays', authMiddleware, displaysRouter);
  app.use('/api/layouts', authMiddleware, layoutsRouter);

  // Serve React SPA in production
  const frontendPath = path.join(__dirname, '../../frontend/dist');
  app.use(express.static(frontendPath));
  app.get('*', (_req, res) => res.sendFile(path.join(frontendPath, 'index.html')));

  return app;
}
```

- [ ] **Step 2: Update src/index.ts**

```typescript
// middleware/src/index.ts
import { createApp } from './api/index';
import { initDb } from './db/index';
import { connectAllEnabledBrokers } from './modules/mqtt/broker-manager';
import { connectAllEnabledSources } from './modules/postgres/source-manager';
import { startAllQueryTimers } from './modules/postgres/query-scheduler';
import bcrypt from 'bcrypt';
import fs from 'fs';
import path from 'path';

const PORT = process.env.PORT || 4000;
const DB_PATH = process.env.DB_PATH || './data/epaper.db';

async function main() {
  const dataDir = path.dirname(DB_PATH);
  if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir, { recursive: true });

  const db = initDb(DB_PATH);

  const userCount = db.prepare('SELECT COUNT(*) as count FROM users').get() as { count: number };
  if (userCount.count === 0) {
    const hash = await bcrypt.hash('admin', 10);
    db.prepare('INSERT INTO users (username, password_hash) VALUES (?, ?)').run('admin', hash);
    console.log('Default admin user created (username: admin, password: admin)');
  }

  connectAllEnabledBrokers();
  connectAllEnabledSources();
  startAllQueryTimers();

  const app = createApp();
  app.listen(PORT, () => console.log(`EPaper Middleware running on http://localhost:${PORT}`));
}

main().catch(console.error);
```

- [ ] **Step 3: Run all tests**

Run: `cd middleware && npx vitest run`
Expected: All tests PASS

- [ ] **Step 4: Commit**

```bash
git add middleware/src/api/index.ts middleware/src/index.ts
git commit -m "feat: wire up Express app with all routes and module initialization"
```

---

## Task 14: Frontend Scaffolding (React + Vite)

**Files:**
- Create: `frontend/` (via Vite scaffold)
- Create: `frontend/src/api/client.ts`
- Create: `frontend/src/App.tsx`
- Create: `frontend/src/components/Sidebar.tsx`
- Create: `frontend/src/pages/Login.tsx`

- [ ] **Step 1: Scaffold React + Vite project**

Run: `cd display && npm create vite@latest frontend -- --template react-ts`
Then: `cd frontend && npm install react-router-dom`
Then: `cd frontend && npm install -D tailwindcss @tailwindcss/vite`

- [ ] **Step 2: Configure vite.config.ts with proxy**

```typescript
// frontend/vite.config.ts
import { defineConfig } from 'vite';
import react from '@vitejs/plugin-react';
import tailwindcss from '@tailwindcss/vite';

export default defineConfig({
  plugins: [react(), tailwindcss()],
  server: { port: 3000, proxy: { '/api': 'http://localhost:4000' } },
});
```

- [ ] **Step 3: Create API client, App.tsx, Sidebar, Login**

Create files as specified in the File Structure section. The API client wraps fetch with JWT token handling. App.tsx sets up React Router with protected routes. Sidebar has navigation links. Login page authenticates against `/api/auth/login`.

See Task 14 in the spec for full code of each file:
- `frontend/src/api/client.ts` — fetch wrapper with JWT
- `frontend/src/App.tsx` — router with ProtectedRoute wrapper
- `frontend/src/components/Sidebar.tsx` — navigation sidebar
- `frontend/src/pages/Login.tsx` — login form
- `frontend/src/main.tsx` — React entry point
- `frontend/src/index.css` — Tailwind import

- [ ] **Step 4: Verify frontend builds**

Run: `cd frontend && npm run build`
Expected: `dist/` folder created

- [ ] **Step 5: Commit**

```bash
git add frontend/
git commit -m "feat: scaffold React frontend with routing, auth, and sidebar"
```

---

## Task 15: Frontend Pages

**Files:**
- Create: `frontend/src/pages/Dashboard.tsx`
- Create: `frontend/src/pages/MqttBrokers.tsx`
- Create: `frontend/src/pages/MqttTopics.tsx`
- Create: `frontend/src/pages/PgSources.tsx`
- Create: `frontend/src/pages/Datapoints.tsx`
- Create: `frontend/src/pages/Displays.tsx`
- Create: `frontend/src/pages/Layouts.tsx`
- Create: `frontend/src/components/StatusBadge.tsx`
- Create: `frontend/src/components/EpaperPreview.tsx`

Each page follows the same CRUD pattern: fetch from API, display in table/cards, add/edit/delete forms. The full code for each page is documented in the spec's brainstorming mockups.

- [ ] **Step 1: Create StatusBadge and EpaperPreview components**
- [ ] **Step 2: Create Dashboard page** (status tiles, datapoint table)
- [ ] **Step 3: Create MqttBrokers page** (CRUD, test connection)
- [ ] **Step 4: Create MqttTopics page** (checkbox list, discovery, payload preview)
- [ ] **Step 5: Create PgSources page** (CRUD for connections + queries)
- [ ] **Step 6: Create Datapoints page** (inline edit label/unit)
- [ ] **Step 7: Create Displays page** (CRUD, layout assignment)
- [ ] **Step 8: Create Layouts page** (element editor, preview)
- [ ] **Step 9: Verify frontend builds**

Run: `cd frontend && npm run build`
Expected: Successful build

- [ ] **Step 10: Commit**

```bash
git add frontend/src/
git commit -m "feat: add all frontend pages with CRUD and e-paper preview"
```

---

## Task 16: ESP32 Firmware

**Files:**
- Create: `esp32/platformio.ini`
- Create: `esp32/src/main.cpp`
- Create: `esp32/include/config.h`
- Create: `esp32/include/renderer.h`
- Create: `esp32/include/icons.h`
- Create: `esp32/include/captive_portal.h`

- [ ] **Step 1: Create platformio.ini**

```ini
[env:esp32]
platform = espressif32
board = esp32dev
framework = arduino
monitor_speed = 115200
lib_deps =
    zinggjm/GxEPD2@^1.5.8
    bblanchon/ArduinoJson@^7.2.0
    adafruit/Adafruit GFX Library@^1.11.10
    adafruit/Adafruit BusIO@^1.16.1
board_build.partitions = default.csv
build_flags = -DBOARD_HAS_PSRAM
```

- [ ] **Step 2: Create config.h** — WiFi/API configuration stored in NVS (Preferences)
- [ ] **Step 3: Create icons.h** — 12 monochrome 24x24 bitmaps (thermometer, droplet, wind, pressure, sun, bolt, gauge, wifi, battery, check, warning, clock)
- [ ] **Step 4: Create renderer.h** — renderElements() function that iterates elements array and draws text/lines/rects/icons using GxEPD2
- [ ] **Step 5: Create captive_portal.h** — WiFi AP "EPaper-Setup" with web config form for SSID, password, API URL, display ID, token
- [ ] **Step 6: Create main.cpp** — Boot → WiFi connect (or captive portal) → HTTP GET → JSON parse → render → deep sleep

- [ ] **Step 7: Verify compilation**

Run: `cd esp32 && pio run`
Expected: Successful compilation

- [ ] **Step 8: Commit**

```bash
git add esp32/
git commit -m "feat: add ESP32 firmware with WiFi, API fetch, e-paper rendering, and captive portal"
```

---

## Task 17: Integration Test + .gitignore

**Files:**
- Modify: `.gitignore`

- [ ] **Step 1: Update .gitignore**

```
node_modules/
dist/
data/
.env
config/project.env
.superpowers/
```

- [ ] **Step 2: Build and start full system**

Run: `cd frontend && npm run build`
Run: `cd middleware && npx tsx src/index.ts`
Test: Open http://localhost:4000, login admin/admin, verify all pages work

- [ ] **Step 3: Test ESP32 endpoint**

Run: `curl http://localhost:4000/api/health`
Expected: `{"status":"ok",...}`

- [ ] **Step 4: Commit**

```bash
git add .gitignore
git commit -m "chore: update gitignore and verify full system integration"
```

---

## Task 18: Deploy to Proxmox LXC

- [ ] **Step 1: Configure project.env** with Proxmox credentials
- [ ] **Step 2: Create LXC container** via `/proxmox-lxc` skill
- [ ] **Step 3: Push to Gitea** via `/git-push` skill
- [ ] **Step 4: Deploy via SSH** — clone, npm install, npm build, create systemd service
- [ ] **Step 5: Verify deployment** at `http://<LXC-IP>:4000`