Complete database schema design with migrations and relationships.
------
┌─────────────────┐
│ Organization │
│ (Tenant) │
└────────┬────────┘
│
┌────┴────┬──────────────┬───────────────┬────────────────┐
│ │ │ │ │
▼ ▼ ▼ ▼ ▼
┌───────┐ ┌───────────┐ ┌─────────┐ ┌────────────┐ ┌────────────┐
│ Users │ │Integrations│ │ Clients │ │ Projects │ │ TimeBlocks │
│(pivot)│ │ │ │ │ │ │ │ │
└───┬───┘ └─────┬─────┘ └────┬────┘ └─────┬──────┘ └────────────┘
│ │ │ │ │
│ ▼ │ │ │
│ ┌───────────┐ └────────────┴───────────────┘
│ │ Calendars │ (foreign keys)
│ └───────────┘
│
└──► organization_user (pivot with role)---
sql
CREATE TABLE organizations (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
slug VARCHAR(255) NOT NULL UNIQUE, -- for subdomain routing (future)
owner_id BIGINT UNSIGNED NULL, -- references users.id (set after user created)
settings JSON NULL, -- org-wide settings
timezone VARCHAR(64) DEFAULT 'UTC',
billing_email VARCHAR(255) NULL,
subscription_status ENUM('trial', 'active', 'canceled', 'past_due') DEFAULT 'trial',
trial_ends_at TIMESTAMP NULL,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
deleted_at TIMESTAMP NULL, -- soft deletes
INDEX idx_slug (slug),
INDEX idx_owner (owner_id)
);
Users
sql
CREATE TABLE users (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
email_verified_at TIMESTAMP NULL,
password VARCHAR(255) NOT NULL,
-- User Preferences
timezone VARCHAR(64) DEFAULT 'UTC', -- user's personal timezone (e.g., 'Europe/Amsterdam')
work_days JSON DEFAULT '["mon","tue","wed","thu","fri"]', -- which days to show in calendar
work_hours_start TIME DEFAULT '08:00:00', -- calendar view start time
work_hours_end TIME DEFAULT '18:00:00', -- calendar view end time
preferences JSON NULL, -- additional UI preferences
current_organization_id BIGINT UNSIGNED NULL, -- last active org (for switching)
remember_token VARCHAR(100) NULL,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
FOREIGN KEY (current_organization_id) REFERENCES organizations(id) ON DELETE SET NULL,
INDEX idx_email (email)
);
-- Add foreign key for owner after users table exists
ALTER TABLE organizations
ADD FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL;
Organization-User Pivot (Many-to-Many with Role)
sql
CREATE TABLE organization_user (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
organization_id BIGINT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
role ENUM('owner', 'admin', 'member', 'viewer') NOT NULL DEFAULT 'member',
invited_at TIMESTAMP NULL,
joined_at TIMESTAMP NULL,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
UNIQUE INDEX idx_org_user (organization_id, user_id),
INDEX idx_user_orgs (user_id)
);
Invitations
sql
CREATE TABLE invitations (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
organization_id BIGINT UNSIGNED NOT NULL,
email VARCHAR(255) NOT NULL,
role ENUM('admin', 'member', 'viewer') NOT NULL DEFAULT 'member',
token VARCHAR(64) NOT NULL UNIQUE,
invited_by BIGINT UNSIGNED NOT NULL,
expires_at TIMESTAMP NOT NULL,
accepted_at TIMESTAMP NULL,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
FOREIGN KEY (invited_by) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_token (token),
INDEX idx_org_email (organization_id, email)
);
Integrations (Organization-level OAuth connections)
sql
CREATE TABLE integrations (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
organization_id BIGINT UNSIGNED NOT NULL,
connected_by BIGINT UNSIGNED NOT NULL, -- user who connected
provider VARCHAR(50) NOT NULL, -- 'google_calendar', 'office365', 'moneybird', 'harvest'
provider_type ENUM('calendar', 'invoicing') NOT NULL,
name VARCHAR(255) NULL, -- friendly name "John's Google Calendar"
access_token TEXT NOT NULL, -- Encrypted
refresh_token TEXT NULL, -- Encrypted
token_metadata TEXT NULL, -- Encrypted JSON (admin_id, scopes, etc.)
expires_at TIMESTAMP NULL,
provider_account_id VARCHAR(255) NULL, -- external account identifier
provider_account_email VARCHAR(255) NULL,
is_active BOOLEAN DEFAULT TRUE,
last_sync_at TIMESTAMP NULL,
last_error TEXT NULL,
error_count INT DEFAULT 0,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
FOREIGN KEY (connected_by) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_org_provider (organization_id, provider),
INDEX idx_provider_type (organization_id, provider_type),
INDEX idx_expires (expires_at)
);
Calendars (Multiple calendars per integration)
sql
CREATE TABLE calendars (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
organization_id BIGINT UNSIGNED NOT NULL,
integration_id BIGINT UNSIGNED NOT NULL,
external_id VARCHAR(255) NOT NULL, -- Google/Office365 calendar ID
name VARCHAR(255) NOT NULL,
color VARCHAR(20) NULL,
is_primary BOOLEAN DEFAULT FALSE,
is_enabled BOOLEAN DEFAULT TRUE, -- user can disable specific calendars
sync_direction ENUM('read', 'write', 'both') DEFAULT 'both',
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
FOREIGN KEY (integration_id) REFERENCES integrations(id) ON DELETE CASCADE,
UNIQUE INDEX idx_integration_external (integration_id, external_id),
INDEX idx_org_enabled (organization_id, is_enabled)
);
Clients (synced from invoicing + local)
sql
CREATE TABLE clients (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
organization_id BIGINT UNSIGNED NOT NULL,
integration_id BIGINT UNSIGNED NULL, -- NULL if created locally
external_id VARCHAR(255) NULL, -- Moneybird/Harvest contact ID
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NULL,
metadata JSON NULL,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
FOREIGN KEY (integration_id) REFERENCES integrations(id) ON DELETE SET NULL,
INDEX idx_org_external (organization_id, external_id),
INDEX idx_org_active (organization_id, is_active)
);
Projects
sql
CREATE TABLE projects (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
organization_id BIGINT UNSIGNED NOT NULL,
client_id BIGINT UNSIGNED NULL,
integration_id BIGINT UNSIGNED NULL,
external_id VARCHAR(255) NULL, -- Moneybird/Harvest project ID
name VARCHAR(255) NOT NULL,
color VARCHAR(20) NULL,
hourly_rate DECIMAL(10,2) NULL,
metadata JSON NULL,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
FOREIGN KEY (integration_id) REFERENCES integrations(id) ON DELETE SET NULL,
INDEX idx_org_external (organization_id, external_id),
INDEX idx_org_client (organization_id, client_id)
);
Time Blocks (Core entity)
sql
CREATE TABLE time_blocks (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
organization_id BIGINT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED NOT NULL, -- who created/owns this block
calendar_id BIGINT UNSIGNED NULL, -- which calendar it syncs to
-- External sync IDs
external_calendar_event_id VARCHAR(255) NULL, -- Google/Office365 event ID
external_invoicing_entry_id VARCHAR(255) NULL, -- Moneybird/Harvest time entry ID
-- Core data
title VARCHAR(255) NOT NULL,
description TEXT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
timezone VARCHAR(64) NOT NULL, -- stored in user's timezone
color VARCHAR(20) NULL,
-- Billing
client_id BIGINT UNSIGNED NULL,
project_id BIGINT UNSIGNED NULL,
is_billable BOOLEAN DEFAULT TRUE,
-- Sync status
calendar_sync_status ENUM('pending', 'synced', 'error', 'disabled') DEFAULT 'pending',
invoicing_sync_status ENUM('pending', 'synced', 'error', 'disabled') DEFAULT 'disabled',
last_synced_at TIMESTAMP NULL,
sync_error TEXT NULL,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
deleted_at TIMESTAMP NULL, -- soft deletes for sync safety
FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (calendar_id) REFERENCES calendars(id) ON DELETE SET NULL,
FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
INDEX idx_org_user_time (organization_id, user_id, start_time, end_time),
INDEX idx_external_calendar (external_calendar_event_id),
INDEX idx_external_invoicing (external_invoicing_entry_id),
INDEX idx_sync_status (calendar_sync_status, invoicing_sync_status)
);
Audit Logs
sql
CREATE TABLE audit_logs (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
organization_id BIGINT UNSIGNED NULL, -- NULL for system events
user_id BIGINT UNSIGNED NULL,
action VARCHAR(100) NOT NULL,
auditable_type VARCHAR(255) NULL,
auditable_id BIGINT UNSIGNED NULL,
old_values JSON NULL,
new_values JSON NULL,
ip_address VARCHAR(45) NULL,
user_agent VARCHAR(500) NULL,
created_at TIMESTAMP NULL,
INDEX idx_org_action (organization_id, action),
INDEX idx_auditable (auditable_type, auditable_id),
INDEX idx_created (created_at)
);
---
Migration Order
Migrations should be created in this order to satisfy foreign key constraints:
database/migrations/
├── 0001_01_01_000000_create_users_table.php
├── 2024_01_01_000001_create_organizations_table.php
├── 2024_01_01_000002_create_organization_user_table.php
├── 2024_01_01_000003_create_invitations_table.php
├── 2024_01_01_000004_create_integrations_table.php
├── 2024_01_01_000005_create_calendars_table.php
├── 2024_01_01_000006_create_clients_table.php
├── 2024_01_01_000007_create_projects_table.php
├── 2024_01_01_000008_create_time_blocks_table.php
└── 2024_01_01_000009_create_audit_logs_table.php
`
Notes on Foreign Keys
1. **Users before Organizations**: Users table created first, then organizations, then add