DynamoDB Tables

All tables use PAY_PER_REQUEST billing mode (no provisioned capacity to manage).

Entity Relationship Diagram

erDiagram
    CUSTOMER ||--o{ CUSTOMER_USER : "has users"
    CUSTOMER ||--o{ PILLAR : "has pillars"
    CUSTOMER ||--o{ TIME_BILL : "billed to"
    CUSTOMER ||--o{ CUSTOMER_FOCUS : "focused by"
    CUSTOMER ||--o{ SLACK_MAPPING : "mapped from"
    TASK ||--o{ TIME_ENTRY : "tracked by"
    TASK ||--o{ TIME_BILL : "generates"
    TASK }o--o| TASK : "parent/children"
    TASK }o--o{ TASK : "blocked by"
    PILLAR ||--o{ TIME_BILL : "categorizes"
    USER }|--o{ TIME_ENTRY : "records"
    USER }|--o| ACTIVE_TIMER : "has active"
    XERO_TOKEN ||--|| XERO_TOKEN : "singleton"

Table Schemas

task-time-tasks

Stores all tasks with status tracking and relationships.

Key	Type
id (Partition)	String

Field	Type	Description
id	String	UUID
title	String	Task title
description	String?	Detailed description
link	String?	External link (e.g., Slack permalink)
parentTaskId	String?	Parent task (if child of exploded task)
customer	String?	Customer name (legacy)
customerId	String?	Customer UUID reference
pillarId	String?	Pillar UUID reference
status	String	intake, to_do, in_progress, completed, cancelled, exploded, blocked
ready	Boolean?	Whether task is ready to start
startStopCount	Number	Times timer has been started
timeElapsed	Number	Total time in milliseconds
childTaskIds	String[]	Child task IDs (when exploded)
blockedByTaskIds	String[]	Blocking task IDs
canStartStop	Boolean	Whether timer can be used
userId	String?	Assigned user (Cognito sub)
createdAt	String	ISO 8601
updatedAt	String	ISO 8601
localVersion	Number	Client version for sync
serverVersion	Number?	Server version for sync

---

task-time-time-entries

Individual timer sessions.

Key	Type
id (Partition)	String

GSI	Partition Key	Sort Key
taskId-index	taskId	startTime
userId-date-index	userId	startTime

Field	Type	Description
id	String	UUID
taskId	String	Associated task
userId	String	Cognito sub
startTime	String	ISO 8601
endTime	String?	ISO 8601 (null if running)
duration	Number?	Milliseconds
notes	String?	Time entry notes
createdAt	String	ISO 8601
updatedAt	String	ISO 8601

---

task-time-time-bills

Daily billing summaries per customer/user/pillar.

Key	Type
customerId (Partition)	String
dateUserIdTaskId (Sort)	String

GSI	Partition Key	Sort Key
userId-date-index	userId	date

Field	Type	Description
id	String	{customerId}#{dateUserIdTaskId}
customerId	String	Customer UUID
taskId	String	Task UUID
pillarId	String	Pillar UUID
date	String	YYYY-MM-DD
dateUserIdTaskId	String	Composite sort key
hours	Number	Rounded up to nearest 0.25
userId	String	Cognito sub
status	String	pending, approved, rejected, sent_to_billing
invoice	String?	Invoice reference
memo	String?	Bill memo/description
rejectionNote	String?	Reason for rejection
xeroTimeEntryId	String?	Xero time entry ID after sync
createdAt	String	ISO 8601
updatedAt	String	ISO 8601

---

task-time-customers

Customer master records.

Key	Type
id (Partition)	String

Field	Type	Description
id	String	UUID
name	String	Customer name
domains	String[]?	Email domains for matching leads
primaryContactEmail	String?	Main contact email
primaryContactName	String?	Main contact name
xeroProjectId	String?	Linked Xero project ID
rate	Number?	Default hourly rate
createdAt	String	ISO 8601
updatedAt	String	ISO 8601

---

task-time-customer-users

Maps Cognito users to customers.

Key	Type
customerId (Partition)	String
userId (Sort)	String

Field	Type	Description
customerId	String	Customer UUID
userId	String	Cognito sub
email	String	User email
name	String?	Display name
role	String	customer_admin or customer_user
createdAt	String	ISO 8601
updatedAt	String	ISO 8601

---

task-time-pillars

Billable service categories within a customer.

Key	Type
customerId (Partition)	String
id (Sort)	String

Field	Type	Description
id	String	UUID
customerId	String	Parent customer
name	String	Pillar name (e.g., "General Support", "Development")
rate	Number?	Hourly rate override (highest priority in rate hierarchy)
inactive	Boolean?	Whether pillar is deactivated
xeroTaskId	String?	Cached Xero task ID
createdAt	String	ISO 8601
updatedAt	String	ISO 8601

---

task-time-active-timers

Tracks which task each user is currently timing. One record per user.

Key	Type
id (Partition)	String

Field	Type	Description
id	String	User ID (Cognito sub)
taskId	String	Active task
startTime	String	ISO 8601
createdAt	String	ISO 8601
updatedAt	String	ISO 8601

---

task-time-xero-tokens

Singleton table for Xero OAuth tokens.

Key	Type
id (Partition)	String

Field	Type	Description
id	String	Always 'default'
accessToken	String	Xero access token
refreshToken	String	Xero refresh token
tenantId	String	Xero tenant/org ID
expiresAt	String	ISO 8601 expiry
connectedBy	String	Who connected (Cognito sub or email)
connectedAt	String	ISO 8601
updatedAt	String	ISO 8601

---

task-time-customer-focus

Tracks which customer each internal user is currently viewing.

Key	Type
customerId (Partition)	String
userId (Sort)	String

Field	Type	Description
customerId	String	Customer UUID
userId	String	User email
name	String?	User display name
focusedAt	String	ISO 8601

---

task-time-slack-channel-mappings

Maps Slack channel name prefixes to customers and pillars.

Key	Type
id (Partition)	String

Field	Type	Description
id	String	UUID
prefix	String	Channel name prefix (e.g., customer-acme)
customerId	String	Customer UUID
pillarId	String?	Optional pillar UUID
createdAt	String	ISO 8601
updatedAt	String	ISO 8601

Table Access by Lambda

Lambda	Full Access	Read-Only Access
task-sync	tasks	—
time-entry-sync	time-entries, time-bills, pillars	tasks, customers, customer-users, xero-tokens
customer-sync	customers, customer-users, pillars, customer-focus, time-bills, time-entries, tasks	—
status-check	tasks	—
active-timer-sync	active-timers	—
xero-sync	xero-tokens	—
slack-integration	tasks, slack-channel-mappings	customers, pillars