Database Schema Overview

VERA's database is a Google Sheet with 14 operational tabs (01–14) plus 22. Projects Master. The bot reads and writes exclusively here. Clients never see this spreadsheet.

---

Two-spreadsheet architecture

Spreadsheet	Purpose	Who accesses it
Database sheet	All operational data — tabs 01–14 + 22	Bot (service account) only
Reporting sheet	Human-facing dashboards	Clients and owners (no bot access)

The reporting sheet imports data from the database sheet via IMPORTRANGE. The bot never reads or writes the reporting sheet.

---

The schema contract

Column order is the contract. The bot reads every sheet by positional index (row[0], row[1], etc.) — not by column name. This means:

• Never reorder columns — even one shift breaks all reads for that sheet
• Never rename a sheet tab — tab names are string literals throughout the code
• Never insert a column in the middle — only append at the end
• Adding a column at the end is safe

If you need to make a schema change, it requires four simultaneous updates:

1. The Google Sheet itself
2. The schema documentation
3. The column-offset code in mcp-server.js
4. The test fixtures in tests/mcp-routes.test.js

---

Foreign key relationships

03. Users ──┬── user_id ──→ 01. Time Entries
            ├── user_id ──→ 07. Budgets
            ├── pm_user_id → 02. Projects
            ├── discipline_id → 08. Disciplines
            ├── created_by ──→ 07. Budgets
            ├── user_id ──→ 10. Pending_PTO
            └── affected_user_id → 11. Unresolved_Items

02. Projects ┬── project_id → 01. Time Entries
             ├── project_id → 06. Project_Tasks
             ├── project_id → 07. Budgets
             ├── client_id → 04. Clients
             ├── parent_project_id → 02. Projects (self-ref, sprints)
             └── affected_project_id → 11. Unresolved_Items

04. Clients ──── client_id → 02. Projects

05. Tasks ──┬── task_id ──→ 01. Time Entries
            ├── task_id ──→ 06. Project_Tasks
            └── task_id ──→ 07. Budgets

22. Projects Master ── project_name →→ 02. Projects.name  (string join, not ID)

---

Tab summary

Tab	Purpose	Key operations
01. Time Entries	Every logged hour	Append, edit, delete
02. Projects	Project definitions	Append, update various cells
03. Users	Team members	Append, update role/status
04. Clients	Client organisations	Append
05. Tasks	Work categories	Append
06. Project_Tasks	Tasks enabled per project	Append (enable), delete (disable)
07. Budgets	Per-person hour envelopes	Append, update hours/status
08. Disciplines	Functional roles	Append
09. Conversations	24hr chat history	Append
10. Pending_PTO	Time off requests	Append, update status
11. Unresolved_Items	Flagged operational issues	Append, update status
12. Company_Calendar	Holidays and events	Append, delete
13. Rate_History	Hourly cost per person (versioned)	Append only
14. Liabilities	Vendor payment commitments	Append, update status
22. Projects Master	Financial metadata per project	Append (on create)

See All Sheets for the complete column-by-column reference.

---

ID strategy

All IDs follow the same pattern:

new_id = MAX(existing ids in sheet) + 1

IDs are permanent and sequential — never reused, even when records are deleted. This ensures time entry #42 always refers to the same entry, enabling reliable audit trails and edit/delete operations via natural language.

---

Write-Ahead Log (WAL)

VERA uses a Write-Ahead Log pattern to handle the Google Sheets read-after-write race condition. After writing to a sheet, the new row is cached in memory so that an immediate read returns the correct data — even before Google's backend has propagated the write.

Three sheets currently have WAL protection:

• Budgets (07. Budgets) — new envelopes, updates, and cancellations are visible immediately after write
• Conversations (09. Conversations) — ensures conversational context is available for immediate follow-up replies
• Time Entries (01. Time Entries) — newly logged, edited, or deleted entries are reflected in project status queries within seconds

This is why you don't see "just logged but not showing up" issues despite Google Sheets' eventual consistency model.