Database

Database Environments

The schema is identical across environments. Which database the app uses is controlled by APPWRITE_DATABASE_ID:

• Production: main_database (set in .env.prod)
• Development / Test: main_dev (set in .env.local and .env.test)

Schema changes (new tables, columns, indexes) must be applied to both databases via the bootstrap script (scripts/saas-phase1-bootstrap.ts).

Schema

Phase 1 seeds the full target schema so Phase 2 and Phase 3 can be activated without migrations. Phase 2 adds board position persistence on okr_items (positionX, positionY) and activates board/gantt runtime APIs. Phase 3 activates scrape_jobs, reports, and member_skills for AI onboarding, report artifacts, and skill mapping, and extends organizations with profile enrichment metadata. Optimistic canvas interactions are UI-only and do not require schema changes. Board and Gantt read from normalized okr_items and okr_links tables with server-side privacy filtering.

organizations

Columns:

• id: UUID
• name: String
• slug: String (unique)
• teamId: UUID (Appwrite team id, unique)
• createdByUserId: UUID
• defaultPrivacyMode: ENUM (option_1, option_2)
• industry: String (nullable)
• website: String (nullable)
• customerNames: String[] (nullable)
• revenueAmount: Number (nullable)
• revenueCurrency: String (nullable)
• revenuePeriod: ENUM (year, quarter, month) (nullable)
• employeeCountMin: Number (nullable)
• employeeCountMax: Number (nullable)
• addressStreet: String (nullable)
• addressPostalCode: String (nullable)
• addressCity: String (nullable)
• addressCountry: String (nullable)
• aiEnrichmentOptIn: Boolean
• lastEnrichedAt: Datetime (nullable)
• lastEnrichmentSource: ENUM (manual, ai) (nullable)
• archivedAt: Datetime (nullable)

org_invites

Columns:

• id: UUID
• organizationId: UUID
• email: String
• normalizedEmail: String
• inviteTokenHash: String (unique)
• status: ENUM (pending, accepted, expired, revoked)
• workspaceRole: ENUM (workspace_admin, workspace_user)
• workspaceId: UUID (nullable)
• invitedByUserId: UUID
• expiresAt: Datetime
• acceptedByUserId: UUID (nullable)
• acceptedAt: Datetime (nullable)

workspaces

Columns:

• id: UUID
• organizationId: UUID
• name: String
• slug: String (unique per organization)
• privacyMode: ENUM (option_1, option_2)
• createdByUserId: UUID
• archivedAt: Datetime (nullable)

workspace_memberships

Columns:

• id: UUID
• organizationId: UUID
• workspaceId: UUID
• userId: UUID
• role: ENUM (workspace_admin, workspace_user)
• managerUserId: UUID (nullable)
• active: Boolean
• invitedViaInviteId: UUID (nullable)

okr_items

Columns:

• id: UUID
• organizationId: UUID
• workspaceId: UUID
• type: ENUM (objective, key_result, action)
• title: String
• description: String (nullable)
• ownerUserId: UUID
• assigneeUserIds: String[]
• parentId: UUID (nullable)
• dueDate: Datetime (nullable)
• actionStatus: ENUM (pending, work_in_progress, done) (nullable, actions only)
• storyPoints: String (nullable, actions only; Fibonacci: 0,1,2,3,5,8,13,21,40,?,∞)
• progressComputed: Number 0..100
• sortOrder: Number
• positionX: Number (nullable)
• positionY: Number (nullable)
• deletedAt: Datetime (nullable)
• deletedByUserId: UUID (nullable)

okr_links

Columns:

• id: UUID
• organizationId: UUID
• workspaceId: UUID
• fromItemId: UUID
• toItemId: UUID
• linkType: ENUM (objective_relation)
• deletedAt: Datetime (nullable)

item_activity_log

Columns:

• id: UUID
• organizationId: UUID
• workspaceId: UUID (nullable)
• itemType: String
• itemId: UUID
• actionType: String
• actorUserId: UUID
• beforeJson: JSON String (nullable)
• afterJson: JSON String (nullable)
• requestId: UUID

scrape_jobs

Columns:

• id: UUID
• organizationId: UUID
• requestedByUserId: UUID
• sourceUrl: URL String
• status: ENUM (pending, running, done, failed)
• inputJson: JSON String (nullable)
• prefillJson: JSON String (nullable)
• errorMessage: String (nullable)
• functionExecutionId: UUID (nullable)
• modelUsed: String (nullable)
• provider: String (nullable)
• promptVersion: String (nullable)
• startedAt: Datetime (nullable)
• finishedAt: Datetime (nullable)

reports

Columns:

• id: UUID
• organizationId: UUID
• workspaceId: UUID
• requestedByUserId: UUID
• type: ENUM (okr_summary)
• status: ENUM (pending, running, done, failed)
• inputSnapshotJson: JSON String (nullable)
• outputMarkdown: String (nullable)
• outputJson: JSON String (nullable)
• errorMessage: String (nullable)

member_skills

Columns:

• id: UUID
• organizationId: UUID
• workspaceId: UUID
• userId: UUID
• canJson: JSON String (nullable)
• shouldJson: JSON String (nullable)
• wantJson: JSON String (nullable)
• constraintsJson: JSON String (nullable)
• updatedByUserId: UUID

pat

Columns:

• id: UUID
• userId: UUID
• patHash: String
• scopes: ENUM[] (read, write, admin)
• description: String
• expiresAt: Datetime
• lastUsedAt: Datetime
• revokedAt: Datetime