01 · Overview
Replacing fragmented workflows with a controlled case record.
The system centralizes case intake, party participation, mediator and venue assignment, sessions, documents, clauses, settlement outcomes, and audit history.
Its differentiating feature is a reproducible compliance run that checks treaty membership and country-linked rules before recording a green, yellow, or red enforceability indicator for a target jurisdiction.
Project Sources
02 · Architecture
A case-centered model connecting people, evidence, process, and enforceability.
The conceptual design organizes the system into four connected data domains while preserving case-level traceability.
Identity & Access
Users specialize into parties and mediators, while case membership controls access to confidential records.
users · party · mediator · users_in_case · permissionCase Operations
The case is the hub for messages, documents, clauses, summaries, sessions, venues, and outcomes.
case_file · message · document · clause · summaryMediation Workflow
Assignment, venue suggestions, scheduling, notifications, and lifecycle transitions are modeled as controlled events.
case_mediator · venue · venue_suggestion · sessionCompliance & Closure
Country and treaty data drive compliance runs, while outcome and arbitration records close the case with an audit trail.
country · treaty_membership · rule · compliance_run · audit_log03 · Requirements
The database supports the mediation lifecycle from intake through closure.
Create authenticated users and assign party or mediator specialization.
Register two parties, clauses, target jurisdictions, and the initial case state.
Recommend, accept, or reject mediators and venue suggestions.
Schedule meetings, collect attendance decisions, messages, and documents.
Evaluate treaty membership, valid dates, reservations, and linked rules.
Finalize settlement, non-settlement, or arbitration and generate a summary.
Performance
Under 2 seconds
Normal requests target sub-two-second response times, concurrent access, and batch uploads up to 100 MB.
Security
Case-level authorization
Sensitive data is encrypted, passwords are hashed, and access is restricted to authorized case roles.
Scale
5,000 users
A single-server target supports up to 5,000 users and 100,000 notification events per day.
Capability
Cross-border by design
Country lookups, multilingual fields, time zones, storage expansion, and API integrations are included.
04 · SQL Logic
Relational structure reinforced with database-side rules.
Relational Foundation
Case membership is explicit.
The composite key in users_in_case prevents duplicate membership and supports confidential case-scoped queries.
CREATE TABLE IF NOT EXISTS `case_file` (
`case_id` INT NOT NULL AUTO_INCREMENT,
`case_name` VARCHAR(100) NOT NULL,
`start_date` DATETIME NOT NULL,
`end_date` DATETIME NULL,
`case_status` ENUM('ongoing','settled','arbitration')
NOT NULL DEFAULT 'ongoing',
PRIMARY KEY (`case_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `users_in_case` (
`user_id` INT NOT NULL,
`case_id` INT NOT NULL,
PRIMARY KEY (`user_id`,`case_id`),
CONSTRAINT `fk_uic_user`
FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_uic_case`
FOREIGN KEY (`case_id`) REFERENCES `case_file`(`case_id`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Confidentiality
Messages are filtered by membership.
The procedure joins each message to the case-membership table before returning records for a user.
DELIMITER $$
CREATE PROCEDURE sp_secure_messages(IN p_user_id INT)
BEGIN
SELECT m.*
FROM message m
JOIN users_in_case uic
ON uic.case_id = m.case_id
WHERE uic.user_id = p_user_id;
END$$
DELIMITER ;Decision Support
Compliance produces a repeatable indicator.
No treaty target returns red, treaty data without rules returns yellow, and complete linked data returns green.
CREATE FUNCTION fn_case_compliance_outcome(p_case_id INT)
RETURNS VARCHAR(10)
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE v_treaty_count INT DEFAULT 0;
DECLARE v_rule_count INT DEFAULT 0;
DECLARE v_outcome VARCHAR(10);
SELECT COUNT(DISTINCT tm.iso2)
INTO v_treaty_count
FROM treaty_membership tm
WHERE tm.case_id = p_case_id;
IF v_treaty_count = 0 THEN
SET v_outcome = 'red';
ELSE
SELECT COUNT(DISTINCT r.rule_id)
INTO v_rule_count
FROM treaty_membership tm
JOIN rule r ON r.iso2 = tm.iso2
WHERE tm.case_id = p_case_id;
SET v_outcome = IF(v_rule_count = 0, 'yellow', 'green');
END IF;
RETURN v_outcome;
END;Unified History
One view reconstructs the case timeline.
Messages, documents, summaries, sessions, and outcomes are normalized into a sortable event stream.
CREATE OR REPLACE VIEW v_case_timeline AS
SELECT m.case_id, 'message' AS event_type,
m.msg_id AS event_id, m.user_id AS actor_user_id,
m.txt AS description, m.date_time
FROM message m
UNION ALL
SELECT d.case_id, 'document', d.doc_id, d.user_id,
d.file_name, d.date_time
FROM document d
UNION ALL
SELECT s.case_id, 'summary', s.summary_id, s.user_id,
s.txt, s.date_time
FROM summary s
UNION ALL
SELECT se.case_id, 'session', se.session_id, NULL,
CONCAT('Session ', se.session_status), se.scheduled_at
FROM session se
UNION ALL
SELECT co.case_id, 'outcome', co.outcome_id, co.user_id,
co.outcome_type, co.date_time
FROM case_outcome co;05 · Automation & Integrity
Critical workflow rules stay close to the data they protect.
Mediator Assignment
Validates mediator status, links the mediator to the case, adds case membership, creates a notification, and records the assigning user.
Message Notifications
After a message is inserted, every case member except the sender receives a notification and the action is written to the audit log.
Session Scheduling
Only the assigned mediator may schedule a session; all participants are notified and the scheduling event is audited.
Document Ownership
A before-delete guard blocks unauthorized deletion, followed by an after-delete audit record for permitted actions.
Outcome Synchronization
Settlement and arbitration outcomes automatically update case status and end dates, preventing contradictory records.
Settlement Summary
A settled case receives a closing summary when one has not already been created, preserving a complete final record.