# RecoTurbo Development Platform — AI Reference Guide

> **Purpose:** This document gives an AI assistant everything it needs to work effectively on the RecoTurbo self-hosted development platform — understanding the architecture, creating projects and sections, writing code that fits established patterns, and avoiding known pitfalls.

---

> **Document layout — read before editing:** Sections are numbered sequentially (`## N. Title`) in the order they were added, oldest first — do not renumber existing sections. Add new sections **above** the `*Last updated: ...*` line at the very bottom of this file, never below it. Each new section should include an `— added <Day> <Month> <Year>` (or a precise `HH:MM` if multiple changes land the same day) suffix in its heading, matching the style already used throughout (e.g. `## 30. Timeline Backup System (\`/opt/timeline/\`) — added 22nd July 2026 16:00`). When a documented feature is later corrected or superseded, prefer editing the existing section in place (marking superseded content with `~~strikethrough~~ — Fixed (Month Year)` per the existing convention in §21/§23) over creating a duplicate section. Always update the `*Last updated*` line at the bottom whenever any edit is made, however small.

## 1. Platform Overview

RecoTurbo runs a self-hosted web application platform on a Fasthosts Ubuntu VPS (`217.154.40.250`). nginx handles SSL termination and routing. Each project is a separate Node.js application with its own port, systemd service, Airtable base, and subdomain. Projects can have sub-pages called **sections**, served as URL paths under the parent project's domain.

The **dev-portal** (`dev.recoturbo.co.uk`) is the management UI — it handles project creation, file management, credentials, team access, and task tracking. It is itself a project on the platform.

### Key URLs

| Service | URL | Port | Notes |
|---|---|---|---|
| Dev Portal | `dev.recoturbo.co.uk` | 3001 | Management UI |
| Internal Portal | `internal.recoturbo.co.uk` | 3002 | Internal staff apps |
| Project N | `<slug>.recoturbo.co.uk` | 3003+ | New projects |

### Key Paths on VPS

```
/opt/dev-portal/          ← dev-portal app (management UI)
/opt/dev-portal/public/docs/recoturbo-platform-guide.md  ← this guide
/opt/dev-portal/backend/lib/credentialHelpers.js  ← shared runtime credential reader
/opt/projects/            ← all other projects live here
  <project-slug>/
    .env                  ← project credentials (written by dev-portal)
    backend/
      server.js           ← Node.js backend
      lib/                ← backend modules
      node_modules/
      package.json
    public/
      index.html          ← frontend SPA entry point
    docs/
    <section-slug>/       ← section subfolders (if any)
      public/
        index.html
      backend/
      docs/

/etc/nginx/sites-available/   ← nginx configs (one file per domain)
/etc/nginx/sites-enabled/     ← symlinks to active configs
/etc/letsencrypt/             ← SSL certificates (managed by certbot)
/usr/local/bin/               ← custom root-owned scripts
```

### SSH Access

```
Host: web.recoturbo.co.uk (217.154.40.250)
Port: 2222
User: root
Auth: key-based (from home machines) or password (from office)
```

Password auth is enabled (`KbdInteractiveAuthentication yes`). Key-based auth uses `id_ed25519`.

---

## 2. Airtable Schema

### Dev-Portal Base (`appa3tRnK9UZ4H9st`)

All dev-portal management data lives here. The token is stored in `/opt/dev-portal/.env`.

#### DevUsers
Stores development team members who can log in to dev-portal.

| Field | Type | Notes |
|---|---|---|
| Name | Text | Display name |
| Email | Email | Login identifier |
| PasswordHash | Text | SHA-256 double hash |
| PasswordSalt | Text | base64url random 24 bytes |
| PasswordSetDate | Date | Used for 6-month expiry check |
| MustChangePassword | Checkbox | Forces change on next login |
| Role | Single select | Admin, Developer |
| Status | Single select | Active, Inactive |
| LastLogin | Date | Updated on each login |

#### Projects
Stores both projects AND sections (sections have `ParentProject` populated).

| Field | Type | Notes |
|---|---|---|
| Name | Text | Project or section name |
| Slug | Text | URL path segment (sections only) |
| Description | Text | |
| URL | Text | Full URL including `https://` |
| DeployPath | Text | Absolute path on VPS e.g. `/opt/projects/my-app` |
| RestartCommand | Text | systemd service name |
| Status | Single select | Active, Paused, Archived |
| Owner | Linked → DevUsers | |
| TeamMembers | Linked → DevUsers | |
| ParentProject | Linked → Projects | Populated for sections only |
| TechStack | Multi-select | |
| GitRepo | Text | |
| CreatedDate | Date | |
| UpdatedDate | Date | |

#### ProjectAccess
Controls which DevUsers can access which projects/sections in dev-portal.

| Field | Type | Notes |
|---|---|---|
| Name | Text | Auto: `{User} - {Project}` |
| DevUser | Linked → DevUsers | |
| Project | Linked → Projects | Also used for sections |
| Role | Single select | Lead, Developer, Reviewer |
| AccessLevel | Single select | Read, ReadWrite |
| AssignedDate | Date | |

#### Credentials
Encrypted key-value store for project secrets.

| Field | Type | Notes |
|---|---|---|
| KeyName | Text | e.g. `AIRTABLE_TOKEN`, `AIRTABLE_BASE_ID` |
| Value | Text | AES-256-GCM encrypted |
| Project | Linked → Projects | |
| IsSecret | Checkbox | |
| UpdatedBy | Text | Email string (NOT linked) |
| UpdatedDate | Date | |

**Encryption format:** `base64(iv):base64(authTag):base64(ciphertext)` — all colon-separated.

**Write .env File:** dev-portal has a button that decrypts all credentials for a project and writes them to `{DeployPath}/.env`. Always use this to generate `.env` files.

### Shared Credentials Helper (added July 2026)

The Airtable **Credentials** table is the source of truth for project credentials. Runtime code must use the shared helper at:

```text
/opt/dev-portal/backend/lib/credentialHelpers.js
```

Do not duplicate credential-reading or decryption code into individual projects. Existing crypto functions in this file remain the compatibility layer for projects already using them; the project-aware reader is exposed as:

```javascript
const { getDevPortalCredential } = require('/opt/dev-portal/backend/lib/credentialHelpers');

const value = await getDevPortalCredential('KEY_NAME');
```

This is a **read-only** operation. It never creates, edits, or deletes records in Airtable's Credentials table.

#### Lookup flow

`getDevPortalCredential(keyName)` determines the project making the request from the requesting module's path, then resolves the credential by both its project link and `KeyName`.

1. Identify the calling project's `DeployPath` and matching Airtable **Projects** record.
2. Find the requested `KeyName` linked to that project.
3. If found, return it. When `IsSecret` is enabled, decrypt `Value` with AES-256-GCM **in memory only** before returning it.
4. If the calling project has no matching key, search other projects for the same `KeyName`.
5. Use that fallback only when exactly one match exists. If none exist, return a clear not-found error; if more than one exists, return an ambiguity error and do not guess.

This makes project-specific credentials safe while still allowing one genuinely shared credential to be reused.

```text
Project code requests KEY_NAME
        ↓
Calling project / DeployPath matched first
        ↓
Credential for that Project + KeyName found? ── yes → decrypt in memory if secret → return value
        │
        no
        ↓
Exactly one matching KeyName in another project? ── yes → decrypt in memory if secret → return value
        │
        no / more than one
        ↓
Clear not-found or ambiguity error; never choose a credential silently
```

#### Rules for projects

- Store a credential in the Airtable Credentials table with the correct `Project` link and `KeyName`; do not put runtime secrets in source code, scheduler command lines, or logs.
- Give the same key a project-specific value when that project owns it. The helper will prefer it over a shared fallback.
- If a key is intended to be shared, ensure only one fallback copy exists across the other projects; duplicate fallback keys make the lookup ambiguous by design.
- The helper returns a value to the requesting process only. It must not print a credential value in errors, audit output, or logs.
- `.env` files remain static snapshots created by **Write .env File**. They are not a replacement for a runtime helper lookup, and updating a registry credential does not change an already-running process until the relevant configuration/service workflow has been applied.

#### Schedules integration (updated 22 July 2026)

Schedules (`/opt/projects/schedules/backend`) uses the central helper directly — there is no local wrapper file. `airtableSchemaRoute.js` imports it as:

const { getDevPortalCredential } = require('/opt/dev-portal/backend/lib/credentialHelpers');

**There is no `devPortalCredentialReader.js`.** An earlier version of this guide described a local compatibility module at that path forwarding to the shared helper — that file was removed (22 July 2026) once confirmed to be a single call site with no other dependents; the import was pointed straight at `credentialHelpers.js` instead. If a similar-looking file reappears in Schedules, don't assume it's load-bearing — check for actual `require()` references before treating it as intentional.

Credentials Schedules resolves this way include `FTP_HOST`, `FTP_USER`, `FTP_PASS`, and `AIRTABLE_561INFOs_BASE_ID`, matched against Schedules' own linked Credentials records first, falling back to a shared credential only if exactly one match exists elsewhere (see lookup flow above).

**Open question, not yet resolved:** this helper is a Node.js module (`require()`-based), reachable only from Schedules' own Node backend. Python task scripts under `reco-scripts/scheduled/` do not yet have a confirmed, documented mechanism for resolving these same credentials — verify and document the actual mechanism here before any new Python task is written to depend on it.

#### Tasks (`tblxSoiuH5I3wSCtd`)
Stores tasks for the dev-portal Board feature.

| Field | Type | Notes |
|---|---|---|
| Title | Text | Task name |
| AssignedTo | Linked → DevUsers | Multiple allowed |
| Description | Long text | Brief description |
| Priority | Single select | Low, Medium, High, Critical |
| Status | Single select | Unassigned, To Do, In Progress, Waiting, Complete |
| AssignedBy | Text | Email of creator (plain text, NOT linked) |
| AssignedDate | Date | When created |
| DueDate | Date | Optional deadline |
| Notes | Long text | Running notes/updates |
| UpdatedDate | Date | Last modified |
| Project | Linked → Projects | Optional — links task to a project or section |

**Access rules:**
- Admins can create, edit all fields, and delete any task
- Non-admins can only edit `Status` and `Notes` on tasks assigned to them or created by them

---

### Internal Portal Base (`appr6BKBHT19L02Yy`)

Separate Airtable base for the internal portal. Has its own dedicated Airtable token.

#### InternalUsers (`tblZGIZlnARdeaBHW`)
Staff who can log into `internal.recoturbo.co.uk`.

| Field | Type | Notes |
|---|---|---|
| Name | Text | Display name |
| Email | Text | Login identifier |
| PasswordHash | Text | Same algorithm as DevUsers |
| PasswordSalt | Text | |
| PasswordSetDate | Date | |
| MustChangePassword | Checkbox | |
| Role | Single select | Admin, User |
| Status | Single select | Active, Inactive |
| LastLogin | Date | |

#### InternalSectionAccess (`tblh7cSf7ibGkOlVn`)
Controls which users see which sections on the internal portal dashboard.

| Field | Type | Notes |
|---|---|---|
| Name | Text | e.g. `Geoff - e-learning` |
| User | Linked → InternalUsers | |
| SectionSlug | Text | Plain text slug e.g. `e-learning` (NOT linked — cross-base linking not supported) |
| Role | Single select | Admin, Trainer, Trainee, Viewer |
| AssignedDate | Date | |

#### Sections (`tblORQG2QgqMSrUJh`)
Global config table — defines display order and metadata for all sections.

| Field | Type | Notes |
|---|---|---|
| SectionSlug | Text | Matches `SectionSlug` in `InternalSectionAccess` |
| Label | Text | Display name e.g. `E-Learning` |
| Icon | Text | Emoji e.g. `🎓` |
| Description | Text | Short description shown on dashboard card |
| Index | Number | Sort order (lower = first) |

**Important:** When adding a new section to the internal portal, always add a record to this table. The dashboard reads order and metadata from here — not from the access records.

---

## 3. Password Hashing

All passwords across all portal services use the same algorithm:

```javascript
function hashPassword(password, salt) {
  const inner = sha256Hex(password);          // SHA-256 of password
  return sha256Hex(inner + salt);             // SHA-256 of (hash + salt)
}
```

Salt is generated as: `crypto.randomBytes(24).toString('base64url')`

To set a password manually on the VPS:

```bash
node -e "
const crypto = require('crypto');
const password = 'YourPassword';
const salt = crypto.randomBytes(24).toString('base64url');
const inner = crypto.createHash('sha256').update(password, 'utf8').digest('hex');
const hash = crypto.createHash('sha256').update(inner + salt, 'utf8').digest('hex');
console.log('Salt:', salt);
console.log('Hash:', hash);
"
```

Then update the Airtable record directly via API or the Airtable UI.

---

## 4. Creating a New Project

### What dev-portal automates:
1. Creates `/opt/projects/<slug>/backend/`, `public/`, `docs/` folders
2. Writes a personalised Under Construction `index.html` to `public/`
3. Creates Airtable records: Projects, ProjectAccess, Credentials
4. Runs the provisioning script to create nginx config + certbot SSL cert

### What requires manual setup:
- **DNS:** Create an A record pointing `<subdomain>.recoturbo.co.uk` (or other domain) to `217.154.40.250` **before** creating the project — the provisioning script needs DNS to be live for certbot's HTTP-01 challenge to succeed
- **Airtable base:** Create a new Airtable base for the project, note the base ID
- **Airtable token:** Use the existing dev-portal token (it works across all bases in the workspace) or create a dedicated one scoped to just this base
- **Backend service:** If the project needs a Node.js backend (not just static files), create a systemd service manually after scaffolding

### Domain provisioning script
Located at `/usr/local/bin/recoturbo-provision-domain.sh`. Called automatically by dev-portal on project creation. Can be called manually:

```bash
sudo /usr/local/bin/recoturbo-provision-domain.sh <domain> <docroot>
# Example:
sudo /usr/local/bin/recoturbo-provision-domain.sh myapp.recoturbo.co.uk /opt/projects/my-app
```

The script:
- Validates domain format and docroot path (must be under `/opt/projects/`)
- Writes a temporary HTTP nginx config
- Runs certbot (`--nginx` flag) to get SSL cert and update the config
- Verifies nginx config with `nginx -t`
- Auto-detects `public/` subfolder if bare project path is passed

**Important:** The script uses `systemctl restart nginx` (not reload) — reload is unreliable on this server.

### nginx config pattern for a project with Node.js backend

```nginx
upstream my_app {
    server 127.0.0.1:3003;
}

server {
    listen 80;
    server_name myapp.recoturbo.co.uk;
    return 301 https://$host$request_uri;
}

server {
    listen 443 ssl;
    server_name myapp.recoturbo.co.uk;

    location / {
        proxy_pass http://my_app;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection $connection_upgrade;
        proxy_set_header Host $host;
        proxy_cache_bypass $http_upgrade;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_read_timeout 180s;
        proxy_connect_timeout 180s;
        proxy_send_timeout 180s;
        proxy_next_upstream off;
    }

    client_max_body_size 20M;

    ssl_certificate /etc/letsencrypt/live/myapp.recoturbo.co.uk/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/myapp.recoturbo.co.uk/privkey.pem;
    include /etc/letsencrypt/options-ssl-nginx.conf;
    ssl_dhparam /etc/letsencrypt/ssl-dhparams.pem;
}
```

The `$connection_upgrade` variable is defined in `/etc/nginx/nginx.conf`:
```nginx
map $http_upgrade $connection_upgrade {
    default upgrade;
    ''      "";
}
```

### systemd service pattern

```ini
[Unit]
Description=My App Node.js Application
After=network.target

[Service]
Type=simple
User=www-data
Group=www-data
WorkingDirectory=/opt/projects/my-app
ExecStart=/usr/bin/node /opt/projects/my-app/backend/server.js
Restart=on-failure
RestartSec=5
Environment=NODE_ENV=production
StandardOutput=append:/opt/projects/my-app/logs/app.log
StandardError=append:/opt/projects/my-app/logs/error.log

[Install]
WantedBy=multi-user.target
```

Enable and start:
```bash
sudo mkdir -p /opt/projects/my-app/logs
sudo chown www-data:www-data /opt/projects/my-app/logs
sudo systemctl daemon-reload
sudo systemctl enable my-app
sudo systemctl start my-app
```

---

## 5. Creating a New Section

A section is a sub-page under an existing project's domain, served at `/<slug>/`. It is stored as a child record in the Projects table (`ParentProject` field populated).

### What dev-portal automates:
1. Creates `<project-path>/<slug>/backend/`, `public/`, `docs/` folders
2. Writes a personalised Under Construction `index.html` to `<slug>/public/`
3. Creates Airtable records: Projects (child), ProjectAccess, Credentials
4. Adds a `location /<slug>/` block to the parent project's nginx config
5. Adds a `location = /<slug>` redirect (no trailing slash → trailing slash)
6. Reloads nginx

### nginx location block pattern for a section (static files)

```nginx
location = /sales { return 301 /sales/; }
location /sales/ {
    alias /opt/projects/my-app/sales/public/;
    index index.html;
    try_files $uri $uri/ =404;
}
```

**Important:** Use `alias` not `root` for sections. `alias` maps the URL path to the filesystem path correctly. Always include the trailing slash in the alias path.

### For sections with their own Node.js backend

If a section needs server-side logic, run it on a separate port and add a `location` block that proxies to it:

```nginx
location /sales/ {
    proxy_pass http://127.0.0.1:3004/;
    proxy_http_version 1.1;
    ...standard proxy headers...
}
```

### Section Airtable fields (in Projects table)
- `ParentProject` — linked to the parent project record
- `Slug` — the URL segment (e.g. `sales`)
- `URL` — full URL with trailing slash (e.g. `https://myapp.recoturbo.co.uk/sales/`)
- `DeployPath` — full path (e.g. `/opt/projects/my-app/sales`)

---

## 6. Authentication Patterns

### Dev-portal
Single user base (`DevUsers`), JWT cookie `jwtToken`. Roles: Admin, Developer.

### Internal Portal — Single Sign-On
The internal portal uses a **single JWT cookie** (`internalToken`) that covers all sections under `internal.recoturbo.co.uk`. There is no separate login per section.

- User logs into `internal.recoturbo.co.uk` once
- JWT is issued against `InternalUsers` table
- When accessing a section (e.g. `/e-learning/`), the section SPA calls `/api/user` and `/api/sections` to verify the session and get the user's role for that section
- Role comes from `InternalSectionAccess.Role` for the matching `SectionSlug`
- If no valid `internalToken` cookie exists, the section redirects to `internal.recoturbo.co.uk` (the login page)

**Role values per section:** Admin, Trainer, Trainee, Viewer — the section UI adapts based on this role.

| Service | User Table | Cookie Name | JWT Secret env var |
|---|---|---|---|
| Dev Portal | DevUsers (dev-portal base) | `jwtToken` | `JWT_SECRET` |
| Internal Portal | InternalUsers (internal base) | `internalToken` | `JWT_SECRET` |
| New project | Own users table | `<project>Token` | `JWT_SECRET` |

### Standard auth middleware pattern (Node.js)

```javascript
function verifyAuth(req, res, next) {
  const token = req.cookies.myProjectToken;
  if (!token) return res.status(401).json({ error: 'Not authenticated' });
  try {
    const decoded = jwt.verify(token, JWT_SECRET);
    req.userEmail = decoded.sub;
    next();
  } catch (err) {
    return res.status(401).json({ error: 'Invalid or expired session' });
  }
}
```

### User cache pattern

Always cache Airtable user lookups to avoid repeated API calls:

```javascript
const _userCache = new Map();
const USER_CACHE_TTL = 5 * 60 * 1000; // 5 minutes

async function getUserByEmailCached(email) {
  const cached = _userCache.get(email);
  if (cached && Date.now() - cached.ts < USER_CACHE_TTL) return cached.record;
  const record = await getUserByEmail(email);
  if (record) _userCache.set(email, { record, ts: Date.now() });
  return record;
}
```

**Invalidating the cache from another file:** `_userCache` is intentionally module-private inside `shared/core.js` — code in `server.js` that changes a user's record (password reset, status/role change) needs to invalidate the relevant cache entry, but can't reach into another module's private variable. Two functions are exported from `shared/core.js` specifically for this: `invalidateUserCacheByEmail(email)` and `invalidateUserCacheById(userId)`. **Do not** reference `_userCache` directly from `server.js` — it isn't exported and doing so throws `_userCache is not defined` at runtime (this was a real, shipped bug for some time before being caught — the affected code paths, admin password reset and admin status/role changes, would throw on every use).

---

## 7. Internal Portal Architecture

### Overview
`internal.recoturbo.co.uk` (port 3002) is a Node.js/Express app serving a SPA frontend. It provides:
- Login, change password, dashboard
- Section cards (ordered by `Sections.Index`, filtered by `InternalSectionAccess`)
- Admin UI (users management + section reordering) — visible only to `Role: Admin`

### Admin card
The admin card (⚙️) is **auto-generated** in code — not stored in Airtable. It appears at position 0 on the dashboard for any user with `InternalUsers.Role = Admin`. It opens the admin view which has two tabs: Users and Sections.

### Users tab
- Lists all `InternalUsers` records
- Add User — creates record with `MustChangePassword: true`
- Reset Password — sets new temp password, enables `MustChangePassword`
- Activate/Deactivate — toggles `Status` field

### Sections tab
- Drag and drop reordering of sections
- Updates `Sections.Index` field in Airtable via `PATCH /api/admin/sections/reorder`
- Save Order button appears after any drag

### Adding a new section to the internal portal
After creating the section via dev-portal:
1. Add a record to the `Sections` table: `SectionSlug`, `Label`, `Icon`, `Description`, `Index`
2. Add `InternalSectionAccess` records for each user who should see it
3. The section will appear on their dashboard automatically

### Section SSO check pattern
Every section SPA under internal portal should start with:

```javascript
document.addEventListener('DOMContentLoaded', async () => {
  try {
    const res = await fetch('/api/user');
    if (!res.ok) {
      window.location.href = '/'; // redirect to internal portal login
      return;
    }
    const user = await res.json();

    // Get role for this section
    const sectRes = await fetch('/api/sections');
    const sectData = await sectRes.json();
    const thisSection = (sectData.sections || []).find(s => s.slug === 'my-section-slug');
    const userRole = thisSection ? thisSection.role.toLowerCase() : null;

    if (!userRole) {
      // Show access denied
      return;
    }
    // Continue loading section UI based on userRole
  } catch (err) {
    window.location.href = '/';
  }
});
```

---

## 8. Dev-Portal Board (Task Tracker)

### Overview
Accessible via the **Board** tab in dev-portal nav. A kanban-style task board with 5 columns: Unassigned, To Do, In Progress, Waiting, Complete.

### Features
- **Filters:** Status (default: Incomplete), Priority, Assignee
- **Incomplete filter** hides the Complete column entirely
- **Cards** show: title, description, priority badge, assignees, assigned date, due date (colour-coded), project link
- **Card sort:** Critical → High → Medium → Low, then earliest due date first
- **Project link** on a card opens the project workspace directly
- **Admins:** can create, edit all fields, delete tasks
- **Non-admins:** can edit Status and Notes on their own tasks only

### API endpoints

| Method | Endpoint | Access | Description |
|---|---|---|---|
| GET | `/api/tasks` | All | List tasks (admins see all, others see assigned/created) |
| POST | `/api/tasks` | Admin only | Create task |
| PATCH | `/api/tasks/:id` | Admin (all fields) / User (status+notes) | Update task |
| DELETE | `/api/tasks/:id` | Admin only | Delete task |

### Important: Date fields
Never send an empty string for `DueDate` — Airtable rejects it with a 422 error. Only include `DueDate` in the PATCH/POST body if it has a value:

```javascript
if (dueDate) fields.DueDate = dueDate; // correct
fields.DueDate = dueDate;              // wrong if dueDate is ''
```

### AssignedDate / UpdatedDate now include time (added July 2026)
`AssignedDate` and `UpdatedDate` are set via `new Date().toISOString()` (full datetime) rather than `.split('T')[0]` (date-only). Frontend formats these explicitly to `dd/mm/yy` (due date) and `dd/mm/yy : hh:mm` (assigned date) via `formatDateDDMMYY()`/`formatAssignedDateTime()` — deliberately not `toLocaleDateString()`, which isn't guaranteed to produce a consistent format across browsers/locales. Date-only strings (`YYYY-MM-DD`, e.g. from `<input type="date">`) are parsed manually rather than via `new Date(str)`, to avoid the classic gotcha where that's parsed as UTC midnight and can display as the previous day in negative-UTC-offset timezones.

**Critical gotcha (bit us in production):** sending a full datetime from the API does nothing if the Airtable field itself is still configured as a plain **Date** field without "Include a time field" turned on — Airtable silently truncates to date-only at the field level, before the value is even stored, regardless of what the payload contains. Both `AssignedDate` and `UpdatedDate` need this option enabled in Airtable's own field settings for the time to actually persist. Missing this on just one of the two fields (`UpdatedDate`, in our case) broke every card save silently — the PATCH request still returned `200 OK` with `{"success":true}` (since the write to `AssignedDate` succeeded even though `UpdatedDate` was rejected/truncated in a way that produced no visible error), while `GET /api/tasks` kept returning byte-identical data, causing a `304 Not Modified` on the follow-up fetch and giving the appearance that edits weren't saving at all. If a task field update looks like it's silently failing, check Airtable's own field-type settings for every field being written before suspecting the code.

---

## 9. Airtable API Patterns

### Fetching records

```javascript
async function fetchAirtable(tableId, formula = null) {
  let url = `https://api.airtable.com/v0/${AIRTABLE_BASE_ID}/${tableId}`;
  if (formula) url += `?filterByFormula=${encodeURIComponent(formula)}`;
  const res = await fetch(url, {
    headers: { Authorization: `Bearer ${AIRTABLE_TOKEN}` }
  });
  const data = await res.json();
  return data.records || [];
}
```

### Critical: Filtering linked record fields

**`ARRAYJOIN()` on a linked field returns display names, NOT record IDs.** This means `FIND("recXXX", ARRAYJOIN({LinkedField}))` will always return zero results.

**Always filter linked fields in Node.js, not in Airtable formula:**

```javascript
// WRONG — won't work for linked fields
const formula = `FIND("${userId}", ARRAYJOIN({User}))`;

// CORRECT — fetch all, filter in Node
const allRecords = await fetchAirtable(TABLES.Access);
const matching = allRecords.filter(r => {
  const users = r.fields.User || [];
  return users.includes(userId);
});
```

### Updating records

```javascript
async function updateAirtable(tableId, recordId, fields) {
  const res = await fetch(
    `https://api.airtable.com/v0/${AIRTABLE_BASE_ID}/${tableId}/${recordId}`,
    {
      method: 'PATCH',
      headers: {
        Authorization: `Bearer ${AIRTABLE_TOKEN}`,
        'Content-Type': 'application/json',
      },
      body: JSON.stringify({ fields }),
    }
  );
  return res.json();
}
```

### Pagination for large tables

Airtable returns max 100 records per request. For tables with more than 100 records, use the `offset` field:

```javascript
async function fetchAllAirtable(tableId, formula = null) {
  const records = [];
  let offset = null;
  do {
    let url = `https://api.airtable.com/v0/${AIRTABLE_BASE_ID}/${tableId}?pageSize=100`;
    if (formula) url += `&filterByFormula=${encodeURIComponent(formula)}`;
    if (offset) url += `&offset=${offset}`;
    const res = await fetch(url, {
      headers: { Authorization: `Bearer ${AIRTABLE_TOKEN}` }
    });
    const data = await res.json();
    records.push(...(data.records || []));
    offset = data.offset || null;
  } while (offset);
  return records;
}
```

### getAllDevUsers() return format
The `getAllDevUsers()` helper in dev-portal returns **flat objects**, not raw Airtable records:

```javascript
// Returns: [{ id, name, email, role, status }]
// NOT: [{ id, fields: { Name, Email, ... } }]

// Correct usage:
const users = await getAllDevUsers();
users.map(u => u.name);   // correct
users.map(u => u.fields.Name); // WRONG — fields is undefined
```

---

## 10. www-data sudo Permissions

The Node.js backend runs as `www-data`. It has limited sudo rights defined in `/etc/sudoers.d/www-data-devportal`:

```
www-data ALL=(root) NOPASSWD: /usr/local/bin/recoturbo-provision-domain.sh
www-data ALL=(root) NOPASSWD: /usr/local/bin/recoturbo-update-nginx-config.sh
www-data ALL=(root) NOPASSWD: /bin/systemctl restart dev-portal
www-data ALL=(root) NOPASSWD: /bin/systemctl reload nginx
www-data ALL=(root) NOPASSWD: /bin/systemctl restart nginx
```

To add a new service restart permission (e.g. for a new project):

```bash
sudo visudo -f /etc/sudoers.d/www-data-devportal
# Add line:
# www-data ALL=(root) NOPASSWD: /bin/systemctl restart my-app
```

### Restart-permission architecture (as of July 2026)

Dev-portal's "Restart" button (per-project, in the workspace UI) calls `execFile('sudo', ['systemctl', 'restart', project.restartCommand])`. **Sudoers is the sole authority on which services can actually be restarted** — there used to also be a hardcoded JS-level allow-list (`ALLOWED_RESTART_SERVICES`) in `dev-portal/backend/server.js`, but it only ever contained `'dev-portal'` and blocked every other project's restart button from working at all, defeating the purpose of `RestartCommand` being a per-project field. It was removed. `execFile` (not `exec`) is used, so `project.restartCommand` is passed as a literal argv value with no shell-injection risk — there's no security reason to duplicate the sudoers gate in JS.

**To enable one-click restart for a project**, two things are needed:
1. A sudoers line for that service (as above)
2. The project's `RestartCommand` field in Airtable set to the **bare systemd service name only** — e.g. `internal-portal`, not `sudo systemctl restart internal-portal`. The code prepends `sudo systemctl restart` itself; a malformed field value here (the whole command instead of just the service name) is a real, easy-to-make mistake that produces a confusing "Restart command failed" error with no further detail in the UI — check the field value directly if this happens.

The same applies to the "archive project" flow, which calls `systemctl stop` on the project's service — if you want archiving to also correctly stop a given project's service, add a matching `stop` sudoers line too, since `restart` and `stop` are different commands from sudo's perspective.

**Not yet built:** auto-provisioning this sudoers permission at project-creation time. Currently it's a manual step per project. A wildcard sudoers rule (`systemctl restart *`) was deliberately avoided — it would let `www-data` restart *any* systemd unit on the box, which is a real privilege-escalation risk for an account that also handles user file uploads.

### Writing nginx config files from Node.js

`www-data` can write to `/etc/nginx/sites-available/` (directory is group-writable) but **cannot overwrite existing root-owned files directly**. Use the helper script:

```javascript
const { execFile } = require('child_process');
const fs = require('fs');

// Write to temp file first
const tmpFile = `/tmp/nginx-update-${Date.now()}.conf`;
fs.writeFileSync(tmpFile, newConfigContent);

// Then move into place via sudo script
execFile('sudo', [
  '/usr/local/bin/recoturbo-update-nginx-config.sh',
  '/etc/nginx/sites-available/myapp.recoturbo.co.uk',
  tmpFile
], callback);
```

---

## 11. Frontend SPA Pattern

All project frontends are Single Page Applications. The pattern used across all portals:

```html
<!-- Views are shown/hidden with display style -->
<div id="viewLogin" style="display:none;">...</div>
<div id="viewDashboard" style="display:none;">...</div>

<script>
function showView(name) {
  document.getElementById('viewLogin').style.display = name === 'login' ? 'flex' : 'none';
  document.getElementById('viewDashboard').style.display = name === 'dashboard' ? 'block' : 'none';
}

// On page load, check session
document.addEventListener('DOMContentLoaded', async () => {
  const res = await fetch('/api/user');
  if (res.ok) {
    showView('dashboard');
  } else {
    showView('login');
  }
});
</script>
```

### Design system

All portals use the same dark theme:

```css
:root {
  --bg: #0f0f0f;
  --bg-card: #1a1a1a;
  --border: #2a2a2a;
  --text: #e8e8e8;
  --text-secondary: #6b6b6b;
  --accent: #00d9ff;
  --green: #27c93f;
  --error: #ff5f56;
  --amber: #ffb000;
}
```

Fonts: `Space Grotesk` (UI), `JetBrains Mono` (code/mono), loaded from Google Fonts.

---

## 12. Known Quirks and Gotchas

### nginx reload vs restart
`systemctl reload nginx` is unreliable on this server — worker processes sometimes continue serving stale config. **Always use `systemctl restart nginx`** when applying config changes.

### Duplicate POST requests
nginx occasionally sends two POST requests to the upstream for one browser request (~7-10 seconds apart). The root cause is unresolved but mitigated with an **idempotency guard** on the project creation endpoint — checks if a project with the same name already exists before creating records. Apply the same pattern to any endpoint that creates records and must not duplicate.

### execFileSync blocks the event loop
Using `execFileSync` for long-running child processes (e.g. certbot) blocks Node's event loop, causing queued requests to be processed after the block releases — appearing as duplicate requests. **Always use async `execFile`** wrapped in a Promise for any subprocess that takes more than a few milliseconds.

### Airtable Personal Access Tokens
Tokens do **not** automatically gain access to new tables created after the token was issued. If a new table is added to a base, the token must be updated in Airtable's token settings to include the new table, or a new token must be created.

### .env files
Never edit `.env` files manually. Always use dev-portal's **Write .env File** button, which decrypts credentials from the Credentials table and writes them correctly. Manual edits will be overwritten next time the button is pressed.

### Credential UpdatedBy field
The `UpdatedBy` field in the Credentials table is a **plain text field** (email string), NOT a linked record. Passing a record ID will cause an Airtable API error.

### File versioning
Dev-portal's file upload system automatically versions existing files — the old version is moved to `_archive/` before the new one is written. The archive is accessible via the Archive tab in the workspace. This means recovering from accidental overwrites is always possible.

### SSL certificate rate limits
Let's Encrypt allows a maximum of **5 certificates per exact domain per 168 hours**. During development/testing, avoid repeatedly creating and deleting certificates for the same domain. Use different test subdomains for repeated tests.

### Airtable base ID in URLs
The Airtable base ID (`appXXXXXXXXXXXXXX`) appears in API URLs. When a base is duplicated in Airtable, the base ID changes but table IDs remain the same. Only `AIRTABLE_TOKEN` and `AIRTABLE_BASE_ID` need updating when promoting from staging to live.

### Cross-base Airtable linking
Airtable does not support linking records across different bases. When a section needs to reference something in a different base (e.g. `InternalSectionAccess` referencing projects in the dev-portal base), store the reference as a **plain text field** (e.g. `SectionSlug`) and resolve it in Node.js code.

### Airtable date fields
Never send an empty string `""` for date fields — Airtable returns a 422 error. Only include date fields in PATCH/POST bodies when they have a value.

### Airtable attachment URLs always expire (added July 2026)
**Any** URL Airtable's API returns for an attachment field is signed and time-limited — typically only a few hours — regardless of how the file was originally uploaded (through a proper upload flow, drag-and-drop, or otherwise). This is Airtable's own behavior, not something specific to any one upload path. Storing that URL directly in stored *text* (e.g. baked into a markdown string like `![](that-url)`) means the image silently breaks once the signed link expires, with no error visible anywhere — it looks fine immediately after uploading and then quietly stops working. This bit E-Learning's course-content images (`![size:x](url)` markdown), and cost real time to diagnose because the failure mode gives zero indication of a cause — the image just doesn't render.

**The fix, if any feature needs to store a reference to an attachment in text rather than a live Airtable field:** don't store Airtable's returned URL directly. Instead store a stable URL pointing at your own server (e.g. `/api/<section>/image-proxy?table=X&record=Y&field=Z&id=W`), which re-resolves a fresh Airtable URL on every request and redirects to it — the browser still loads the actual bytes from Airtable's own CDN, this never proxies the image data itself, so it adds no disk usage. Cache the resolved URL briefly server-side (comfortably inside Airtable's own signed-URL lifetime) to avoid re-hitting the Airtable API on every single image view.

**Fields that don't have this problem:** anything stored as a genuine Airtable attachment field and re-fetched live on every page load (thumbnails, user photos, course-download attachments) — these are never at risk, since a fresh URL is fetched every time regardless. Only text fields with a URL baked permanently into them are vulnerable.

---

## 13. Adding a New User

### Dev-portal access
Admin → Admin tab → Users → `+ Add User`. Sets `MustChangePassword: true` automatically — user is forced to change on first login.

To reset a password: Admin → Admin tab → Users → Reset Password button.

### Internal portal access
Admin → login to `internal.recoturbo.co.uk` → Admin card → Users tab → `+ Add User`.
Also create an `InternalSectionAccess` record for each section the user needs access to.

### Manual password set (any portal)
```bash
node -e "
const crypto = require('crypto');
const password = 'TempPassword123';
const salt = crypto.randomBytes(24).toString('base64url');
const inner = crypto.createHash('sha256').update(password, 'utf8').digest('hex');
const hash = crypto.createHash('sha256').update(inner + salt, 'utf8').digest('hex');
console.log('Salt:', salt);
console.log('Hash:', hash);
"
```
Then update `PasswordHash`, `PasswordSalt`, `PasswordSetDate`, `MustChangePassword: true` in Airtable.

---

## 14. Port Assignments

| Port | Service |
|---|---|
| 3001 | Dev Portal |
| 3002 | Internal Portal |
| 3003 | Schedules Portal (Hasan) — `schedules-portal.service` |
| 3004 | Reco Web Portal — `web-portal.service` (see §26) |
| 3005+ | New projects (assign sequentially) |

Check available ports before starting a new service:
```bash
ss -tlnp | grep 300
```
Also worth checking `systemctl list-units --type=service --state=running | grep -i node` and `ls /etc/systemd/system/*.service` to see what's actually running and what each service's `ExecStart` points at — a port showing as in-use in `ss` doesn't say *what* is using it, and guessing wrong risks colliding with someone else's project.

---

## 15. Auto-Provisioning (New Projects & Sections)

### Overview
When a new project is created via dev-portal, three standard Airtable tables are automatically created in the project's base, and all active DevUsers are seeded as Admin users. When a new section is created, the parent project's Sections config table is auto-populated.

### Requirement
The Airtable token supplied when creating a project **must have `schema.bases:write` scope** for auto-provisioning to work. If the token lacks this scope, provisioning fails gracefully (warning logged, project still created).

To add the scope: Airtable → Developer Hub → Personal Access Tokens → edit token → add `schema.bases:read` and `schema.bases:write`.

### Tables created on new project

**Users table** — standard auth table:
- Name, Email, PasswordHash, PasswordSalt, PasswordSetDate, MustChangePassword (checkbox), Role (Admin/User), Status (Active/Inactive), LastLogin

**Sections table** — section config:
- SectionSlug, Label, Icon, Description, Roles, Index

**SectionAccess table** — user→section assignments:
- User (linked → Users), SectionSlug, Role, AssignedDate

The table IDs are automatically stored as credentials against the project:
- `AIRTABLE_USERS_ID`
- `AIRTABLE_SECTIONS_ID`
- `AIRTABLE_ACCESS_ID`

### User seeding
All active DevUsers are seeded into the new project's Users table as `Role: Admin` with `MustChangePassword: true`. Their passwords are not set — they must be set manually or via the project's admin UI.

### Section auto-config
When a section is added to a project via dev-portal:
1. A record is auto-created in the parent project's Sections table with the slug, label, icon, description, roles and next available index
2. SectionAccess records are auto-created for all Admin users in the project's Users table

### Code location
- `/opt/dev-portal/backend/lib/airtableProvisioner.js` — table and field creation, user seeding
- `/opt/dev-portal/backend/lib/projectCreation.js` — calls provisioner after credentials stored
- `/opt/dev-portal/backend/lib/sectionCreation.js` — auto-creates Sections config record and SectionAccess records

---

## 16. Internal Portal Admin UI

### Overview
Admins (Role: Admin in Users table) see an ⚙️ Admin card on the dashboard. This opens the admin view with two tabs: Users and Sections.

### Users tab
- Lists all Users records with name, email, role, status, last login
- **Access button** — opens "Manage Access" modal showing which sections the user has access to, with ability to add/remove sections and set roles
- **Reset Password** — sets temp password, enables MustChangePassword
- **Activate/Deactivate** — toggles Status field
- **+ Add User** — creates new user with MustChangePassword: true

### Sections tab
- Lists all sections from the Sections config table
- Drag and drop to reorder (updates Index field)
- **Users button** — opens "Manage Section Users" modal showing which users have access, with ability to add/remove users and set roles

### Section assignment UI
Both modals (Manage Access and Manage Section Users) use the `Roles` field from the Sections table to populate the role dropdown — so each section can have its own set of valid roles.

### Admin API endpoints (internal portal)

| Method | Endpoint | Description |
|---|---|---|
| GET | `/api/admin/users` | List all users |
| POST | `/api/admin/users/create` | Create user |
| POST | `/api/admin/users/:id/reset-password` | Reset password |
| PATCH | `/api/admin/users/:id` | Toggle status |
| GET | `/api/admin/sections` | List sections with roles |
| PATCH | `/api/admin/sections/reorder` | Update section order |
| GET | `/api/admin/access` | List all SectionAccess records |
| POST | `/api/admin/access` | Create SectionAccess record |
| DELETE | `/api/admin/access/:id` | Remove SectionAccess record |

---

## 17. File Tree Filtering

The dev-portal workspace file tree automatically filters what's shown:

- **Top-level projects** — shows own files only; child section folders are hidden
- **Sections** — shows only that section's files (deployPath already scoped to section folder)
- **Always hidden:** `node_modules/`, `.git/`, `_archive/` (Archive tab), `logs/`

This prevents confusion when a project has multiple sections — opening the Internal project workspace won't show the e-learning folder.

**Implementation:** `buildFileTree()` in `server.js` accepts an `extraIgnore` set. The tree endpoint fetches child section slugs for the current project and passes them as extra ignore entries.

---

## 18. Projects Page — Section Identification

On the "Your Projects" page, sections are visually distinguished from top-level projects:
- Cyan **SECTION** badge in the top-left of the card
- "of [Parent Project Name]" label showing which project it belongs to
- Cyan left border on the card

This is driven by the `parentProjectId` and `parentProjectName` fields returned by `/api/projects`.

---

## 19. Project File Lock System

### Overview
Any user can lock a project/section workspace to prevent simultaneous file edits by other team members. The lock is lightweight — stored as a JSON file on the VPS, not in Airtable.

### Lock file location
```
/opt/dev-portal/locks/<projectId>.lock
```

**Contents:**
```json
{"lockedBy": "geoff@recoturbo.co.uk", "lockedByName": "Geoff", "lockedAt": "2026-07-07T20:00:00.000Z"}
```

### SSH backdoor (emergency unlock)
```bash
rm /opt/dev-portal/locks/<projectId>.lock
```

### Behaviour
- Lock prevents file **upload and delete** operations only — does not block credentials, section creation, or admin functions
- Only the lock owner can unlock from within dev-portal
- On lock, critical files (`server.js`, `index.html`) are automatically snapshot to `_archive/` with a `lock-snapshot` timestamp in the filename
- Lock indicator (🔒 + owner name) appears on the project card (amber left border) and in the workspace header

### API endpoints

| Method | Endpoint | Description |
|---|---|---|
| GET | `/api/projects/:id/lock` | Get lock status |
| POST | `/api/projects/:id/lock` | Lock project |
| DELETE | `/api/projects/:id/lock` | Unlock (owner only) |

### Lock snapshots
When locking a project, `server.js` and `index.html` are copied to:
```
_archive/backend/server-lock-snapshot-<timestamp>.js
_archive/public/index-lock-snapshot-<timestamp>.html
```
These are visible in the Archive tab and can be used to restore files if someone edits via SSH while the lock is set.

---

## 20. Hierarchical Projects Navigation

### Overview
The "Your Projects" page shows only **top-level projects** — sections and sub-sections are accessible via drill-down panels.

### Navigation flow
```
Your Projects → [project cards — top-level only]
  → Sections button → slide panel showing sections for that project
    → Sub-sections button → slide panel showing sub-sections for that section
      → ← Back → returns to parent panel
```

### Button visibility rules
- **Sections button** — only appears on top-level projects (`parentProjectId` is null) that have at least one child
- **Sub-sections button** — only appears on sections (have a `parentProjectId`) that have at least one child
- No button shown if no children exist

### API flags
The `/api/projects` endpoint returns two flags on each project:
- `hasSections` — true if this project has any child records
- `parentIsSection` — true if this project's parent is itself a section (used for bubble styling)

### Card visual indicators
- **Top-level project** — standard card, no badge
- **Section** — cyan `SECTION` badge + "of [Parent]" (parent in plain text if top-level project, cyan bubble if parent is also a section)
- **Locked project** — amber left border, 🔒 owner name shown

---

## 21. Sub-sections Architecture

### Overview
Sections can have their own sub-sections, creating a two-level hierarchy under each project. A section that acts as a dashboard for its sub-sections gets an auto-generated functional dashboard page instead of the Under Construction page.

### URL structure
```
internal.recoturbo.co.uk/accounts/          ← Section dashboard (auto-generated)
internal.recoturbo.co.uk/accounts/credits/  ← Sub-section
internal.recoturbo.co.uk/accounts/payroll/  ← Sub-section
```

### Slug format
Sub-sections use plain slugs (no slash) in the Projects table — the hierarchy is determined by the `ParentProject` linked field, not the slug format.

### Dashboard template
When a **top-level section** (no slash in slug, direct child of a project) is created via dev-portal, it automatically gets a functional dashboard `index.html` that:
- Checks SSO authentication
- Calls `/api/sections?parent=<slug>` to get sub-sections for the logged-in user
- Renders sub-section cards with role badges
- Has a "← Portal" back link

Sub-sections get the standard Under Construction page.

### `/api/sections?parent=` filter
The internal portal's `/api/sections` endpoint supports a `parent` query parameter:
```javascript
// Returns only direct children of 'accounts' slug
fetch('/api/sections?parent=accounts')
```
Returns sections where `SectionSlug` starts with `accounts/` — but since slugs don't contain slashes, this effectively filters by parentage via the Sections config table.

### nginx location blocks
Each sub-section gets its own nginx location block added to the parent project's config:
```nginx
location = /accounts/credits { return 301 /accounts/credits/; }
location /accounts/credits/ {
    alias /opt/projects/internal/accounts/credits/public/;
    index index.html;
    try_files $uri $uri/ =404;
}
```

### SectionAccess for sub-sections
Sub-sections use plain slugs in `SectionAccess.SectionSlug` (e.g. `credits`, not `accounts/credits`). Access is managed independently per sub-section — having access to `accounts` does not automatically grant access to `accounts/credits`.

### ~~Nested sub-section access check~~ — Fixed (July 2026)
Every generated section/sub-section `index.html`'s own access-gate JS called `fetch('/api/sections')` with no `?parent=` — which only ever returns TOP-LEVEL sections. A nested slug (e.g. `assembly/technical-datasheets`) could never be found in that list, so the check always fell through to "Access denied — you don't have permission to view this section", regardless of actual permissions. This affected every existing sub-section created before the fix (fixed via a batch-patch script scanning `<parent>/*/public/index.html`), and was fixed at the template level in both `buildUnderConstructionHtml()` and `buildSectionDashboardHtml()` in `sectionCreation.js` — the fix computes `parentSlug` from the slug's own nesting and queries `/api/sections?parent=<parentSlug>` when nested, `/api/sections` when top-level.

### Section name is now pulled live, not baked in (added July 2026)
The generated `<title>`, header brand label, and (dashboard-template) `<h1>` are updated at runtime from the same `thisSection` object the access check already fetches (Airtable Sections table `Label` field) — not the name given at creation time. Renaming a section in Airtable now actually renames it on the page on next load, with no manual file edit needed. Sections created before this landed still show their creation-time name as static text.

### Starter `routes.js` is now auto-generated (added July 2026)
Every new section gets a minimal `backend/routes.js` alongside its `index.html` — wired to `shared/core.js`'s `verifyAuth`/`requirePasswordCurrent`, with one working `GET /api/<slug>/ping` route proving the wiring is correct. Without this, the generated page's own "Building this out" hint text referenced a `routes.js` that didn't actually exist. The `require()` path depth to `shared/core.js` is computed from the slug's nesting (top-level vs sub-section need a different number of `../`), not hardcoded.

### Header convention: black, not gradient (added July 2026)
All new sections (and Internal Portal's own top-level dashboard, and any project cloned from Internal — see §26) use a black/dark-grey header (`background-color: #1a1a1a`) instead of the earlier teal→indigo gradient. This is a platform-wide styling decision, not specific to any one project — apply it to any new top-level portal or section going forward. The logo also switched from an SVG referenced via `mix-blend-mode: multiply` (which relies on the colored gradient and renders invisible on solid black) to the same embedded base64 PNG dev-portal's own header already used, which is proven to work correctly on a dark background.

### Undo / Refresh Template (added July 2026)
Two related but distinct admin actions, both in dev-portal's Manage Projects list, both scoped to sections only (refuse on top-level projects):
- **↩ Undo** (`POST /api/admin/projects/:id/undo-creation`) — reverses a section's creation entirely (disk folder, dev-portal Projects/ProjectAccess/Credentials records, Internal's Sections record and its seeded SectionAccess), but *only* if nothing has been added or changed since creation. The safety check regenerates what `index.html`/`routes.js` SHOULD contain right now (same template functions used at creation) and compares byte-for-byte against what's on disk, plus confirms no other files exist and no sub-sections have been created underneath. Any mismatch is refused with a specific reason, not a silent no-op. Deliberately narrow by design — dev-portal has never supported a true delete for projects generally (archive only), and this doesn't change that; it only handles the "typo, seconds after creation, zero content" case safely.
- **↻ Refresh Template** (`POST /api/admin/projects/:id/refresh-template`) — regenerates `index.html`/`routes.js` from the CURRENT template, for sections created before a template fix landed. Unlike Undo, this always overwrites once confirmed — no safety gate, since the whole point is intentionally replacing what's there. Detects the section's actual access type (public/private) from the existing file (`id="accessGate"` present = private) rather than guessing, since accessType isn't stored anywhere retrievable after creation.

**Note:** section creation also auto-seeds SectionAccess for every Admin user found in the parent project's own Users table — this is normal, expected state for every fresh section, not a sign it's already in real use. Don't use "SectionAccess records exist" as a signal for anything; it's true immediately on creation.

---

## 22. Section Specification Template

A template for developers to fill in before asking an AI to build a new section is available at:
```
https://dev.recoturbo.co.uk/docs/recoturbo-section-spec-template.md
```

The template covers:
- Section identity (name, slug, deploy path)
- Authentication (SSO check code, role definitions)
- Data source (Airtable base, token, tables, fields)
- Feature requirements
- Technical constraints (single file, CSS variables, header pattern)
- Backend endpoint definitions
- UI layout description
- Exact prompt wrapper to give the AI

**All developers building new sections should complete this template** before writing any code or asking an AI for assistance.

---

## 23. Known Issues / Pending Fixes

### ~~Sub-section credential lookup~~ — Fixed (July 2026)
Previously, creating a sub-section looked for `AIRTABLE_SECTIONS_ID` in the sub-section's own credentials instead of walking up to the top-level parent project. Fixed in `sectionCreation.js` via `resolveTopLevelAndNestedSlug()`, which walks the `ParentProject` chain to find the top-level ancestor and resolves credentials from there. SectionAccess auto-seeding for sub-sections now works correctly.

### ~~SectionAccess not auto-created for legacy projects~~ — Fixed (July 2026)
Same root cause and fix as above — this was really the same bug described two different ways. Confirmed working for Internal, Dev Portal, and their existing sub-sections as of the section-creation rework in July 2026.

### Auto-loader crash isolation — Fixed (July 2026)
Internal Portal's section auto-loader used to `require()` every section's `routes.js` with no error handling — a syntax error or thrown exception in *any single section* would crash the entire Internal Portal server at startup (all sections down, not just the broken one). Fixed by wrapping each section's `require()` in try/catch inside `mountSection()` (Internal's `backend/server.js`): a broken section now logs `[SECTION] Failed to load routes for <slug>: <error>` and is skipped — its static frontend (if any) still loads, other sections are completely unaffected, and the server still reaches "listening" successfully. If building or reviewing an auto-loader-style pattern for another project, replicate this — the failure mode without it is a real outage, not theoretical (it happened once during E-Learning development).

### Section access type: Public vs Private (added July 2026)
Section creation (`sectionCreation.js`, called from dev-portal's Add Section form) now accepts an `accessType` field (`'private'` — default, existing behaviour — or `'public'`). A public section's generated template skips the access-gate JS entirely (no `/api/user` check, no login redirect, no header user-chip) so anonymous visitors can view it. Existing sections are unaffected (missing/invalid `accessType` defaults to `'private'`). **Known limitation:** a public *top-level* section that itself has sub-sections will still fail to list them for anonymous visitors, since `/api/sections?parent=X` (used by the sub-section-listing dashboard template) is not yet accessType-aware and still requires auth regardless of the parent's own access type. Not an issue for a flat public section with no sub-sections of its own.

### Forgot Password (dev-portal, added July 2026)
Dev-portal now has a working forgot/reset password flow (`/api/forgot-password`, `/api/reset-password`, plus a `/reset-password` route serving the SPA for the emailed link to load correctly). SMTP credentials are stored in dev-portal's own Credentials table (project = dev-portal itself), read via a dedicated `getSmtpConfig()` helper, decrypted via the existing local `decryptValue()` function (not the separate `credentialHelpers.js` module used by section-creation code — these are two different, non-interchangeable implementations living in different files; mixing them up caused a real bug during development). Reset tokens are single-use, expire after 1 hour, and are stored directly on the `DevUsers` record (`ResetToken`, `ResetTokenExpiry` fields) rather than in-memory, since dev-portal restarts frequently enough during normal development that an in-memory token store would be unreliable.

### Hasan's Schedules feature
Hasan is actively developing a Schedules feature in dev-portal. Files modified: `server.js` (new functions `scheduleFileIsIdentical`, `buildScheduleCheckPayload`), `index.html` (new CSS classes `.sched-top-tabs`, `.sched-section-panel`, `.sched-library-layout`). Do not overwrite these files without checking with Hasan first.

### E-Learning section (Internal Portal)
A large, feature-complete section built out over two sessions in July 2026 — courses, modules, lessons, quizzes, a standalone Matching Exercise type (drag-line pairing UI), Lookup Exercise, trainee enrollment/progress tracking, and an admin review system. It has its **own dedicated handoff document** (`ELEARNING-HANDOFF.md`, in the E-Learning Docs folder) covering its specific architecture, Airtable schema, and several real bugs found and fixed during development — read that instead of trying to reverse-engineer `routes.js`/`index.html` from scratch. Two points worth knowing at the platform level even if you never touch E-Learning directly:
- It's a working example of the auto-loader crash-isolation fix above actually mattering in practice (a bug in one of its endpoints briefly took down all of Internal Portal before that fix existed).
- Its shared `Users`/`getElUser()` pattern (auto-provisioning a section-local user record on first access, keyed off Internal's own SectionAccess as the real permission source) is a reasonable model to reuse for any other section that needs its own lightweight per-user data without needing its own login system.

## 24. Disaster Recovery Backups — NOT YET DEPLOYED

**Correction (July 2026):** an earlier version of this document described this feature as working. It is not. The backup scripts (VPS folder/config backup, Airtable JSON export) were written but never actually deployed to dev-portal's running `server.js` — worse, an incomplete `require('./lib/backupManager')` line was briefly committed to `server.js` without the corresponding file ever being deployed, which crashed dev-portal on startup in production. That line has since been removed, and the non-functional "Backups" tab UI has been removed from dev-portal's Admin panel entirely (it called endpoints that don't exist).

**Open scoping question, unresolved:** the main blocker isn't the VPS-side backup (small, straightforward) — it's Airtable **attachment** data. A record-data-only export (JSON, no attachment bytes) is small and ready to build. A full export *with* attachment bytes could be 200GB+ for some bases, which won't fit on this VPS's own disk (5.2GB) and needs an external storage destination decided before it's worth building. Options on the table, not yet decided: (1) VPS-config-only backup, (2) Airtable record-data-only (no attachments), (3) full attachment backup once external storage is sorted.

**If picking this back up:** don't just re-add the removed UI — resolve the attachment-storage question first, then build server-side from scratch rather than trusting anything currently in the codebase relating to backups.

---

## 25. Internal Portal Bridge — Credential-Based Table IDs (added July 2026)

Dev-portal's own code needs to reach into Internal Portal's *separate* Airtable base for two things: mirroring `Lead`-level `ProjectAccess` grants into Internal's `SectionAccess` table, and (added July 2026) mirroring a section's `Description` edit into Internal's own `Sections` table. Both of these previously used **hardcoded table-ID constants** in `server.js` (`INTERNAL_PORTAL_USERS_TABLE_ID`, `INTERNAL_PORTAL_SECTION_ACCESS_TABLE_ID`) — this was a real, if quiet, design flaw: `sectionCreation.js` had already established a better pattern (reading `AIRTABLE_SECTIONS_ID`/`AIRTABLE_ACCESS_ID`/`AIRTABLE_USERS_ID` as **Credentials** against the top-level project, not hardcoding them), and the hardcoded constants in `server.js` duplicated information that would silently go stale if any of those table IDs were ever rotated in Airtable without a matching code deploy.

**Fixed:** a shared `getInternalPortalTableIds(topLevelProjectId)` helper in `server.js` now resolves all three (`sectionsTableId`, `accessTableId`, `usersTableId`) from Credentials, exactly matching `sectionCreation.js`'s own lookup. Both `cascadeInternalSectionAccess()` and the newer `cascadeInternalSectionDescription()` (see §26) call this instead of referencing hardcoded constants. **If building any new bridge feature that needs to reach into another top-level project's Airtable base, use this same pattern** — read the target table ID as a Credential against that project, don't hardcode it, even for a "just this once" feature.

## 26. Description Sync — Dev-Portal ↔ Internal Portal (added July 2026)

Dev-portal's own `Projects.Description` (edited via the workspace view's pencil-icon) and Internal's own `Sections.Description` (shown on Internal's dashboard cards) are genuinely two separate fields in two separate Airtable bases — editing one never touched the other, which was reported as a real bug (deleting a description in dev-portal didn't remove it from Internal's dashboard). Fixed with a `cascadeInternalSectionDescription(project, description)` function in `server.js`, called from `PUT /api/workspace/:projectId/description` — mirrors the same best-effort-cascade pattern as the SectionAccess bridge (logs a warning and continues rather than failing the dev-portal-side save if the mirror step itself fails, since that save has already succeeded and remains the source of truth either way).

## 27. Reco Web Portal (`web.recoturbo.co.uk`) — added July 2026

A new, genuinely separate top-level project — RecoTurbo's intended future customer-facing portal, distinct from Internal Portal (staff-only). Its own dedicated port (3004), systemd service (`web-portal.service`), Airtable base, and login system — **not** sharing Internal's users or base. Built by cloning Internal Portal's own `server.js`/`shared/core.js`/`index.html` (same auth pattern, same section auto-loader), then re-pointing at its own `.env`, port, and Airtable base — cloned from Internal specifically so both projects keep sharing any future auth/core-logic fix rather than diverging.

**Domain history:** originally set up under `recoportal.recoturbo.co.uk`, migrated to `web.recoturbo.co.uk` (the old domain retired, not kept as a redirect — a deliberate choice, not an oversight). If `recoportal.recoturbo.co.uk` is ever seen referenced anywhere, it's stale.

**Setup gotchas hit during deployment, worth knowing if setting up another new top-level project this way:**
- **Airtable tokens don't automatically get access to a brand-new base**, even with "all workspaces" access configured on the token — the base has to be added to the token's access list explicitly, checked via `GET /v0/meta/bases/<baseId>/tables` (returns an `INVALID_PERMISSIONS_OR_MODEL_NOT_FOUND` error if access is missing, indistinguishable from a wrong base ID or wrong token without checking each independently).
- **`.env` is a static snapshot** — clicking "Write .env File" in dev-portal's Credentials tab writes it once; editing the underlying credential afterward does nothing to the already-running process until "Write .env" is clicked again AND the service is restarted (the process only reads `.env` once, at startup, via `dotenv`).
- Files "delivered" (e.g. as a chat attachment) are not the same as files "deployed" — several setup steps were re-attempted because a config/service file was assumed to be in place on the VPS when it had never actually been transferred there at all. Always verify with `ls`/`cat` on the actual target path rather than assuming a delivered file made it across.
- `package.json` needs to list every dependency the actual code requires (`dotenv`, `jsonwebtoken`, `cookie-parser` — not just `express`) — cloning `server.js`/`core.js` without updating `package.json` to match produces a `Cannot find module` crash on startup that's easy to mistake for a `.env`/credentials problem instead.

## 28. Turbo Search — Shared Cross-Section Feature (added July 2026)

The first (and reference pattern for any future) genuinely **shared** section — built once as its own top-level section under Internal, then linked to (not duplicated into) other sections' dashboards via a static card pointing at `/turbo-search/`. Chosen deliberately over creating a separate copy per section that would need it (Assembly, Warranty, Warehouse, and likely more to come): a shared section keeps one canonical codebase and one dataset, with zero risk of the copies drifting apart from each other over time.

**Important access-model gotcha:** because `SectionAccess` is granted per-slug with no inheritance, a user with access to (say) `assembly` does **not** automatically get access to `turbo-search` — that has to be granted separately. This is easy to forget when granting a new user access to a section that links to Turbo Search; if the card 404s or access-denies for someone who should be able to use it, this is the first thing to check.

## 29. Naming Convention: "Portal", Not "Dashboard" (added July 2026)

Going forward, new labels/copy should say **"Portal"** rather than **"Dashboard"** wherever referring to a section's or project's own landing page (e.g. "Reco Web Portal" rather than "Reco Web Dashboard", matching dev-portal's own naming). This is a forward-looking convention for new work only — existing "Dashboard" labels elsewhere in the platform have not been retroactively renamed and don't need to be as a one-off task.

---



## 30. Timeline Backup System (`/opt/timeline/`) — added 22nd July 2026 16:35

A VPS-wide backup system, separate from the pre-existing dev-checkpoint folder (`schedules-migration-backups`, see below).

### Structure

### Core script
`/usr/local/bin/recoturbo-project-backup.sh` — the single implementation both paths below call. Takes `--mode=full|manual --path=/opt/... --slug=name`. Restricts source paths to `/opt/projects/*` or `/opt/dev-portal`; slugs must be alphanumeric/hyphen/underscore only. Excludes `node_modules`, `.git`, `venv`, `__pycache__`, `.npm-cache` from the archive.

### Nightly full-system backup
- Runner: `/usr/local/bin/recoturbo-nightly-backup.sh` — loops over every directory under `/opt/projects/*` plus `/opt/dev-portal`, calling the core script in `--mode=full` for each. New projects are picked up automatically; no per-project registration needed.
- Timer: `recoturbo-nightly-backup.timer`, runs daily at 02:30.
- **Retention: 14 days.** The runner deletes `full-system-backups/**/*.tar.gz` older than 14 days after each run.
- Log: `/var/log/recoturbo-nightly-backup.log`.

### Manual section backup (Dev Portal button)
- Endpoint: `POST /api/workspace/:projectId/backup` in dev-portal's `server.js` (added directly after the existing restart endpoint, same `verifyAuth`/`requirePasswordCurrent`/`requireProjectReadWrite` guards). Resolves the project via `getProjectById()`, derives the slug from `path.basename(deployPath)`, and calls the core script via `execFile('sudo', [...])` — same pattern as the restart button, no shell string building.
- Sudoers: `www-data ALL=(root) NOPASSWD: /usr/local/bin/recoturbo-project-backup.sh` — scoped to only this script, same convention as the other entries in `/etc/sudoers.d/www-data-devportal`.
- UI: a "💾 Section Backup" button in the workspace top action row, positioned between **+ Add Section** and **🔒 Lock** (`id="backupBtn"`, calls `submitBackup()`), present for every project workspace.
- **Retention: none — kept indefinitely by design.** This is a point-in-time manual snapshot, not a rolling schedule.

### Dev-checkpoint folder — kept separate, not part of Timeline
`/opt/projects/schedules-migration-backups/` predates the Timeline system and holds AI-assisted development checkpoint snapshots (one per batch/phase during active work on Schedules), not full project archives. It has its own independent cleanup:
- Script: `/usr/local/bin/recoturbo-migration-backups-cleanup.sh`
- Timer: `recoturbo-migration-backups-cleanup.timer`, daily at 03:45
- **Retention: 7 days.**
- Log: `/var/log/recoturbo-migration-backups-cleanup.log`

Do not confuse this folder with `/opt/timeline/` — different purpose, different retention, different location. If a similar dev-checkpoint folder is created for another project in the future, it should get its own equivalent timer rather than being swept into the Timeline system's 14-day/indefinite rules.

## 30a. Manual File-Edit Backups (`/opt/timeline/file-backup/`) — added 28th July 2026

A third Timeline backup category, distinct from the other two documented in §30:

- **`full-system-backups/`** — nightly, whole-project archives (§30)
- **`section-backups/`** — manual, on-demand, per-project snapshots triggered from the Dev Portal workspace UI (§30)
- **`file-backup/`** — ad-hoc, single-file backups taken during manual AI-assisted or SSH-based code edits, *before* a patch is applied. This is the "back up the real file before editing it" step from `vps-change-guidelines.md` §2 — its backups belong here, not in `/opt/timeline/audits/mini/`, which is reserved for read-only investigation output (greps, `sed -n` ranges) rather than actual file copies.

### Naming convention
Every backup written to `file-backup/` must be tagged with its origin so it's identifiable from the filename alone, without needing to know which folder it came from:
- <project-slug><section-slug-if-any><original-filename>.bak-<YYYYMMDD-HHMMSS>
- example: ac-internal__warranties-dashboard__routes.js.bak-20260728-170300

For a top-level project file with no section involved, omit the section segment: dev-portal__server.js.bak-20260728-170300

### Rule
Going forward, any single-file backup taken as part of a manual edit (per `vps-change-guidelines.md` §2) should be written to `/opt/timeline/file-backup/` using this naming convention — not to `/opt/timeline/audits/mini/`, which stays reserved for investigation logs only.

Also update the bottom line of the file: *Last updated: 28 July 2026, 17:xx.*
(fill in the actual time you make the edit)

And in vps-change-guidelines.md, Section 2 ("Back up the real file before editing it") should be updated from:
sudo cp /path/to/real/file.js /opt/timeline/audits/mini/file.js.bak-$(date +%Y%m%d-%H%M%S)
to:
sudo cp /path/to/real/file.js /opt/timeline/file-backup/<project>__<section-if-any>__file.js.bak-$(date +%Y%m%d-%H%M%S)

## 31. Scheduler (Schedules Portal) — added 22nd July 2026 16:35

### Overview
`schedules.recoturbo.co.uk` (port 3003, `schedules-portal.service`) is a Node.js/Express app that runs and manages scheduled Python tasks on the VPS — a cron-replacement with a web UI, task history, and file management, built on the same auth pattern as the rest of the platform (`verifyAuth` against Dev Portal's own `DevUsers`, not a separate login system).

### Key paths
/opt/projects/schedules/backend/server.js          <- main backend
/opt/projects/schedules/backend/airtableSchemaRoute.js  <- uses shared credential helper directly (see §2)
/opt/projects/schedules/public/                     <- frontend SPA
/opt/projects/schedules/reco-scripts/scheduled/     <- one folder per task
/opt/projects/schedules/reco-scripts/venv/          <- single shared Python venv for all tasks
/opt/reco-scheduler/tasks/*.json                    <- task configs (one per task, read by both the Node backend and the cleanup script)

### Task folder convention
Each task lives at `reco-scripts/scheduled/<task-folder-name>/` and follows this structure:
<task-folder-name>/
  <script>.py            <- the task's Python script
  log.txt                 <- current run's log, overwritten each run
  logs/                   <- historical per-run logs, timestamped filenames
  outputs/                <- output data files (CSV/JSON/etc), timestamped filenames
  outputs/_archive/        <- older output snapshots (same naming convention as outputs/)

A task's `workingDirectory` and `scriptPath` (from its task JSON) determine which folder the cleanup script (see below) treats as that task's root — both must resolve to a real directory under `SCHEDULER_SCRIPT_ROOT` (`/opt/reco-scripts/scheduled`) or the cleanup script skips them.

### Task JSON structure
Each file in `/opt/reco-scheduler/tasks/` is a flat JSON object: `id`, `name`, `enabled`, `scriptPath`, `workingDirectory`, `arguments`/`commandArguments` (the literal shell command run, including redirecting output to `log.txt`), `triggerConfig` (schedule/frequency/days), and optionally `airtableProtection` (links the task to a specific Airtable base/table for display purposes only). Multiple tasks can point at the same `workingDirectory` (e.g. `schedule-rtyr` and `schedule-test-task` both point at `231all-mlt`) — this is valid and expected when the same script is registered under more than one schedule.

### Log & output retention (added 22nd July 2026 16:00)
`/usr/local/bin/reco-scheduler-clean-old-files` (Python, run nightly at 03:15 via `reco-scheduler-cleanup.timer`) automatically discovers every task from `/opt/reco-scheduler/tasks/*.json` — future tasks are covered with no extra setup. It applies two different rules depending on file classification:

- **Logs** (anything under a `logs/` folder, plus root-level `log.txt` or any `*.log` file): age-based, deleted if older than **7 days**. A task's live log is safe regardless of age as long as the task keeps running and overwriting it — this rule only cleans up logs from tasks that have stopped running or produce dated log files.
- **Outputs** (anything under `outputs/`/`outputs/_archive/`, plus recognised root-level output files): grouped by filename with the trailing `_YYYYMMDD_HHMMSS` timestamp stripped, **only the newest file per group is kept** — every older duplicate is deleted regardless of age. This is a dedup rule, not an age rule.

Both rules run from the same script; see `/usr/local/bin/reco-scheduler-clean-old-files` for the exact classification logic. Log: `/var/log/reco-scheduler-cleanup.log`.

### Credentials
See §2's "Schedules integration" subsection — Schedules' Node backend uses the shared `credentialHelpers.js` directly. **Python task scripts do not yet have a confirmed credential mechanism documented** — see the open question noted there before wiring a new Python task to expect Node-style credential resolution.

### Backups
Schedules participates in the platform-wide nightly full backup and has its own manual "Section Backup" button in its Dev Portal workspace — see §30. Its pre-Timeline dev-checkpoint folder (`/opt/projects/schedules-migration-backups/`) is a separate, older mechanism with its own 7-day cleanup timer — also documented in §30, not to be confused with the Timeline system itself.

### Known permissions note
`www-data` has a dedicated sudoers entry to restart this service specifically (`/bin/systemctl restart schedules-portal`), added 22nd July 2026 — before this was added, Dev Portal's one-click Restart button for Schedules would fail with no useful error message. If a similar new project's restart button silently fails, check `/etc/sudoers.d/www-data-devportal` for a matching line first.

## 32. Credentials Migration (Postgres-backed `dp_credentials`, section-side cache) — added 15th August 2026

**Status:** Dual-write (Airtable ⇄ Postgres) is live for dev-portal's Credentials table.
Two sections — `sales/vehicle-data` and `sales/vehicle-analysis` — have been migrated to
read their own credentials from Postgres instead of `.env`, confirmed working in
production. This is the first migration of its kind; more sections will follow the same
pattern over time. Full design rationale in `CREDENTIALS-MIGRATION-HANDOFF.md`
(`/opt/dev-portal/public/docs/`) — this section is the load-bearing summary.

### What it does
Every project/section's secrets (Airtable base/table IDs, API keys, SMTP params, etc.)
historically lived in that section's own `.env`, written by dev-portal from its Airtable
Credentials table. This adds a Postgres mirror (`dp_credentials`) plus a shared in-memory
cache module, so sections can read credentials directly from Postgres at runtime — no
`.env` needed except the unavoidable bootstrap values to reach Postgres itself. Airtable
stays the source of truth; dev-portal's Credentials UI dual-writes to both stores on every
create/update/delete, best-effort on the Postgres side (logs loudly, never blocks the
Airtable-successful request).

### Database (`dad`)
`dp_credentials` table — one row per credential: `id` (UUID PK), `airtable_record_id`
(UNIQUE, correlates the two stores), `project_id` (Airtable Project record id, same value
`credentialHelpers.js` resolves projects by), `key_name`, `is_secret`, `value` (plaintext,
or `iv:authTag:ciphertext` base64 if secret — same AES-256-GCM format as everywhere else
on the platform), `updated_date`/`updated_by`, `created_at`/`updated_at`. Indexed on
`project_id` and `key_name`.

A pre-existing, unrelated bare `credentials` table (no `airtable_record_id`, two empty
dependent views) was found and dropped during this migration — leftover scaffolding from
an earlier planning pass, never populated, not referenced anywhere. Don't assume a table
named `credentials` is this system without checking.

**Any section reading `dp_credentials` needs BOTH of these grants** — table `SELECT`
alone is not sufficient without schema `USAGE`, and both produce the identical
"permission denied" error, so don't assume the table grant covers it:
```sql
GRANT USAGE ON SCHEMA public TO <role>;
GRANT SELECT ON dp_credentials TO <role>;
```

### Dev-portal side
`/opt/dev-portal/backend/lib/pgCredentials.js` (mirror CRUD, called from `server.js`'s
existing Credentials routes) and `/opt/dev-portal/backend/backfillCredentials.js`
(one-off, re-runnable, `ON CONFLICT DO NOTHING`). Both must load dev-portal's `.env` by
explicit absolute path (`{ path: '/opt/dev-portal/.env' }`) — dotenv's bare default looks
in the current working directory, which breaks when either script is run manually from
elsewhere. If debugging either script from a long-lived root shell, check `env | grep
AIRTABLE` / `env | grep PG` first — stray exports from earlier testing silently shadow
`.env` values, since dotenv never overrides variables already in `process.env`.

### Section side — `credentialsCache.js`
`/opt/dev-portal/backend/lib/credentialsCache.js` — one instance per section, reusing
that section's own `pgPool`. Resolves its project the same way
`getDevPortalCredential()` in `credentialHelpers.js` does (matching caller path against
`Projects.DeployPath`). Usage:
```javascript
const { createCredentialsCache } = require('/opt/dev-portal/backend/lib/credentialsCache');
const credentials = createCredentialsCache({ pgPool, sectionName: 'my-section', callerPath: __filename });
```
Lazily, at each call site (not module load time): `await credentials.ready();` then
`credentials.get('KEY_NAME')` (or `.has('KEY_NAME')` for optional/graceful-skip cases).
Refreshes every 5 minutes in the background, so a credential change in dev-portal
propagates to a running section without a restart.

**`callerPath: __filename` is not optional in practice — this was the one real bug hit
during this migration.** `credentialsCache.js` is shared; if two sections are mounted in
the SAME Node process (as happens under internal portal's section auto-loader), Node's
module cache means the default (`module.parent`) only ever reflects whichever section
required the module *first*, for the life of that process — silently resolving every
other section's cache to the *first* section's project, with no error and sometimes
still-appears-to-work behaviour. Always pass `callerPath: __filename` explicitly. If a
section ever seems to be reading another section's credentials, compare
`credentials.getResolvedProjectId()` against the project actually linked to the
credential in Airtable/`dp_credentials`.

What still has to stay in `.env`: each section's own Postgres bootstrap
(`{PREFIX}_PG_HOST/PORT/DATABASE/USER/PASSWORD`) — a credential system can't supply the
credentials needed to reach itself. Everything else becomes a `credentials.get()` call.

### Migrating a new section — checklist
1. Grant its Postgres role `USAGE` on `public` + `SELECT` on `dp_credentials`.
2. Create its `credentials` cache instance (with `callerPath: __filename`) right after
   its own `pgPool`.
3. Mechanically swap every `process.env.X` credential read for `credentials.get('X')`,
   lazily, at each call site — don't prune "unused-looking" vars as part of this, just
   swap the source; something elsewhere may still depend on a credential this file
   doesn't itself call.
4. Test with the `.env` entry genuinely commented out (not just present-but-unused) and
   the service restarted — this is the only real proof the cache path is being used,
   since an already-dead code path "still working" proves nothing either way.
5. Once confirmed, remove (or comment out, as a rollback safety net) the now-redundant
   `.env` entries.

*Last updated: 15 August 2026.*