Per-Matter / Per-Case / Per-Lawyer Billing Rate Hierarchy

The Five-Level Rate Schema

CREATE TABLE billing_rate_configs (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id         INT UNSIGNED NOT NULL,
  -- Scope: all fields nullable; specificity increases with more non-null fields
  matter_id       BIGINT UNSIGNED DEFAULT NULL,
  proceeding_id   BIGINT UNSIGNED DEFAULT NULL,
  user_id         INT UNSIGNED DEFAULT NULL,  -- lawyer
  -- Rate definitions
  rate_per_hour   DECIMAL(10,2) DEFAULT NULL,
  fixed_per_session DECIMAL(10,2) DEFAULT NULL,
  fixed_per_hearing DECIMAL(10,2) DEFAULT NULL,
  -- Temporal: rates can change on a date
  effective_from  DATE NOT NULL DEFAULT (CURRENT_DATE),
  effective_to    DATE DEFAULT NULL,
  FOREIGN KEY (team_id)       REFERENCES teams(id),
  FOREIGN KEY (matter_id)     REFERENCES matters(id),
  FOREIGN KEY (proceeding_id) REFERENCES proceedings(id),
  FOREIGN KEY (user_id)       REFERENCES users(id)
);

The Resolution Algorithm

class BillingRateResolver
{
    /** @return array{rate_per_hour: float|null, fixed_per_session: float|null} */
    public function resolve(
        int      $teamId,
        int      $userId,
        int      $matterId,
        ?int     $proceedingId,
        \DateTimeInterface $on,
        PDO      $pdo
    ): array {
        $dateStr = $on->format('Y-m-d');

        // Ordered from most specific to least specific
        $candidates = array_filter([
            $proceedingId ? ['proceeding_id' => $proceedingId, 'user_id' => $userId]    : null,
            $proceedingId ? ['proceeding_id' => $proceedingId, 'user_id' => null]        : null,
            ['matter_id' => $matterId, 'user_id' => $userId],
            ['matter_id' => $matterId, 'user_id' => null],
            ['matter_id' => null,      'user_id' => $userId],  // user default
        ]);

        foreach ($candidates as $scope) {
            $where   = ['team_id = ?', 'effective_from <= ?', '(effective_to IS NULL OR effective_to >= ?)'];
            $params  = [$teamId, $dateStr, $dateStr];

            foreach (['matter_id', 'proceeding_id', 'user_id'] as $col) {
                if (isset($scope[$col])) {
                    $where[]  = "{$col} = ?";
                    $params[] = $scope[$col];
                } else {
                    $where[] = "{$col} IS NULL";
                }
            }

            $sql  = 'SELECT * FROM billing_rate_configs WHERE ' . implode(' AND ', $where)
                  . ' ORDER BY effective_from DESC LIMIT 1';
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $row  = $stmt->fetch();

            if ($row && ($row['rate_per_hour'] !== null || $row['fixed_per_session'] !== null)) {
                return $row;
            }
        }

        return ['rate_per_hour' => null, 'fixed_per_session' => null];
    }
}

What to Watch For

• Effective date gaps — If a rate config expires (effective_to in the past) and no successor exists, the resolver returns null. Build an alert for expiring configs 30 days in advance.
• Retroactive rate changes — A new config with an effective_from in the past will change the resolved rate for all historical billing entries that have not yet been invoiced. Warn admins before saving such a change.
• Currency assumptions — If you expand to multiple jurisdictions, store currency with the rate. Do not assume a single currency at the schema level.