---
name: baserow-schema-design
description: Design and build Baserow databases from spreadsheets or requirements — normalization, formula syntax, link rows, API-driven schema creation, and E2E verification.
---

# Baserow Schema Design

## When to Load This Skill
- Migrating spreadsheets (Google Sheets, Excel) to Baserow
- Designing Baserow tables, fields, views, and permissions
- Debugging Baserow formula errors
- Programmatically creating Baserow schema via API
- Building a "moat" (護城河) permission model in Baserow

## Workflow Preference (the operator / twwater-ai)
**Before making fixes or gap analyses: confirm strategic alignment first.**
When the user describes their business domain and asks "does this schema cover what we need?",
the correct response is:
1. Assess whether the overall design is sound (yes/no + why)
2. List gaps concisely
3. Ask which gap to address first — do NOT jump into fixing all gaps unprompted

The user will redirect if you go too deep too fast ("重點不是⋯⋯先不搞那些").
Offer a prioritised list and wait for direction before executing changes.

---

## Design Principles

### Anti-patterns to avoid (expensive mistakes)

**1. Over-normalisation for small datasets**
A 100-row spreadsheet does NOT need 11 tables with link rows everywhere. Baserow link rows require manual selection in the UI — every extra link is user friction. Rule of thumb:
- < 200 rows per entity → single table, use Single Select for repeated values
- Repeated string values (designer names, org names) → Single Select field, NOT a separate table + Link Row
- Reference data that barely changes (unit names) → Single Select or separate table depending on count

**2. Splitting one lifecycle into multiple tables**
Wrong: `design_phase` table + `construction_phase` table for the same project.
Right: One `engineering_works` table with `current_stage` field (Single Select). Same project, same row, different columns become relevant as it progresses.

**Why:** Baserow has no JOIN in its UI. Cross-table queries require manual linking. If the user needs to see "all projects in Stage 2 with budget > X", a two-table design forces them to bounce between tables.

**3. Link Row for simple lookups**
Wrong: `supervisor` Link Row → `staff_members` table for 15 staff members.
Right: `supervisor_name` Text field. Baserow link rows require users to search/pick from a modal every time — painful for fields updated daily.

**Reserve Link Row for true M:N relationships** where the linked records have their own lifecycle (e.g., a progress log entry that links to multiple projects).

---

## Field Type Reference

| Data | Baserow type | Notes |
|---|---|---|
| Short name / code | `text` | |
| Long content, notes | `long_text` | |
| Integer count | `number`, decimal_places=0 | |
| Money / percentage | `number`, decimal_places=1 or 2 | |
| True/False flag | `boolean` | |
| Dropdown (fixed options) | `single_select` | provide `select_options` array |
| Multi-value dropdown | `multiple_select` | |
| Derived / calculated | `formula` | see formula syntax below |
| Calendar date | `date`, date_format=`ISO` | |
| Date + time | `date`, date_include_time=true | |
| Relation to other table | `link_row`, link_row_table_id=N | use sparingly — see above |
| Count of linked rows | `count` | auto-updates |
| Value from linked row | `lookup` | |
| File upload | `file` | |
| Auto-increment ID | auto_number (type `auto_number`) | useful as business key |
| Creation timestamp | `created_on` | read-only, immutable |
| Last-modified stamp | `last_modified` | read-only, updates on any write |

---

## Formula Syntax (Baserow 1.35)

**⚠️ Critical: `date_diff` unit must be `'d'` not `'days'`**

```
# WRONG — returns NaN
date_diff('days', today(), field('planned_completion'))

# CORRECT
date_diff('d', today(), field('planned_completion'))
```

**Null check for date fields:** `if(field('date_field') = '', ...)` fails for date type.
Use `is_null()`:

```
# Days remaining until a date (positive = future, negative = overdue)
if(not(is_null(field('planned_completion'))),
   date_diff('d', today(), field('planned_completion')),
   0)

# Days remaining — 0 if already completed (actual date set)
if(not(is_null(field('actual_completion'))),
   0,
   if(not(is_null(field('planned_completion'))),
      date_diff('d', today(), field('planned_completion')),
      0))
```

**Other useful functions:**
```
# Arithmetic
field('budget_a') + field('budget_b') + field('budget_c')

# Conditional text
if(field('progress_diff') >= 0, '正常', if(field('progress_diff') >= -5, '注意', '落後'))

# Rounding
round(field('total_expenditure') / field('total_budget') * 100, 1)

# Division guard
if(field('total_budget') > 0, round(field('total_expenditure') / field('total_budget') * 100, 1), totext(0))
```

**NOT available in Baserow 1.35:** `datetime_diff`, `is_blank`, `todays_date`, `timestamp_diff`

---

## API-Driven Schema Creation

### Auth
```python
import requests

BASE = "http://localhost:8089"  # or http://HOST:PORT

# JWT for admin operations (field creation, schema changes)
r = requests.post(f"{BASE}/api/user/token-auth/",
    json={"username": "admin@example.com", "password": "Password"})
jwt = r.json()["token"]
jh = {"Authorization": f"JWT {jwt}", "Content-Type": "application/json"}

# API Token for data operations (CRUD rows)
th = {"Authorization": f"Token {BASEROW_API_TOKEN}", "Content-Type": "application/json"}
```

### Create table + fields
```python
# Create table (primary field auto-created as "Name" type text)
tbl = requests.post(f"{BASE}/api/database/tables/database/{DB_ID}/",
    headers=jh, json={"name": "engineering_works"}).json()
TABLE_ID = tbl["id"]

# Rename primary field
fields = requests.get(f"{BASE}/api/database/fields/table/{TABLE_ID}/", headers=jh).json()
primary_id = fields[0]["id"]
requests.patch(f"{BASE}/api/database/fields/{primary_id}/", headers=jh, json={"name": "project_name"})

# Add single_select field with options
requests.post(f"{BASE}/api/database/fields/table/{TABLE_ID}/", headers=jh, json={
    "name": "current_stage",
    "type": "single_select",
    "select_options": [
        {"value": "設計規劃", "color": "blue"},
        {"value": "施工執行", "color": "orange"},
        {"value": "已完工", "color": "green"},
        {"value": "已結案", "color": "gray"},
        {"value": "已停止", "color": "red"},
    ]
})

# Add formula field
requests.post(f"{BASE}/api/database/fields/table/{TABLE_ID}/", headers=jh, json={
    "name": "completion_remaining",
    "type": "formula",
    "formula": "if(not(is_null(field('actual_completion'))), 0, if(not(is_null(field('planned_completion'))), date_diff('d', today(), field('planned_completion')), 0))"
})

# Add link_row field
requests.post(f"{BASE}/api/database/fields/table/{TABLE_ID}/", headers=jh, json={
    "name": "progress_logs",
    "type": "link_row",
    "link_row_table_id": PROGRESS_LOG_TABLE_ID
})
```

### Insert rows (with token, not JWT)
```python
# Single row
r = requests.post(
    f"{BASE}/api/database/rows/table/{TABLE_ID}/?user_field_names=true",
    headers=th,
    json={"project_name": "北區管線工程A", "current_stage": "施工執行", "is_active": True}
)

# Linked row — pass row IDs in a list
r = requests.post(
    f"{BASE}/api/database/rows/table/{PROGRESS_LOG_ID}/?user_field_names=true",
    headers=th,
    json={"log_title": "第一週進度", "work": [PROJECT_ROW_ID]}  # link row uses list of IDs
)
```

### Soft delete (NEVER hard delete for audit tables)
```python
# Set is_active=false, never call DELETE
requests.patch(
    f"{BASE}/api/database/rows/table/{TABLE_ID}/{ROW_ID}/?user_field_names=true",
    headers=th, json={"is_active": False}
)
```

---

## Single Select Options — Must Pre-Populate ALL Values

**Critical:** Baserow `single_select` fields reject any value not in their `select_options` list with `ERROR_REQUEST_BODY_VALIDATION: "The provided select option value 'X' is not a valid select option."` There is NO auto-create-on-write behaviour.

**Pattern:** Before migrating data, collect all unique values from the source and add them to the field:

```python
# 1. Collect all unique values from source data
import csv
subjects = set()
with open('data.csv') as f:
    for row in csv.DictReader(f):
        v = row.get('科目', '').strip()
        if v and v not in ('#N/A', ''):
            subjects.add(v)

# 2. Get current field options
jwt = get_jwt()
field_resp = requests.get(f"{BASE}/api/database/fields/{FIELD_ID}/",
    headers={"Authorization": f"JWT {jwt}"}).json()
existing = [o['value'] for o in field_resp.get('select_options', [])]
missing = [v for v in subjects if v not in existing]

# 3. Add all missing options in one PATCH
new_options = field_resp['select_options'] + [{'value': v, 'color': 'blue'} for v in missing]
requests.patch(f"{BASE}/api/database/fields/{FIELD_ID}/",
    headers={"Authorization": f"JWT {jwt}", "Content-Type": "application/json"},
    json={"select_options": new_options})
```

**Do this BEFORE any bulk row insert** — otherwise rows with unknown select values will fail silently (or loudly with 400 errors).

---

## Number Field Decimal Places — Strict Enforcement

Baserow number fields enforce `decimal_places` strictly. Common traps:

| Field type | Typical config | Python must send |
|-----------|----------------|-----------------|
| Budget (NT$) | `decimal_places=0` | `int(val)` not `float(val)` |
| Progress % | `decimal_places=1` | `round(float(val), 1)` |
| Percentage | `decimal_places=2` | `round(float(val), 2)` |

Error: `Ensure that there are no more than N decimal places.`

Always use `int()` for integer-configured fields and `round(x, N)` for decimal fields. Never send Python floats like `9.290000000000001` to a `decimal_places=1` field.

```python
def parse_int(val):
    """Safe int parser — handles commas, spaces, trailing decimals"""
    if not val or str(val).strip() in ('', '#N/A', '-'):
        return None
    cleaned = re.sub(r'[,\s]', '', str(val).strip())
    try:
        return int(float(cleaned))  # float() first handles "10,000" → 10000
    except:
        return None

def parse_pct(val, decimals=1):
    """Safe percentage parser — strips %, enforces decimal_places"""
    if not val or str(val).strip() in ('', '#N/A'):
        return None
    try:
        return round(float(str(val).strip().replace('%', '')), decimals)
    except:
        return None
```

---

## Renaming Formula Fields — Must Include `formula` String in PATCH

Baserow formula fields reject a rename-only PATCH with `ERROR_REQUEST_BODY_VALIDATION: formula is required`.
You must GET the current formula string first, then PATCH with both `name` and `formula`:

```python
# WRONG — 400 error
requests.patch(f"{BASE}/api/database/fields/{fid}/", headers=jh, json={"name": "新名稱"})

# CORRECT
field = requests.get(f"{BASE}/api/database/fields/{fid}/", headers=jh).json()
requests.patch(f"{BASE}/api/database/fields/{fid}/", headers=jh,
    json={"name": "新名稱", "formula": field["formula"]})
```

---

## Duplicate Field Names Within Same Table — `ERROR_FIELD_WITH_SAME_NAME_ALREADY_EXISTS`

Baserow rejects a rename if another field in the same table already has the target name.
Common trap: Baserow scaffolds both an `Active` (boolean) and an `is_active` (boolean) field.
If you rename `Active` → `啟用`, then renaming `is_active` → `啟用` will fail.

**Fix:** Give the secondary boolean a distinct name, e.g. `列管中` (soft-delete flag) vs `啟用` (display toggle).

```python
# Pattern: check for collision before bulk rename
existing_names = {f["name"] for f in table_fields}
if target_name in existing_names:
    target_name = target_name + "狀態"  # or use domain-specific alternative
```

---

## Renaming Table Names (not just fields)

Use the tables endpoint, not the fields endpoint:

```python
requests.patch(f"{BASE}/api/database/tables/{table_id}/",
    headers=jh, json={"name": "工程管控"})
```

Endpoint: `PATCH /api/database/tables/{table_id}/`
This is separate from `PATCH /api/database/fields/{field_id}/`.

---

## Bulk Rename via CrewAI — Concurrent Lock Failures

When a CrewAI agent calls `rename_baserow_field` in a tight loop, ~30% of fields fail with
Baserow API lock conflicts (concurrent request limit). The agent reports them as failures.

**Fix:** After the crew completes, run a verification + retry pass in `execute_code` with
`time.sleep(0.15)` between each call:

```python
for fid, target in mappings:
    jwt = get_jwt()
    r = requests.get(f"{BASE}/api/database/fields/{fid}/", headers=jh)
    if r.json()["name"] == target:
        continue  # already done
    requests.patch(f"{BASE}/api/database/fields/{fid}/", headers=jh,
        json={"name": target})
    time.sleep(0.15)
```

This catches all crew-failed fields without re-running the full crew.

---

## JWT Token Expiry — Extremely Short (< 2 seconds in practice)

Baserow JWTs expire very quickly. Any pattern that obtains a token and then uses it after even a small delay will get `ERROR_INVALID_ACCESS_TOKEN`.

**Correct pattern:** Obtain token in the same shell command as the API call, or refresh every N rows in Python:

```bash
# One-shot: get token AND use it in the same shell pipeline
ACCESS=$(curl -s -X POST "$BASE/api/user/token-auth/" \
  -H "Content-Type: application/json" \
  -d '{"email":"admin@x.ai","password":"pass"}' \
  | python3 -c "import sys,json; print(json.load(sys.stdin)['access_token'])")
curl -s "$BASE/api/database/tables/database/$DB_ID/" -H "Authorization: JWT $ACCESS"
```

```python
# In Python bulk migration: refresh every N rows
import time
_jwt_cache = {"token": None, "ts": 0}
def get_jwt():
    if _jwt_cache["token"] and time.time() - _jwt_cache["ts"] < 60:
        return _jwt_cache["token"]
    r = requests.post(f"{BASE}/api/user/token-auth/",
        json={"email": EMAIL, "password": PASS}, timeout=15)
    t = r.json()["access_token"]
    _jwt_cache.update({"token": t, "ts": time.time()})
    return t
```

**Never** obtain a token in one `execute_code` call and try to use it in the next — it will be expired.

---

## Field & Table Renaming — API Pitfalls

### Formula fields require `formula` in PATCH
Renaming a formula field with only `{"name": "新名稱"}` returns `ERROR_REQUEST_BODY_VALIDATION: formula is required`.
Always GET the field first to retrieve the formula string, then include it in the PATCH:

```python
r = requests.get(f"{BASE}/api/database/fields/{field_id}/", headers=jh)
formula = r.json().get("formula", "")
requests.patch(f"{BASE}/api/database/fields/{field_id}/", headers=jh,
    json={"name": new_name, "formula": formula})
```

### Concurrency locking causes silent failures at scale
Renaming 70+ fields in parallel or with tight loops causes `ERROR_LOCKED_FIELD` or 400s.
**Fix:** Add `time.sleep(0.15)` between each PATCH call. Always verify actual current name before renaming (GET → compare → PATCH only if needed).

### Duplicate field names within a table — `ERROR_FIELD_WITH_SAME_NAME_ALREADY_EXISTS`
Baserow has two boolean pattern fields (`Active` + `is_active`) in many tables. Both cannot be renamed to the same Chinese name.
**Convention:** `Active` → `啟用`, `is_active` → `列管中` (active management flag in TW water context).

### Table renaming endpoint
```python
requests.patch(f"{BASE}/api/database/tables/{table_id}/",
    headers=jh, json={"name": "中文表名"})
```
No extra fields required — tables are simpler than fields.

### Crew-for-mapping + Hermes-for-execution hybrid pattern
For bulk rename tasks (70+ fields), use CrewAI for the **judgment layer** (English→Chinese mapping decisions) and Hermes `execute_code` for the **execution layer** (actual API calls). Crew produces a `field_mapping.json`, Hermes verifies and retries failures.

```
1. Crew: Schema Analyst → produces field_mapping.json (JSON list of {field_id, old_name, new_name, table})
2. Hermes execute_code: GET current name → skip if already correct → PATCH with 0.15s sleep
3. Hermes execute_code: separate pass for formula fields (GET formula → PATCH with formula included)
4. Hermes execute_code: verify pass — pull all tables, assert 0 English-named fields remain
```

This pattern avoids the crew hallucinating rename success while also keeping the naming judgment in an LLM (not hardcoded).

## Row Deletion — No Batch Endpoint

`DELETE /api/database/rows/table/{id}/batch/` returns **405 Method Not Allowed**. There is no batch delete endpoint. Delete rows individually:

```python
def delete_all_rows(table_id):
    """Delete all rows from a table — needed for clean re-migration."""
    while True:
        jwt = get_jwt()
        resp = requests.get(f"{BASE}/api/database/rows/table/{table_id}/?size=200",
            headers={"Authorization": f"JWT {jwt}"}).json()
        rows = resp.get("results", [])
        if not rows:
            break
        for row in rows:
            requests.delete(f"{BASE}/api/database/rows/table/{table_id}/{row['id']}/",
                headers={"Authorization": f"JWT {jwt}"})
```

At ~33 rows/sec (with token refresh), 100 rows ≈ 3 seconds. Plan accordingly for large tables.

---

## API Token Permissions — Critical Bug

**Baserow creates API tokens with `delete: true` by default**, even when you don't ask for it.
Always PATCH the token immediately after creation:

```python
tok = requests.post(f"{BASE}/api/database/tokens/", headers=jh,
    json={"name": "hermes-agent", "workspace": WORKSPACE_ID}).json()
# IMMEDIATELY fix:
requests.patch(f"{BASE}/api/database/tokens/{tok['id']}/", headers=jh,
    json={"permissions": {"create": True, "read": True, "update": True, "delete": False}})
api_key = tok["key"]  # this is the token for data operations
```

The token `id` and `key` are only in the creation response — GET returns masked key. Save both.

---

## Link Row `link_row_related_table_id` returns `None` from API

This is a known Baserow API response quirk. The `link_row_related_table_id` field in GET responses for fields returns `None` even when the link is working correctly. **Don't use this to verify the link.** Instead, functionally test by inserting a row with the link field populated:

```python
r = requests.post(f"{BASE}/api/database/rows/table/{PROGRESS_LOG_ID}/?user_field_names=true",
    headers=th, json={"log_title": "TEST", "work": [1]})  # work is the link_row field
assert r.status_code in (200, 201), f"Link row insert failed: {r.text}"
```

---

## Spreadsheet Migration Strategy

### Analyse before designing
1. **Count actual data rows** — strip trailing empty rows first (use `csv.reader`, check `if any(c.strip() for c in row)`)
2. **Find unique values per column** (low cardinality = good Single Select candidate)
3. **Identify cross-sheet relationships** by matching on project name (often no ID column)
4. **Classify columns**: identifier / descriptive / status / date / calculated / free-text

```python
# Find columns that are good Single Select candidates (< 20 unique values)
from collections import Counter
for col_idx, col_name in enumerate(headers):
    vals = set(row[col_idx].strip() for row in data_rows if col_idx < len(row) and row[col_idx].strip())
    if 1 < len(vals) <= 20:
        print(f"{col_name}: {sorted(vals)}")
```

### Google Sheets merged-cell (合併儲存格) forward-fill pattern

Google Sheets with merged cells exports as CSV with blank cells where the merge spans multiple rows. The **first non-blank row** has the value; subsequent rows in the merge are blank. This is the most common pattern for government engineering spreadsheets.

```python
def load_sheet_grouped(csv_path):
    """
    Group rows by the first column (project identifier),
    forward-filling blank values from merged cells.
    Returns: list of dicts, one per unique project (with sub-rows aggregated).
    """
    with open(csv_path, encoding='utf-8') as f:
        all_rows = list(csv.reader(f))

    headers = all_rows[1]          # Row 0 = blank header row (common in TW gov sheets)
    data_rows = all_rows[2:]       # Row 2+ = data

    last_key = ''
    groups = {}
    group_order = []

    for row in data_rows:
        if not any(c.strip() not in ('', '#N/A') for c in row):
            continue               # skip completely blank rows

        key = row[0].strip()
        if key:
            last_key = key
        if not last_key:
            continue

        rdict = dict(zip(headers, row))
        if last_key not in groups:
            groups[last_key] = []
            group_order.append(last_key)
        groups[last_key].append(rdict)

    # Each group = one unique project; aggregate numeric fields across sub-rows
    projects = []
    for key in group_order:
        rows = groups[key]
        first = rows[0]
        total_budget = sum(parse_int(r.get('114年預算', '')) or 0 for r in rows)
        # ... aggregate other numeric columns ...
        projects.append({
            'project_name': key,
            # metadata from first row, aggregated numbers from all rows
            'budget_114': total_budget if total_budget > 0 else None,
            # Notes: store all sub-row subject codes for reference
            'Notes': ', '.join(r.get('科目', '') for r in rows if r.get('科目', '').strip()),
        })
    return projects
```

**Key insight:** 1,108 raw CSV rows → 103 unique projects after grouping + blank-row filtering. Always check actual unique key count vs raw row count before designing your migration strategy.

### Google Sheets CSV double-encoding fix
Google Sheets export via `requests.get()` may arrive as mojibake (Chinese chars as `å·¥ç¨`). Fix:
```python
raw = response.content
fixed_text = raw.decode('utf-8').encode('latin-1').decode('utf-8')
```

### One-table lifecycle pattern (recommended for < 500 rows)
```
engineering_works (main table)
  ├── design_* columns      # relevant when current_stage = 設計規劃
  ├── construction_* columns # relevant when current_stage = 施工執行
  └── current_stage         # drives which columns are active

progress_log (append-only, linked to engineering_works)
  └── work (link_row → engineering_works)

ai_audit_log (append-only, never delete)
  └── work_link (link_row → engineering_works)

organizations (small reference, 7 rows)
contractors (small reference, ~30 rows)
```

---

## View Design (護城河 / Moat mechanism)

Baserow Views are the primary way to implement role-based data access without physical table isolation.

```
engineering_works table Views:
  🏗️ 施工管理儀表板   Filter: current_stage = 施工執行, is_active = true
                     Group: organization, Sort: completion_remaining ASC
  ⚠️ 逾期預警         Filter: completion_remaining < 7 AND actual_completion is null
  💰 預算執行率       Group: organization, Sort: achievement_rate ASC
  📋 設計規劃追蹤     Filter: current_stage = 設計規劃
  🔒 承辦人填報       Hide: budget_*, expenditure_*, achievement_rate (sensitive fields)
                     Only show: project_name, current_stage, weekly_report, construction_current_status
```

**Key principle:**承辦人 (staff) gets a View that hides financial columns — not a separate table. The moat is in the View's field visibility settings, not physical table isolation.

---

## E2E Verification Script Template

After any schema build, always run a real verification (not a CrewAI agent that may hallucinate):

```python
# scripts/verify_schema.py
import requests, json, time, sys

BASE = "http://localhost:8089"
PASS_THRESHOLD = 0.85  # 85% pass rate = approved

def login():
    r = requests.post(f"{BASE}/api/user/token-auth/",
        json={"username": "admin@...", "password": "..."})
    return r.json()["token"]

jwt = login()
jh = {"Authorization": f"JWT {jwt}"}

# 1. Tables exist?
tables = {t["name"]: t["id"] for t in
    requests.get(f"{BASE}/api/database/tables/database/78/", headers=jh).json()}
for name in ["engineering_works", "progress_log", "ai_audit_log", "organizations", "contractors"]:
    assert name in tables, f"MISSING TABLE: {name}"

ew_id = tables["engineering_works"]

# 2. Formula fields exist?
fields = requests.get(f"{BASE}/api/database/fields/table/{ew_id}/", headers=jh).json()
formula_names = {f["name"] for f in fields if f["type"] == "formula"}
assert "design_total_budget" in formula_names
assert "progress_status" in formula_names
assert "completion_remaining" in formula_names

# 3. Formula values correct?
r = requests.post(f"{BASE}/api/database/rows/table/{ew_id}/?user_field_names=true",
    headers={"Authorization": f"Token {API_TOKEN}", "Content-Type": "application/json"},
    json={"project_name": "VERIFY_TEST", "budget_113_retained": 1000000,
          "budget_114": 2000000, "budget_115": 3000000,
          "scheduled_progress_pct": 60.0, "actual_progress_pct": 45.0,
          "planned_completion": "2026-12-31"})
row_id = r.json()["id"]
time.sleep(1)
row = requests.get(f"{BASE}/api/database/rows/table/{ew_id}/{row_id}/?user_field_names=true",
    headers={"Authorization": f"Token {API_TOKEN}"}).json()

assert float(row["design_total_budget"]) == 6000000.0, f"budget formula wrong: {row['design_total_budget']}"
assert float(row["progress_diff"]) == -15.0, f"diff formula wrong: {row['progress_diff']}"
assert row["progress_status"] == "落後", f"status formula wrong: {row['progress_status']!r}"

# Soft delete test row
requests.patch(f"{BASE}/api/database/rows/table/{ew_id}/{row_id}/?user_field_names=true",
    headers={"Authorization": f"Token {API_TOKEN}", "Content-Type": "application/json"},
    json={"is_active": False})

print("✅ All checks passed")
```

## Schema Review & ER Diagram — User Preference

**When a user asks for a schema overview or ER diagram, produce a VISUAL diagram first — not a text table.**

The pattern that works:
1. Fetch all fields via API (`/api/database/fields/table/{id}/` for each table)
2. Identify `link_row` fields — these are the real FK relationships
3. Identify `text` fields that reference other tables loosely (call these "text reference / 鬆散引用")
4. Generate an HTML/SVG architecture diagram (use `architecture-diagram` skill) — dark-themed, tables as boxes, arrows for link_row relations, dashed arrows for text refs
5. Send the `.html` file as MEDIA attachment

A text bullet-list schema dump is **not** what users mean when they ask for an ER diagram. The first response should always be visual.

### Gap Analysis Pattern (schema vs business requirements)

When the user describes business requirements and asks if the schema covers them, run a structured Gap Analysis:

| Requirement | Mapping | Status |
|---|---|---|
| [requirement] | [field or table] | ✅/⚠️/❌ |

Categorise gaps by severity (P0/P1/P2) and call out:
- Missing fields
- Isolated tables (no link_row to core)
- Text references that should be link_rows
- Missing lifecycle tracking fields

### Baserow Field Rename via CrewAI (中文化 pattern)

When renaming many fields to match client's original naming (e.g. English → Chinese for demo):

**2-agent crew pattern:**
```
Schema Analyst (no tools) → Database Executor (with rename tool)
```

```python
@tool("rename_baserow_field")
def rename_baserow_field(field_id: int, new_name: str) -> str:
    jwt = get_jwt()  # refresh every 55s
    r = requests.patch(
        f"{BASEROW_BASE}/api/database/fields/{field_id}/",
        headers={"Authorization": f"JWT {jwt}", "Content-Type": "application/json"},
        json={"name": new_name}, timeout=15
    )
    if r.status_code in (200, 201):
        return f"OK: field {field_id} → '{r.json().get('name', new_name)}'"
    return f"ERROR: {r.status_code} {r.text[:200]}"
```

- Schema Analyst task: embed full current schema + original sheet column names as string constants → output JSON mapping list
- Executor task: iterate every item, call `rename_baserow_field` per field, then `save_mapping_report`
- Set `max_iter=120` for the executor (70+ fields × ~1-2 calls each)
- JWT must refresh every <55 seconds — use `_jwt_cache` pattern

**Naming rules for TWWater / 台水 projects:**
- Use exact original sheet column names where they exist
- `Notes` → `備註`, `is_active` → `啟用`, `updated_by` → `更新者`
- `link_row` fields: use Chinese relation names (e.g. `progress_log` → `進度日誌`)
- `formula` fields: translate semantically (e.g. `achievement_rate` → `達成率`)

## Renaming Fields and Tables to Chinese (Demo/Client-Facing)

When client needs Chinese field names for demo screenshots, rename via API — not in the UI.

### Rename all fields in one pass (Python)
```python
import requests, time

def get_jwt(base, email, pw):
    r = requests.post(f"{base}/api/user/token-auth/", json={"email":email,"password":pw})
    return r.json()["access_token"]

def rename_field(base, fid, new_name, formula=None):
    """Formula fields MUST include formula string in the PATCH body, else 400."""
    jwt = get_jwt(base, EMAIL, PASS)
    body = {"name": new_name}
    if formula:
        body["formula"] = formula
    r = requests.patch(f"{base}/api/database/fields/{fid}/",
        headers={"Authorization": f"JWT {jwt}", "Content-Type": "application/json"},
        json=body, timeout=10)
    return r.status_code, r.json().get("name", r.text[:80])
```

**Pitfalls for field renaming:**
1. **Formula fields need `formula` in PATCH** — `PATCH /api/database/fields/{id}/` with only `{"name":"..."}` returns 400 for formula fields. GET the field first to grab the formula string, then PATCH with both `name` and `formula`.
2. **Duplicate name collision** — if two fields in the same table share the same new name (e.g. `Active` + `is_active` both → `啟用`), the second rename fails with `ERROR_FIELD_WITH_SAME_NAME_ALREADY_EXISTS`. Rename one to a distinct name first (e.g. `列管中` for the soft-delete flag, `啟用` for the display toggle).
3. **Rate limit / lock** — large batches (70+ fields) hit Baserow locking. Add `time.sleep(0.15)` between calls. Crew agents calling in parallel will fail; always use sequential retry loop.
4. **Rename tables** via `PATCH /api/database/tables/{table_id}/` with `{"name": "中文名稱"}` — separate endpoint from fields.

### Canonical Chinese names for system fields
| English | Chinese | Notes |
|---|---|---|
| Notes / long_text | 備註 | |
| Active (boolean) | 啟用 | display toggle |
| is_active (boolean) | 列管中 | soft-delete flag — use distinct name to avoid collision |
| updated_by | 更新者 | |
| link_row to engineering_works | 工程 or 工程連結 | |
| link_row to progress_log | 進度日誌 | |

## PostgreSQL Internals (for direct DB access / Metabase integration)

When connecting external tools (Metabase, psql, custom scripts) directly to Baserow's PostgreSQL:

| Concept | Actual value | Common wrong assumption |
|---|---|---|
| Data table name | `database_table_{id}` | ~~`baserow_table_{id}`~~ |
| Select option lookup | `database_selectoption` | ~~`database_select_option`~~ |
| Boolean field storage | char `'t'` / `'f'` | ~~`true` / `false`~~ |
| single_select storage | integer option ID | ~~text value string~~ |
| Formula field type | `text` in PG | ~~numeric / generated column~~ |

**single_select JOIN pattern:**
```sql
-- single_select fields store integer IDs — always JOIN to get text
SELECT s.value as "單位"
FROM database_table_282 t
JOIN database_selectoption s ON s.id = t.field_2757 AND s.field_id = 2757
WHERE field_3093 = 't'  -- boolean: char 't', not SQL true
```

**Grant for external reader:**
```bash
# Must grant on database_table_* (not baserow_table_*)
sudo docker exec <db> psql -U postgres -d baserow -c "
DO \$\$ DECLARE r RECORD; BEGIN
  FOR r IN SELECT tablename FROM pg_tables WHERE schemaname='public' AND tablename LIKE 'database_table_%'
  LOOP EXECUTE 'GRANT SELECT ON TABLE public.' || quote_ident(r.tablename) || ' TO metabase_reader'; END LOOP;
END \$\$;"
sudo docker exec <db> psql -U postgres -d baserow -c "GRANT SELECT ON database_selectoption TO metabase_reader;"
```

**Rename all fields/tables to Chinese for Demo:**
Baserow PATCH `/api/database/fields/{id}/` renames fields. PATCH `/api/database/tables/{id}/` renames tables.
For formula fields, you must include `formula` string in the PATCH body or it returns 400.
For duplicate field names in the same table, rename conflicting field first before assigning the desired name.

## References
- `references/twwater-schema-design.md` — design decisions for 台水 工程管控系統, role gaps, known issues
- `references/twwater-metabase-dashboard-sql.md` — complete SQL for 4 dashboards, field_id quick reference, formula casting patterns
- `references/baserow-dashboard-alternatives.md` — BI tool comparison (Metabase vs Superset vs Redash vs Grafana), GCP firewall note
- `references/google-sheets-migration-patterns.md` — merged-cell grouping, two-pass upsert, data type conversion
- `references/metabase-baserow-integration.md` — Metabase CE + Baserow PG integration: read-only user SQL, Docker Compose snippet, Caddy routing, formula/link_row/single_select query patterns, field alias JSON, Hermes cron scheduled-report workaround
- `references/twwater-metabase-dashboard-sql.md` — complete SQL for all 台水 twwater-ai Metabase dashboard charts (4 dashboards, field_id quick reference)
- `references/baserow-dashboard-alternatives.md` — BI tool comparison (Metabase/Superset/Redash/Grafana/Appsmith), scheduled report workarounds, Metabase CE docker-compose snippet, 台水 starter queries
- `references/twwater-er-diagram-pattern.md` — ER diagram HTML/SVG template for Baserow schemas with link_row and text-ref distinction