Database Schema

The PREQSTATION database stores tasks, project metadata, work logs, and audit history. The current schema is defined in projects-manager/lib/db/schema.ts.

Core Tables

users

Single-owner account table.

Column	Type	Purpose
id	UUID	Primary key
email	Text	Login email
name	Text	Optional display name
isOwner	Boolean	Single-owner flag
createdAt	Timestamp	Creation time
updatedAt	Timestamp	Last update

Note

PREQSTATION is still modeled as a single-owner system. Multi-user support would require structural changes.

projects

Tracked repositories and deployment targets.

Column	Type	Purpose
id	UUID	Primary key
ownerId	UUID	Owner foreign key
projectKey	Text	Stable project key such as PROJ
name	Text	Project display name
description	Text	Optional description
repoUrl	Text	Repository URL
vercelUrl	Text	Optional deployment URL
status	Text	Project status
priority	Integer	Ordering priority
bgImage	Text	Background image or preset
bgImageCredit	JSONB	Attribution metadata
deletedAt	Timestamp	Soft delete timestamp
createdAt	Timestamp	Creation time
updatedAt	Timestamp	Last update

project_settings

Per-project configuration is stored as key/value rows, not as wide columns.

Column	Type	Purpose
id	UUID	Primary key
projectId	UUID	Project foreign key
key	Text	Setting name
value	Text	Setting value
createdAt	Timestamp	Creation time
updatedAt	Timestamp	Last update

Common keys:

• deploy_strategy
• deploy_default_branch
• deploy_auto_pr
• deploy_commit_on_review
• deploy_squash_merge
• agent_instructions

tasks

Task cards with workflow status plus execution overlay fields.

Column	Type	Purpose
id	UUID	Primary key
ownerId	UUID	Owner foreign key
projectId	UUID	Related project
labelId	UUID	Legacy primary label pointer
taskKey	Text	Human-readable key such as PROJ-123
taskPrefix	Text	Project key portion
taskNumber	Integer	Sequence number within the project
title	Text	Task title
note	Text	Description plus acceptance criteria markdown
status	Text	inbox, todo, hold, ready, done, archived
taskPriority	Text	highest, high, medium, none, low, lowest
branch	Text	Branch name used for work
engine	Text	claude-code, codex, or gemini-cli
runState	Text	Internal execution state: queued, running, or null
runStateUpdatedAt	Timestamp	Last run-state update
dueAt	Timestamp	Optional due date
focusedAt	Timestamp	Today-focus pin timestamp
sortOrder	Varchar	Fractional ordering key for Kanban
archivedAt	Timestamp	Archive time
createdAt	Timestamp	Creation time
updatedAt	Timestamp	Last update

External task APIs serialize runState="running" as run_state="working" so API clients do not need to know the internal label.

task_labels

Owner-scoped label taxonomy.

Column	Type	Purpose
id	UUID	Primary key
ownerId	UUID	Owner foreign key
name	Text	Unique label name per owner
color	Text	Stored color value
createdAt	Timestamp	Creation time
updatedAt	Timestamp	Last update

task_label_assignments

Join table between tasks and labels.

Column	Type	Purpose
id	UUID	Primary key
taskId	UUID	Task foreign key
labelId	UUID	Label foreign key
position	Integer	Display order within the task
createdAt	Timestamp	Creation time

API and Audit Tables

api_tokens

Bearer tokens for agent authentication.

Column	Type	Purpose
id	UUID	Primary key
ownerId	UUID	Owner foreign key
name	Text	Token display name
tokenPrefix	Text	Public prefix (preq_...)
tokenHash	Text	SHA-256 hash
lastUsedAt	Timestamp	Last successful use
expiresAt	Timestamp	Optional expiration
revokedAt	Timestamp	Revocation time
createdAt	Timestamp	Creation time
updatedAt	Timestamp	Last update

Note

The plaintext token is shown only once when created. After that, only the hash and prefix remain in the database.

work_logs

Durable timeline entries for manual work logs and agent results.

Column	Type	Purpose
id	UUID	Primary key
ownerId	UUID	Owner foreign key
projectId	UUID	Optional project foreign key
taskId	UUID	Optional task foreign key
title	Text	Work log title
detail	Text	Work log body
engine	Text	Engine that produced the entry
workedAt	Timestamp	When the work happened
createdAt	Timestamp	Creation time

events_outbox

Append-only event stream for downstream processing.

Column	Type	Purpose
id	BigSerial	Monotonic event cursor
ownerId	UUID	Owner foreign key
projectId	UUID	Related project when available
eventType	Text	Event name such as TASK_STATUS_CHANGED
entityType	Text	task, project, or worklog
entityId	Text	Task key, UUID, or work log id
payload	JSONB	Event-specific metadata
createdAt	Timestamp	Creation time

audit_logs

Immutable record of application mutations.

Column	Type	Purpose
id	UUID	Primary key
ownerId	UUID	Owner foreign key
action	Text	Action name
targetType	Text	Resource type
targetId	Text	Resource identifier
meta	JSONB	Structured metadata
createdAt	Timestamp	Creation time

security_events

Authentication and authorization history.

Column	Type	Purpose
id	UUID	Primary key
ownerId	UUID	Owner foreign key (nullable)
actorEmail	Text	Email tied to the event
eventType	Text	Event name
outcome	Text	success or failure
ipAddress	Text	Source IP
userAgent	Text	User agent
path	Text	Route involved
detail	JSONB	Extra context
createdAt	Timestamp	Creation time

user_settings

Owner-scoped key/value settings.

Column	Type	Purpose
id	UUID	Primary key
ownerId	UUID	Owner foreign key
key	Text	Setting name
value	Text	Setting value
createdAt	Timestamp	Creation time
updatedAt	Timestamp	Last update

Common keys:

• telegram_bot_token
• telegram_chat_id
• telegram_enabled

Notes

• Projects are soft-deleted through deletedAt.
• Tasks keep workflow status and execution state separate.
• Labels are many-to-many through task_label_assignments.
• Event ids are monotonic cursors, which is why /api/events returns nextCursor.