Series 5 — Legal SaaS Architecture in PHP • Part 1 of 7

Legal practice management software stores some of the most sensitive data that exists: client communications, matters, financial records, and proceedings. The schema design must reflect this — every table enforces team isolation, audit trails are non-negotiable, and nothing is ever hard-deleted.

Core Entities

-- Every row is scoped to a team
CREATE TABLE matters (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id     INT UNSIGNED NOT NULL,
  ref_number  VARCHAR(50) NOT NULL,
  title       VARCHAR(500) NOT NULL,
  status      ENUM('open','closed','on_hold','archived') NOT NULL DEFAULT 'open',
  opened_on   DATE NOT NULL,
  closed_on   DATE DEFAULT NULL,
  deleted_at  DATETIME DEFAULT NULL,    -- soft delete only
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_team_ref (team_id, ref_number),
  INDEX idx_team_status (team_id, status),
  FOREIGN KEY (team_id) REFERENCES teams(id)
);

CREATE TABLE proceedings (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id     INT UNSIGNED NOT NULL,
  matter_id   BIGINT UNSIGNED NOT NULL,
  forum       VARCHAR(200) NOT NULL,
  case_number VARCHAR(100) DEFAULT NULL,
  stage       VARCHAR(100) DEFAULT NULL,
  deleted_at  DATETIME DEFAULT NULL,
  FOREIGN KEY (matter_id) REFERENCES matters(id)
);

CREATE TABLE parties (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id     INT UNSIGNED NOT NULL,
  matter_id   BIGINT UNSIGNED NOT NULL,
  name        VARCHAR(300) NOT NULL,
  role        ENUM('client','opposing_party','witness','expert','court','other') NOT NULL,
  is_our_client TINYINT(1) NOT NULL DEFAULT 0,
  deleted_at  DATETIME DEFAULT NULL
);

Hearings, Work Logs, and Billing Entries

CREATE TABLE hearings (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id       INT UNSIGNED NOT NULL,
  proceeding_id BIGINT UNSIGNED NOT NULL,
  scheduled_at  DATETIME NOT NULL,
  venue         VARCHAR(300) DEFAULT NULL,
  purpose       VARCHAR(500) DEFAULT NULL,
  outcome       TEXT DEFAULT NULL,
  deleted_at    DATETIME DEFAULT NULL
);

CREATE TABLE work_logs (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id         INT UNSIGNED NOT NULL,
  matter_id       BIGINT UNSIGNED NOT NULL,
  proceeding_id   BIGINT UNSIGNED DEFAULT NULL,
  user_id         INT UNSIGNED NOT NULL,
  description     TEXT NOT NULL,
  started_at      DATETIME NOT NULL,
  ended_at        DATETIME DEFAULT NULL,
  duration_min    SMALLINT UNSIGNED DEFAULT NULL,   -- computed on stop
  status          ENUM('draft','pending_review','approved','rejected','applied') NOT NULL DEFAULT 'draft',
  approved_by     INT UNSIGNED DEFAULT NULL,
  approved_at     DATETIME DEFAULT NULL,
  deleted_at      DATETIME DEFAULT NULL
);

CREATE TABLE billing_entries (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id         INT UNSIGNED NOT NULL,
  work_log_id     BIGINT UNSIGNED DEFAULT NULL,
  matter_id       BIGINT UNSIGNED NOT NULL,
  description     TEXT NOT NULL,
  hours           DECIMAL(8,2) DEFAULT NULL,
  rate_per_hour   DECIMAL(10,2) DEFAULT NULL,
  fixed_amount    DECIMAL(10,2) DEFAULT NULL,
  entry_type      ENUM('time','fixed','expense','retainer') NOT NULL DEFAULT 'time',
  billed_at       DATE DEFAULT NULL,
  invoice_id      BIGINT UNSIGNED DEFAULT NULL
);

Why You Never Hard-Delete in Legal Software

Hard deleting a matter record could destroy evidence in litigation, violate bar association file retention requirements, or breach contractual obligations to preserve client records. Soft deletes — a deleted_at timestamp — are the only acceptable deletion mechanism.

At the query layer, every fetch must include WHERE deleted_at IS NULL unless the query is explicitly for archived records. Enforce this with a base repository class that appends the filter automatically.

What to Watch For

  • Missing team_id on joins — A JOIN that doesn't include AND b.team_id = ? on both sides is a data leak waiting to happen. Use a code review rule: every JOIN on a multi-tenant table requires explicit team scoping.
  • Cascade deletes in the DB — Do not use ON DELETE CASCADE on any table that should soft-delete. It bypasses the soft-delete pattern entirely.
  • Audit trail for schema changes — When you change the schema (add a column, change an enum), document it in the migrations table with a timestamp and a reason.