ACT Master Schedule
A full-stack internal operations platform that centralizes clinical study schedules, assignments, workbook-derived timelines, daily scheduling, workload analytics, ClinSpark report retrieval, Builder Audits, Transfer Data processing, and admin controls. It was built to help small clinical research teams answer operational questions faster without repeatedly cross-checking large workbooks and scattered tools.
The Problem
Clinical research teams were coordinating study milestones, assignment ownership, imported timeline data, report retrieval, and daily work from separate spreadsheets and source systems. That made routine planning slower than it needed to be:
- Staff had to scan large workbooks to answer basic questions about ownership, due dates, and upcoming milestones.
- Timeline changes after workbook syncs were hard to spot and communicate.
- Workload was difficult to compare across CRCs, assignees, and daily schedule tasks.
- ClinSpark report setup and audit review required repeated manual effort.
- Team-level permissions were needed so users could collaborate without broad edit access.
Solution Overview
Study & Milestone Management
- Study creation, editing, archiving, restoring, filtering, and CSV export
- Inline milestone status, assignee, due date, timeline start/end, and notes fields
- Multi-assignee milestones with favorites and persistent user filters
- Copy/paste of milestone tables with cross-team assignee safety checks
Timeline Views
- Dashboard, Table, Calendar, Gantt, Workload, Work, Daily Schedule, and Confinement views
- Macro and Micro Timeline imports with change tracking and event materialization
- Team Assign linking between operational ownership and imported worksheet timelines
- ClinSpark-only, team, status, milestone, and People Team filters
Daily Schedule
- Team-specific day schedules with two panels visible at once
- Editable times, notes, study selection, statuses, and multiple assignees
- Templates, copy/paste workflows, locking, audit trail, retention controls, and print support
- My Work and Workload integration for assigned daily tasks
Security & Access Control
- HttpOnly JWT sessions with refresh rotation and server-side validation
- Configurable roles, tab visibility, multi-team memberships, and per-user editable Team permissions
- Backend-enforced Team edit checks for Table and Daily Schedule workflows
- Rate limiting, CORS hardening, request limits, encrypted report credentials, and security event logging
Reports & Audits
- ClinSpark Reports definitions with Basic/Bearer authentication and reusable parameter sets
- Manual and scheduled invocation with sequential execution and retained newest completed files
- Builder Audits ingestion with duplicate detection, filters, pagination, and last-sync summaries
- Transfer Data processing with run status, logs, downloads, and optional encryption settings
Operations Admin
- User, role, team, permission, backup, audit, and server settings management
- Backup preview, restore, checksums, data health utilities, and forward-only migrations
- Presence, heartbeat, inactivity, retention, and performance settings for low-cost hosting
Architecture
The application follows a React SPA plus FastAPI backend architecture. Cloudflare Pages serves the frontend, Render hosts the API, and Neon PostgreSQL stores operational data. The system uses forward-only SQL migrations, service/repository/router boundaries, bounded retention settings, and cached server-prepared views for expensive timeline screens.
- Frontend: React 18, TypeScript, Vite, TailwindCSS, Zustand, React Router, Axios
- Backend: Python, FastAPI, Pydantic, async psycopg
- Database: PostgreSQL on Neon serverless
- Deployment: Cloudflare Pages frontend and Render backend
- Integrations: Power Automate workbook sync, ClinSpark report APIs, uploaded workbooks and files
Tech Stack
React 18
TypeScript
TailwindCSS
Zustand
FastAPI
Python
PostgreSQL
psycopg (async)
JWT / HttpOnly Cookies
Cloudflare Pages
Render
Neon
Axios
Vite
Screenshots
Challenges & Solutions
- Large workbook syncs: Background jobs extract Macro Timeline, Micro Timeline, Team Assign, and Enrollment data with duplicate cleanup, change summaries, event materialization, and overlapping-job guards.
- Team-aware editing: Users can belong to multiple teams, while backend edit checks restrict Table and Daily Schedule changes to teams they are allowed to modify.
- Workload visibility: Workload charts support Table Workload and Daily Schedule Workload modes with zoom levels, row reordering, favorites, and persistent filters.
- Low-cost infrastructure: Indexed queries, cached Confinement views, bounded date windows, retention policies, request cancellation, and batch upserts keep Render and Neon usage manageable.
- Report automation safety: Reusable report definitions, encrypted credentials, enabled parameter sets, sequential calls, and newest-file retention reduce repeated setup without storing unnecessary output.
Outcomes & Impact
- Centralized study schedules, imported timelines, assignment ownership, workload charts, daily tasks, reports, and audit review in one browser workspace.
- Reduced manual Excel scanning and repeated report setup through persistent filters, workbook sync, reusable report definitions, templates, and copy/paste workflows.
- Improved team collaboration with role permissions, Team edit permissions, audit trails, backups, and operational data-health utilities.
- Designed for practical daily use on low-cost hosting while preserving clear boundaries around official validated source systems.
Lessons Learned
- Internal tools become much more valuable when they grow around actual recurring questions, not around a fixed initial feature list.
- Persistent filters, clear empty states, and print/export support often save as much user time as major backend automation.
- Role and Team permissions need to be designed early because operational data ownership rarely maps to a single global admin/editor split.
Future Improvements
- Add browser-based end-to-end tests with seeded accounts.
- Add richer concurrent-edit warnings for Daily Schedule changes.
- Add exportable workload reports and dashboard widgets for Daily Schedule workload.
- Add configurable retention-policy controls by data type.