All Sheets — Column Reference

Full column definitions for every tab in the database sheet. Columns are listed in order — the index (0-based) is the positional contract the bot uses.

Warning: Column order is the code contract. Never reorder or insert columns in the middle. See Schema Overview.

---

01. Time Entries — A:H

Written by: log_time, log_sick_day, log_leave, approve_pto, approve_time_offUpdated by: edit_entry (full row rewrite A:H) Deleted by: delete_entry

Col	Idx	Field	Type	Notes
A	0	id	integer	Sequential, permanent. Never reused.
B	1	date	YYYY-MM-DD	Entry date (not necessarily today).
C	2	person	string	Deprecated — always "". Do not read.
D	3	user_id	integer	FK → 03. Users.id
E	4	project_id	integer	FK → 02. Projects.id
F	5	task_id	integer or ""	FK → 05. Tasks.id. Empty for sick/leave/PTO entries.
G	6	hours	float	Decimal hours, e.g. 4.5
H	7	notes	string	Optional. Labels: "Sick day", "Leave", "PTO".

Example: 101, 2026-03-14, , 5, 12, 3, 2.5, Development work

---

02. Projects — A:L

Written by: create_project, create_sprint, ensureProtectedProjectsUpdated by: set_project_budget (D), set_project_deadline (I), rename_project (B), assign_pm (G), update_sprint_status (L) Deleted by: delete_project

Col	Idx	Field	Type	Notes
A	0	id	integer	Sequential, permanent.
B	1	name	string	Unique per tenant.
C	2	client_id	integer	FK → 04. Clients.id
D	3	budget_hours	float or ""	Total hours cap. Set via set_project_budget.
E	4	alert_threshold	integer	Percent 0–100. Default 80.
F	5	status	string	"active" or "inactive" (lowercase).
G	6	pm_user_id	integer or ""	FK → 03. Users.id. Set via assign_pm.
H	7	project_category	string	"client" | "internal" | "pto" | "sick" | "leave" | "time_off"
I	8	deadline	YYYY-MM-DD or ""	Owner-only via set_project_deadline.
J	9	parent_project_id	integer or ""	Sprints only. FK → 02. Projects.id.
K	10	sprint_number	integer or ""	Sprints only. Auto-incremented.
L	11	sprint_status	string or ""	Sprints only: "planned" | "active"

Example: 42, Nike Rebrand, 7, 200, 80, active, 3, client, 2026-06-30, , ,

---

03. Users — A:H

Written by: add_user, ensure_userUpdated by: update_user_role (D), rename_user (C), deactivate_user (E), reactivate_user (E)

Col	Idx	Field	Type	Notes
A	0	id	integer	Sequential, permanent.
B	1	slack_user_id	string	Slack user ID, e.g. U01234567.
C	2	name	string	Display name.
D	3	role	string	"owner" | "manager" | "user". Code normalises "pm" → "manager" on read.
E	4	status	string	"active" | "inactive"
F	5	discipline_id	integer or ""	FK → 08. Disciplines.id
G	6	employment_type	string	"fte" | "contractor" | "vendor"
H	7	hours_per_week	float	Working hours per week. Default 40. Set explicitly for contractors. Used for capacity and overbooking calculations. Vendor value is a placeholder only.

Example: 5, U01234567, Sarah Chen, manager, active, 2, fte, 40

---

04. Clients — A:D

Written by: create_client, create_project (auto-creates client), ensureProtectedProjects (auto-creates "Internal")

Col	Idx	Field	Type	Notes
A	0	id	integer	Sequential.
B	1	name	string	Unique. "Internal" is auto-created.
C	2	status	string	"active" | "inactive"
D	3	notes	string	Optional. Column D has no header in the sheet.

Example: 3, Acme Corp, active, Enterprise client

---

05. Tasks — A:C

Written by: create_task

Col	Idx	Field	Type	Notes
A	0	id	integer	Sequential.
B	1	name	string	e.g. "Development", "Design", "QA"
C	2	is_billable	boolean string	"TRUE" or "FALSE"

Example: 3, Development, TRUE

---

06. Project_Tasks — A:D

Written by: enable_task_for_project — writes [project_id, task_id, budget_hours, alert_threshold]Deleted by: disable_task_for_project (row deletion), delete_project (cascade)

Presence = enabled. A row here means the task is enabled for that project. Absence means disabled. There is no enabled flag column — the CLAUDE.md documentation is incorrect on this point.

Col	Idx	Field	Type	Notes
A	0	project_id	integer	FK → 02. Projects.id
B	1	task_id	integer	FK → 05. Tasks.id
C	2	budget_hours	float or ""	Task-level hours budget. Empty if none set.
D	3	alert_threshold	integer	Percent. Default 80.

Example: 42, 3, 40, 80

---

07. Budgets (Allocations / Envelopes) — A:I

Written by: allocate_hoursUpdated by: update_allocation (D, F), cancel_allocation (F)

Despite the name, this sheet stores per-person hour envelopes (allocations), not project budgets. The project-level budget lives in 02. Projects.budget_hours.

Col	Idx	Field	Type	Notes
A	0	id	integer	Sequential.
B	1	user_id	integer	FK → 03. Users.id
C	2	project_id	integer	FK → 02. Projects.id
D	3	allocated_hours	float	Hours for this person on this project.
E	4	transferred_from_allocation_id	integer or ""	FK → 07. Budgets.id. Set when transferred; "" on initial creation.
F	5	status	string	"active" | "cancelled"
G	6	created_by	integer	FK → 03. Users.id — the Manager/Owner who created it.
H	7	created_at	YYYY-MM-DD	Creation date.
I	8	task_id	integer or ""	FK → 05. Tasks.id. Optional work-category tag.

Example: 17, 5, 42, 40, , active, 2, 2026-03-10, 3

---

08. Disciplines — A:C

Written by: add_discipline

Col	Idx	Field	Type	Notes
A	0	id	integer	Sequential.
B	1	name	string	e.g. "Engineering", "Design"
C	2	status	string	Always "active".

Example: 2, Design, active

---

09. Conversations — A:F

Written by: store_conversation (auto-called after every exchange) Read by: get_conversation (returns last N exchanges for current user today)

Col	Idx	Field	Type	Notes
A	0	conversation_id	string	Composite: {slack_user_id}_{YYYY-MM-DD}
B	1	timestamp	ISO 8601	Full timestamp.
C	2	user_id	string	Slack user ID (string, not integer DB id).
D	3	user_name	string	Display name at time of message.
E	4	message	string	The user's message.
F	5	response	string	VERA's response.

One row per exchange. Only today's rows are read (24-hour context window).

---

10. Pending_PTO — A:I

Written by: request_ptoUpdated by: approve_pto (status, approved_by, resolved_at)

Col	Idx	Field	Type	Notes
A	0	id	integer	Sequential.
B	1	user_id	integer	FK → 03. Users.id
C	2	date	YYYY-MM-DD	Requested date.
D	3	hours	float	Typically 8h (one work day).
E	4	status	string	"pending" | "approved" | "rejected"
F	5	created_at	YYYY-MM-DD	When submitted.
G	6	approved_by	integer or ""	FK → 03. Users.id. Set on resolution.
H	7	resolved_at	YYYY-MM-DD or ""	When approved/rejected.
I	8	notes	string	User-supplied reason.

Example: 8, 5, 2026-03-20, 8, approved, 2026-03-14, 2, 2026-03-14, Holiday trip

---

11. Unresolved_Items — A:I

Written by: createUnresolvedItem() — called by log_sick_day, log_leave, approve_pto, request_time_offUpdated by: resolve_item, dismiss_item (G:I)

Col	Idx	Field	Type	Notes
A	0	id	integer	Sequential.
B	1	type	string	"sick_conflict" | "leave_conflict" | "pto_conflict" | "time_off_request"
C	2	description	string	Human-readable, or JSON for time_off_request type.
D	3	affected_project_id	integer or ""	FK → 02. Projects.id
E	4	affected_user_id	integer or ""	FK → 03. Users.id
F	5	created_at	YYYY-MM-DD
G	6	status	string	"open" | "resolved" | "dismissed"
H	7	resolved_at	YYYY-MM-DD or ""
I	8	resolved_by	integer or ""	FK → 03. Users.id

---

12. Company_Calendar — A:D

Written by: add_company_holidayDeleted by: remove_company_holiday

Col	Idx	Field	Type	Notes
A	0	id	integer	Sequential.
B	1	date	YYYY-MM-DD
C	2	name	string	e.g. "Christmas Day"
D	3	type	string	"holiday" | "company_event"

Only "holiday" entries block PTO requests and are excluded from leave date ranges.

---

13. Rate_History — A:D

Written by: set_rate (append-only — never updates existing rows)

Col	Idx	Field	Type	Notes
A	0	id	integer	Sequential.
B	1	user_id	integer	FK → 03. Users.id
C	2	hourly_cost	float	e.g. 80.00 for $80/hr
D	3	effective_from	YYYY-MM-DD	Rate applies from this date forward.

Lookup rule: for an entry dated D, use MAX(effective_from) WHERE effective_from ≤ D. This preserves historical P&L accuracy when rates change.

Example:

1, 5, 75.00, 2025-01-01
2, 5, 85.00, 2026-03-01  ← rate increase from March 1st

---

14. Liabilities — A:H

Written by: create_liabilityUpdated by: cancel_liability (F — status)

Col	Idx	Field	Type	Notes
A	0	id	integer	Sequential.
B	1	project_id	integer	FK → 02. Projects.id
C	2	vendor_user_id	integer	FK → 03. Users.id (must be employment_type="vendor")
D	3	description	string	What this payment covers.
E	4	agreed_amount	float	Fixed payment in USD.
F	5	status	string	"active" | "cancelled"
G	6	created_by	integer	FK → 03. Users.id
H	7	created_at	YYYY-MM-DD

Example: 15, 12, 9, Dev House - Auth module, 5000, active, 1, 2026-03-15

---

22. Projects Master — A:I

Written by: create_project, create_sprint, ensureProtectedProjects

This sheet is joined to 02. Projects by project name (not ID). The join is case-insensitive.

Bot-written columns (A–H):

Col	Idx	Field	Type	Notes
A	0	project_name	string	Join key to 02. Projects.name.
B	1	client_name	string	Denormalised from 04. Clients.name.
C	2	type	string	"T&M" | "Fixed Price" | "Retainer" | "Pro Bono" | "Agile" | "sprint"
D	3	is_billable	boolean string	"TRUE" or "FALSE". Auto: false for Pro Bono.
E	4	contract_value	float or ""	Total contract value in USD.
F	5	hours_budget	float or ""	Annual hours (Retainer) or total hours (others).
G	6	status	string	"Active" (capital A) — different from 02. Projects which uses lowercase.
H	7	notes	string	Free text.
I	8	billing_rate	float or ""	Blended hourly billing rate. User-managed in the sheet.

Note: Columns I–O in the full reporting sheet version are user-formula columns (estimated hours, actual hours, variances). The bot writes only A–I. In reporting contexts, reference A:P for the full table.

Further reading

• The Invisible Contract in the Spreadsheet — why column order is treated as a hard contract and what happens when it breaks