Database Schema Changes for Laravel Implementation

This document describes every change made to the original DBML schema when implementing the system with Laravel + Filament. The original schema was designed around Logto (external identity provider) and a Node.js/JWT-bearer API architecture. Switching to Laravel requires removing Logto coupling, simplifying the auth layer, renaming reserved words, and adding Laravel/package-specific tables.

The original DBML schema remains the authoritative domain reference. This document only records deltas — what is removed, renamed, modified, or added.

Quick Reference Summary

Change Type	Count	Examples
Tables removed	4	`LOGTO_ORGANIZATIONS`, `LOGTO_SYNC_LOG`
Tables renamed	13	`CASES` → `matters`, `CASE_BUDGETS` → `matter_budgets`
Tables modified	7	`AUTH_USERS`, `ROLES`, `USER_ROLE_ASSIGNMENTS`
Tables added (Laravel)	13	`personal_access_tokens`, `jobs`, `activity_log`, `media`

1. Tables Removed Entirely

These four tables exist solely to support Logto synchronization. Without Logto, none of them have a purpose.

`LOGTO_ORGANIZATIONS`

Why removed: This was a mirror cache of Logto's organization objects, mapped 1:1 to LAW_FIRMS. With Laravel auth, LAW_FIRMS itself is the tenant boundary — no external org concept exists to mirror.

`LOGTO_ORG_MEMBERSHIPS`

Why removed: This tracked which Logto users belonged to which Logto organizations. In Laravel, firm membership is tracked directly via FIRM_USER_PROFILES.law_firm_id. No intermediate sync table is needed.

`LOGTO_SYNC_LOG`

Why removed: This was an audit trail for Logto webhook and scheduled sync operations. With no Logto sync process, there is nothing to log. General audit logging is handled by spatie/laravel-activitylog instead.

`LOGTO_ORG_ROLE_MAPPINGS`

Why removed: This was a bridge table that mapped Logto's coarse org roles (admin, member, guest) to the application's fine-grained roles. With Laravel auth, roles are assigned directly via USER_ROLE_ASSIGNMENTS with no Logto intermediary.

2. Tables Renamed

`CASES` → `matters` (cascade rename)

CASE is a reserved keyword in PHP — a class cannot be named Case. The domain term "matter" is also the correct legal term used by law firms. Every table prefixed with CASE_ is renamed to matter_ accordingly.

Original Table	Laravel Table	Notes
`CASES`	`matters`	core rename
`CASE_SPECIALTY`	`matter_specialties`	pivot table
`CASE_BUDGETS`	`matter_budgets`
`CASE_ATTORNEY_ROLE_TYPES`	`matter_attorney_role_types`	lookup table
`CASE_PARTY_ROLE_TYPES`	`matter_party_role_types`	lookup table
`CASE_FIRM_ROLE_TYPES`	`matter_firm_role_types`	lookup table
`CASE_ATTORNEYS`	`matter_attorneys`	pivot with extra columns
`CASE_PARTNER_ATTORNEYS`	`matter_partner_attorneys`	pivot with extra columns
`CASE_CLIENTS`	`matter_clients`	pivot with extra columns
`CASE_PARTNER_FIRMS`	`matter_partner_firms`	pivot with extra columns
`CASE_BILLING_SETTINGS`	`matter_billing_settings`
`CASE_RATE_PLAN_SELECTIONS`	`matter_rate_plan_selections`
`CASE_PARTNER_RATE_SELECTIONS`	`matter_partner_rate_selections`

Cascade column rename: Every case_id foreign key column in all other tables is renamed to matter_id.

Tables that contain case_id and must be updated:

user_role_assignments.case_id         → matter_id
lawyer_time_entries.case_id           → matter_id
partner_time_entries.case_id          → matter_id
expense_entries.case_id               → matter_id
invoices.case_id                      → matter_id
documents.case_id                     → matter_id
appointments.case_id                  → matter_id

3. Tables Modified

3.1 `AUTH_USERS` → renamed to `users`

This is the most significant change. The original table was a read-only mirror of Logto's user store — it intentionally had no password or remember_token because Logto owned authentication. Laravel owns authentication entirely, so the table becomes a standard Laravel users table.

Columns removed:

Column	Reason
`logto_user_id`	Logto's external user identifier — no longer needed
`logto_synced_at`	Timestamp of last Logto sync — no sync process
`logto_custom_data`	JSON cache of Logto's `custom_data` field

Columns added:

Column	Type	Reason
`password`	`varchar`	Laravel hashes and owns credentials
`remember_token`	`varchar(100)`	Laravel "remember me" cookie support
`email_verified_at`	`datetime nullable`	Laravel email verification (replaces the boolean `email_verified`)

Columns kept (unchanged):

email, name, phone, phone_verified, avatar_url, is_active, last_app_login_at, app_preferences, created_at, updated_at

Indexes removed:

(logto_user_id) [unique]    -- no longer exists

Before vs. After:

-- ORIGINAL (Logto-coupled)
Table AUTH_USERS {
  id bigint [pk]
  logto_user_id varchar [unique]     ← REMOVE
  email varchar
  name varchar
  phone varchar
  email_verified boolean             ← REPLACE with email_verified_at
  phone_verified boolean
  avatar_url varchar
  logto_synced_at datetime           ← REMOVE
  logto_custom_data json             ← REMOVE
  is_active boolean
  last_app_login_at datetime
  app_preferences json
  created_at datetime
  updated_at datetime
}

-- LARAVEL (standalone auth)
Table users {
  id bigint [pk]
  email varchar [unique]
  name varchar
  phone varchar
  email_verified_at datetime         ← ADDED (Laravel convention)
  phone_verified boolean
  avatar_url varchar
  password varchar                   ← ADDED
  remember_token varchar             ← ADDED
  is_active boolean
  last_app_login_at datetime
  app_preferences json
  created_at datetime
  updated_at datetime
}

3.2 `LAW_FIRMS`

Columns removed:

Column	Reason
`logto_org_id`	Logto's organization ID — no external org to reference
`logto_synced_at`	Last sync from Logto — no sync process

Indexes removed:

(logto_org_id) [unique]

Everything else remains identical. LAW_FIRMS continues to be the tenant boundary for Filament's tenancy system.

3.3 `FIRM_USER_PROFILES`

Columns removed:

Column	Reason
`logto_user_id`	Redundant cross-reference to Logto — only `user_id` FK is needed

Columns renamed:

Original	Laravel	Reason
`auth_user_id`	`user_id`	`auth_users` table is now `users`

Indexes removed:

(logto_user_id, law_firm_id) [unique]   -- column no longer exists

Indexes updated:

-- BEFORE
(auth_user_id, law_firm_id) [unique]

-- AFTER
(user_id, law_firm_id) [unique]

3.4 `ROLES`

The Logto integration flags on roles allowed roles to be auto-assigned based on a user's Logto org role. Without Logto, all role assignments are explicit.

Columns removed:

Column	Reason
`is_logto_synced`	Flag indicating this role was auto-assigned from Logto
`logto_org_role_mapping`	The Logto org role (`admin`/`member`/`guest`) this maps to

Indexes removed:

(is_logto_synced)

3.5 `USER_ROLE_ASSIGNMENTS`

Columns removed:

Column	Reason
`logto_user_id`	Redundant cross-reference alongside `user_id` FK
`logto_org_id`	Scoping by Logto organization — firm scope uses `law_firm_id` instead

Columns renamed:

Original	Laravel
`auth_user_id`	`user_id`

assignment_source enum values updated:

-- BEFORE
MANUAL | LOGTO_ORG_ROLE | AUTO | CASE_MEMBER | SYSTEM

-- AFTER
MANUAL | AUTO | MATTER_MEMBER | SYSTEM

LOGTO_ORG_ROLE is removed. CASE_MEMBER is renamed to MATTER_MEMBER for consistency with the CASES → matters rename.

Indexes removed:

(logto_user_id, role_id)
(logto_org_id, auth_user_id)

Indexes updated:

-- BEFORE
(auth_user_id, role_id, law_firm_id, org_unit_id, case_id) [unique]
(auth_user_id, ends_at)
(law_firm_id, auth_user_id)

-- AFTER
(user_id, role_id, law_firm_id, org_unit_id, matter_id) [unique]
(user_id, ends_at)
(law_firm_id, user_id)

3.6 `RESOURCE_ACCESS_GRANTS`

Columns removed:

Column	Reason
`logto_user_id`	Redundant alongside `user_id` FK
`logto_org_id`	Scoping by Logto org — no longer applicable

Columns renamed:

Original	Laravel
`auth_user_id`	`user_id`

grant_source enum values updated:

-- BEFORE
MANUAL | ROLE | CASE_MEMBER | PARTNER_MEMBER | SYSTEM | LOGTO_ORG_ADMIN

-- AFTER
MANUAL | ROLE | MATTER_MEMBER | PARTNER_MEMBER | SYSTEM

LOGTO_ORG_ADMIN is removed. CASE_MEMBER renamed to MATTER_MEMBER.

Indexes removed:

(logto_user_id)
(logto_org_id, resource_type)

Indexes updated:

-- BEFORE
(resource_type, resource_id, subresource_type, subresource_id, auth_user_id, access_level) [pk]
(auth_user_id)
(auth_user_id, access_level)

-- AFTER
(resource_type, resource_id, subresource_type, subresource_id, user_id, access_level) [pk]
(user_id)
(user_id, access_level)

3.7 `CLIENT_USERS` and `PARTNER_ATTORNEY_USERS`

These tables link portal users to their domain records (CLIENT_CONTACTS and PARTNER_ATTORNEYS respectively). In the original schema, portal users authenticated through Logto and the user_id FK pointed to AUTH_USERS.id.

No structural column changes are needed to these tables — user_id already exists and continues to point to the auth user record. The only change is the referenced table: AUTH_USERS → users.

Both portal user types authenticate through separate Laravel guards backed by the same users table. The guard determines which portal a user can access — a client portal user cannot authenticate into the admin panel.

4. New Tables Added

4.1 Laravel Core Tables

These are created automatically by Laravel's built-in migrations.

Table	Package/Command	Purpose
`password_reset_tokens`	Laravel core	Self-service password reset flow
`sessions`	`php artisan session:table`	Database-backed session storage
`cache`	`php artisan cache:table`	Database cache driver (fallback to Redis)
`cache_locks`	`php artisan cache:table`	Atomic cache lock support
`jobs`	`php artisan queue:table`	Pending queue jobs
`failed_jobs`	`php artisan queue:failed-table`	Failed jobs for inspection and retry
`job_batches`	`php artisan queue:batches-table`	Batch job coordination

4.2 Laravel Sanctum

Table	Purpose
`personal_access_tokens`	API token authentication for mobile apps and external integrations. Each token is scoped to a user and can carry abilities (e.g., `time-entries:write`).

4.3 spatie/laravel-activitylog

Table	Purpose
`activity_log`	Immutable audit trail of every change to financial and domain records. Stores `subject_type`, `subject_id`, `causer_type`, `causer_id`, `event`, `properties` (before/after JSON). Required for ABA compliance.

4.4 spatie/laravel-media-library

Table	Purpose
`media`	Tracks every uploaded file — S3 path, MIME type, size, custom properties, version number. Replaces the `DOCUMENTS` table's direct S3 path storage with a managed media record linked to any model via polymorphic relation.

4.5 Laravel Telescope (development only)

Table	Purpose
`telescope_entries`	Recorded requests, queries, jobs, exceptions, and log entries for development inspection.
`telescope_entries_tags`	Tag index for filtering telescope entries.
`telescope_monitoring`	Monitored tags configuration.

Telescope tables exist in development and staging only. They are excluded from production migrations via the TelescopeServiceProvider environment check.

5. Soft Deletes Added

Laravel's soft delete pattern adds a deleted_at datetime nullable column. Records with a non-null deleted_at are excluded from all standard Eloquent queries without requiring a hard delete.

The following tables gain deleted_at:

Table	Reason
`users`	Deactivating a user must not lose their historical associations
`firm_user_profiles`	Staff departure should be recoverable
`clients`	Client archiving must preserve matter history
`matters`	Closed matters must remain auditable
`partner_firms`	Ending a partnership must not delete historical billing data
`partner_attorneys`	Same reason as partner firms
`documents`	The spec already describes document deletion as a soft operation

Financial tables (invoices, payments, lawyer_time_entries, expense_entries, payment_events) do not use soft deletes. They use the append-only pattern enforced by a model observer — neither soft nor hard deletion is permitted on these tables after creation.

6. Foreign Key Reference Map

Summary of all foreign key targets that change as a result of the above modifications.

Table	Column	Was → Now
`firm_user_profiles`	`user_id`	`auth_users.id` → `users.id`
`user_role_assignments`	`user_id`	`auth_users.id` → `users.id`
`user_role_assignments`	`matter_id`	`cases.id` → `matters.id`
`resource_access_grants`	`user_id`	`auth_users.id` → `users.id`
`client_users`	`user_id`	`auth_users.id` → `users.id`
`partner_attorney_users`	`user_id`	`auth_users.id` → `users.id`
`matter_specialties`	`matter_id`	`cases.id` → `matters.id`
`matter_budgets`	`matter_id`	`cases.id` → `matters.id`
`matter_attorneys`	`matter_id`	`cases.id` → `matters.id`
`matter_clients`	`matter_id`	`cases.id` → `matters.id`
`matter_partner_attorneys`	`matter_id`	`cases.id` → `matters.id`
`matter_partner_firms`	`matter_id`	`cases.id` → `matters.id`
`matter_billing_settings`	`matter_id`	`cases.id` → `matters.id`
`matter_rate_plan_selections`	`matter_id`	`cases.id` → `matters.id`
`matter_partner_rate_selections`	`matter_id`	`cases.id` → `matters.id`
`lawyer_time_entries`	`matter_id`	`cases.id` → `matters.id`
`partner_time_entries`	`matter_id`	`cases.id` → `matters.id`
`expense_entries`	`matter_id`	`cases.id` → `matters.id`
`invoices`	`matter_id`	`cases.id` → `matters.id`
`documents`	`matter_id`	`cases.id` → `matters.id`
`appointments`	`matter_id`	`cases.id` → `matters.id`
`matters`	`created_by`	`auth_users.id` → `users.id`
`matters`	`updated_by`	`auth_users.id` → `users.id`

7. Migration Order

Laravel migrations must run in dependency order. The groups below represent safe batches — all migrations within a group can be run together.

Batch 1 — Foundation
  create_users_table                    (replaces auth_users)
  create_law_firms_table

Batch 2 — Org Structure
  create_locales_table
  create_org_units_table
  create_positions_table
  create_specialties_table

Batch 3 — Staff Profiles
  create_firm_user_profiles_table       (depends on users, law_firms)
  create_lawyer_licenses_table
  create_user_org_units_table
  create_user_positions_table
  create_reporting_lines_table
  create_firm_user_specialties_table

Batch 4 — Clients & Matters
  create_clients_table
  create_client_contacts_table
  create_matters_table                  (renamed from cases)
  create_matter_specialties_table
  create_matter_budgets_table
  create_client_users_table

Batch 5 — Partner Network
  create_partner_firms_table
  create_partner_firm_contacts_table
  create_partner_attorneys_table
  create_firm_partner_relationships_table
  create_firm_partner_agreements_table
  create_firm_partner_rate_cards_table
  create_firm_partner_jurisdictions_table
  create_partner_attorney_users_table

Batch 6 — Matter Roles & Assignments
  create_matter_attorney_role_types_table
  create_matter_party_role_types_table
  create_matter_firm_role_types_table
  create_matter_attorneys_table
  create_matter_partner_attorneys_table
  create_matter_clients_table
  create_matter_partner_firms_table

Batch 7 — RBAC
  create_permissions_table
  create_roles_table
  create_role_permissions_table
  create_user_role_assignments_table
  create_resource_types_table
  create_resource_subtypes_table
  create_resource_access_grants_table

Batch 8 — Billing Catalogs
  create_billing_activity_codes_table
  create_expense_categories_table
  create_firm_tax_rates_table

Batch 9 — Rate Plans
  create_lawyer_rate_plans_table
  create_lawyer_rate_plan_rates_table
  create_client_rate_plan_selections_table
  create_matter_rate_plan_selections_table
  create_partner_rate_plans_table
  create_partner_rate_plan_rates_table
  create_matter_partner_rate_selections_table

Batch 10 — Time, Expenses & Invoicing
  create_matter_billing_settings_table
  create_lawyer_time_entries_table
  create_partner_time_entries_table
  create_expense_entries_table
  create_invoices_table
  create_invoice_line_items_table
  create_credit_notes_table
  create_credit_note_lines_table
  create_credit_note_allocations_table
  create_payment_methods_table
  create_payments_table
  create_payment_allocations_table
  create_payment_events_table

Batch 11 — Partner Billing & ERP
  create_partner_bills_table
  create_partner_bill_lines_table
  create_erp_export_queue_table
  create_erp_account_mappings_table
  create_erp_posting_rules_table
  create_vendors_table
  create_firm_opex_table

Batch 12 — Appointments & Content
  create_appointments_table
  create_appointment_attendees_table
  create_appointment_notes_table
  create_appointment_consents_table
  create_appointment_events_table
  create_articles_table
  create_article_translations_table

Batch 13 — HR & Performance
  create_performance_cycles_table
  create_performance_metrics_table
  create_performance_reviews_table
  create_performance_review_metrics_table
  create_bonus_plans_table
  create_bonus_awards_table
  create_bonus_award_revisions_table
  create_category_dimensions_table
  create_position_categories_table

Batch 14 — Misc & Admin
  create_documents_table
  create_comments_table
  create_notifications_table
  create_support_access_sessions_table
  create_admin_resource_locks_table

Batch 15 — Laravel Package Tables
  create_personal_access_tokens_table   (Sanctum)
  create_activity_log_table             (spatie/activitylog)
  create_media_table                    (spatie/media-library)
  create_jobs_table
  create_failed_jobs_table
  create_job_batches_table
  create_sessions_table
  create_password_reset_tokens_table

Original Database Schema — authoritative source of truth for domain design
Eloquent Models — how each table maps to an Eloquent model class
Authentication — Laravel guards for the three user types
Components — why each package (Sanctum, activitylog, media-library) is included

Quick Reference Summary​

1. Tables Removed Entirely​

LOGTO_ORGANIZATIONS​

LOGTO_ORG_MEMBERSHIPS​

LOGTO_SYNC_LOG​

LOGTO_ORG_ROLE_MAPPINGS​

2. Tables Renamed​

CASES → matters (cascade rename)​

3. Tables Modified​

3.1 AUTH_USERS → renamed to users​

3.2 LAW_FIRMS​

3.3 FIRM_USER_PROFILES​

3.4 ROLES​

3.5 USER_ROLE_ASSIGNMENTS​

3.6 RESOURCE_ACCESS_GRANTS​

3.7 CLIENT_USERS and PARTNER_ATTORNEY_USERS​

4. New Tables Added​

4.1 Laravel Core Tables​

4.2 Laravel Sanctum​

4.3 spatie/laravel-activitylog​

4.4 spatie/laravel-media-library​

4.5 Laravel Telescope (development only)​

5. Soft Deletes Added​

6. Foreign Key Reference Map​

7. Migration Order​

Related Documents​

Quick Reference Summary

1. Tables Removed Entirely

`LOGTO_ORGANIZATIONS`

`LOGTO_ORG_MEMBERSHIPS`

`LOGTO_SYNC_LOG`

`LOGTO_ORG_ROLE_MAPPINGS`

2. Tables Renamed

`CASES` → `matters` (cascade rename)

3. Tables Modified

3.1 `AUTH_USERS` → renamed to `users`

3.2 `LAW_FIRMS`

3.3 `FIRM_USER_PROFILES`

3.4 `ROLES`

3.5 `USER_ROLE_ASSIGNMENTS`

3.6 `RESOURCE_ACCESS_GRANTS`

3.7 `CLIENT_USERS` and `PARTNER_ATTORNEY_USERS`

4. New Tables Added

4.1 Laravel Core Tables

4.2 Laravel Sanctum

4.3 spatie/laravel-activitylog

4.4 spatie/laravel-media-library

4.5 Laravel Telescope (development only)

5. Soft Deletes Added

6. Foreign Key Reference Map

7. Migration Order

Related Documents