PSA/ee/docs/plans/2025-10-20-contract-template-overhaul-plan.md

Contract Template Overhaul & Client Assignment Alignment Plan

Summary

This plan restructures the contracts domain so that contracts and contract_lines represent reusable templates, while client_contracts and client_contract_lines continue to hold tenant-specific instances. The work spans backend schema changes, domain service updates, UI flow adjustments for both the contract template wizard and the client contract wizard, and a data migration to decouple template-only fields from client-specific values.

Goals

• Treat contracts / contract_lines as tenant-scoped templates that omit client-specific commercial terms (rates, quantities, bucket settings, etc.).
• Ensure client_contracts and client_contract_lines own all mutable billing terms and lifecycle metadata used for invoicing.
• Provide a streamlined Contract Template Wizard that mirrors the client contract wizard but skips per-client pricing fields.
• Allow the Client Contract Wizard to bootstrap from a selected contract template, pre-filling compatible sections while prompting for client-specific details.
• Migrate existing data so that all commercial terms live on client_contract_lines (with fallbacks for empty values).
• Keep billing engine, reporting, and integrations functional during the transition.

Non-Goals

• Overhauling the service catalog or invoicing engine beyond required interface changes.
• Introducing new billing models or pricing logic.
• Building multi-tenant contract sharing across different Alga tenants.

Stakeholders

• Billing & Finance product owners
• Frontend billing dashboard team
• Backend billing platform team
• QA & Test Automation team

High-Level Timeline

1. Phase 0 – Discovery & Alignment (1 week)
2. Phase 1 – Schema Preparation & Migrations (1.5 weeks)
3. Phase 2 – Backend Domain Updates (2 weeks)
4. Phase 3 – Frontend Template Wizard & Client Wizard Enhancements (2 weeks)
5. Phase 4 – Data Migration & Backfill (1 week)
6. Phase 5 – QA, Rollout, & Monitoring (1 week)

Total estimated duration: ~8.5 weeks including buffer.

---

Phase 0 – Discovery & Alignment

Goals: Validate assumptions about current contracts usage, document edge cases, and align on template vs. client instance semantics.

• Inventory existing tables/fields using DB diagrams and confirm ownership (e.g. contracts, contract_lines, contract_line_mappings, client_contracts, client_contract_lines, client_contract_line_service_configurations).
  • Review Knex migrations in server/migrations (start with files containing contract/billing naming) to capture current columns, composite keys, and sequences on each related table.
  • Map multi-tenant helpers referenced by migrations (e.g., server/src/lib/db/admin.ts, server/src/lib/db/knexfile.ts, shared/db/index.ts) to understand how schema helpers are invoked during tenant provisioning.
  • Document any triggers, row level security policies, and dependent views/materialized views defined in SQL files (see server/migrations/*cleanup_billing_to_contracts.cjs and cleanup_obsolete_email_tables.sql for precedent).
• Review backend usage patterns:
  • Run rg "client_contract" and rg "contract_lines" across server/src/lib/api/services, server/src/lib/billing, and ee/server/src/lib to list the service entrypoints, DTOs, and utilities that currently rely on template pricing data.
  • Trace how the REST/Next.js routes obtain contract data (e.g., server/src/app/api/v1/contracts, enterprise overrides under ee/server/src/app/api) and note which serializers expect price fields on contracts vs. client_contracts.
  • Summarize how integration and E2E suites seed contract data through helpers in server/src/test/integration, server/src/test/e2e, and scripts such as server/scripts/create-tenant.ts and server/scripts/seed-templates.js that provision default billing records.
• Capture UI flows:
  • Walk through ContractWizard.tsx, ContractDetail.tsx, and billing dashboard routes to catalog inputs that currently bind to template pricing, quantities, or bucket settings.
  • Note feature flags, context providers, and state containers (Contracts.tsx wizard state, billing feature flags) that determine whether a template can be selected during client contract creation.
• Produce a conclusive spec doc (append to this plan) enumerating which fields should remain on template tables, including an explicit mapping of “template column → client instance column” plus validation rules for missing data.
• Align with stakeholders on acceptance criteria and rollout checkpoints; schedule reviews with Billing PM, finance, and QA to confirm migration sequencing and sign-off expectations.

Phase 0 Progress Log (2025-02-14)

• Inventory current schema surface
  • contracts table stores template metadata today (contract_name, billing_frequency, activation flags) with tenant-scoped PKs and indexes (server/migrations/20251008000001_rename_billing_to_contracts.cjs:166-183).
  • contract_lines and supporting config tables retain pricing knobs such as base rates, overtime multipliers, and bucket limits (server/migrations/20251008000001_rename_billing_to_contracts.cjs:400-936).
  • Client instance data is split across client_contracts (start/end dates, active state) and client_contract_lines (per-client assignments with timestamps) (server/migrations/20251008000001_rename_billing_to_contracts.cjs:240-569).
  • Multi-tenant enforcement relies on shared addTenantForeignKey helper; no triggers or RLS rules are defined for these tables in current migrations (server/migrations/20251008000001_rename_billing_to_contracts.cjs:1214-1246).
• Review backend usage patterns
  • REST entry points live under Next routes (server/src/app/api/v1/contracts/route.ts, server/src/app/api/v1/contract-lines/*) backed by ApiContractLineController, which exposes CRUD, template copy, and client assignment flows (server/src/lib/api/controllers/ApiContractLineController.ts:1-210).
  • createContractFromWizard persists pricing, rate tiers, and bucket settings directly onto template tables before seeding client assignments (server/src/lib/actions/contractWizardActions.ts:80-260).
  • Reporting and billing pipelines pull amounts from contract_line_fixed_config, contract_line_services, and fall back to contract_line_mappings.custom_rate, so template-vs-instance separation must preserve these call sites (server/src/lib/reports/definitions/contracts/expiration.ts:60-96).
  • Test helpers in server/test-utils/billingTestHelpers.ts:520-640 seed both template and client tables, mirroring production expectations for pricing fields.
• Capture current UI flows
  • ContractWizard collects client-facing pricing inputs (base rates, quantities, usage rates, bucket overlays) and posts them through createContractFromWizard, reinforcing that templates currently own monetary fields (server/src/components/billing-dashboard/contracts/ContractWizard.tsx:23-210).
  • Contract list surfaces (server/src/components/billing-dashboard/contracts/Contracts.tsx:300-360) and rate dialogs (server/src/components/billing-dashboard/contracts/ContractPlanRateDialog.tsx:12-72) expect per-line pricing to be editable on template entities.
• Template field ownership spec
  • Captured a column-by-column mapping for core tables plus proposed client-instance counterparts under “Template vs Instance Field Ownership”.
• Stakeholder alignment
  • Schedule finance/QA/billing sync once ownership matrix is stable; confirm rollout checkpoints and validation requirements.

Template vs Instance Field Ownership

Table	Column / Concern	Current Role	Proposed Owner	Notes / Follow-ups
contracts	contract_name, contract_description, billing_frequency, is_active, timestamps (server/migrations/20251008000001_rename_billing_to_contracts.cjs:166-227)	Wizard defaults + feature gating	Template	Keep columns; add template_metadata JSON for guidance strings and UI hints.
client_contracts	client_id, start_date, end_date, is_active, contract_id, timestamps (server/migrations/20251008000001_rename_billing_to_contracts.cjs:240-284)	Client lifecycle	Client instance	Add template_contract_id FK + created_from_template_version to track template revisions.
contract_line_mappings	display_order, custom_rate, timestamps (server/migrations/20251008000001_rename_billing_to_contracts.cjs:300-345)	Associates template contracts↔lines & overrides	Mixed	Keep ordering on template. Move custom_rate into client_contract_line_pricing table keyed by client_contract_line_id.
contract_lines	contract_line_name, description, contract_line_type, service_category, activation flags (server/migrations/20251008000001_rename_billing_to_contracts.cjs:400-468)	Template scaffolding	Template	Preserve; add is_template flag (Phase 1).
contract_lines	billing_frequency, overtime/after-hours columns (enable_overtime, overtime_rate, etc.)	Currently shared defaults but billable	Client instance	Copy into client_contract_line_terms (new) linked to client_contract_line_id; template retains optional suggested defaults stored in contract_line_template_terms.
contract_line_fixed_config	base_rate, enable_proration, billing_cycle_alignment (server/migrations/20251008000001_rename_billing_to_contracts.cjs:470-522)	Controls fixed-fee pricing	Client instance	Duplicate structure into client_contract_line_fixed_config; template table keeps nullable default columns only.
contract_line_services	service_id, quantity, custom_rate (server/migrations/20251008000001_rename_billing_to_contracts.cjs:790-838)	Bundled services + rate overrides	Client instance	Create client_contract_services keyed by client_contract_line_id; template table keeps service_id + default_role style metadata (quantity -> default_quantity, custom_rate -> remove).
contract_line_service_configuration	configuration_type, custom_rate, quantity (server/migrations/20251008000001_rename_billing_to_contracts.cjs:840-894)	Service-specific config (Fixed/Bucket/Usage)	Client instance	Introduce client_contract_service_configuration keyed by client_contract_service_id; template table retains configuration_type + optional metadata only.
contract_line_service_bucket_config	total_minutes, overage_rate, allow_rollover (server/migrations/20251008000001_rename_billing_to_contracts.cjs:960-1010)	Bucket sizing & overage pricing	Client instance	New client_contract_service_bucket_config; templates store defaults in contract_line_service_defaults JSON.
contract_line_service_fixed_config	base_rate (server/migrations/20251008000001_rename_billing_to_contracts.cjs:1012-1060)	Fixed rate overrides	Client instance	Move to client_contract_service_fixed_config; template retains default guidance.
contract_line_service_hourly_config	minimum_billable_time, overtime multipliers, etc. (server/migrations/20251008000001_rename_billing_to_contracts.cjs:1062-1114)	Hourly behavior	Client instance	New client_contract_service_hourly_config; template table trimmed to recommended values.
contract_line_service_hourly_configs	hourly_rate, minimum_billable_time (server/migrations/20251008000001_rename_billing_to_contracts.cjs:1116-1152)	Hourly tier matrix	Client instance	Mirror table client_contract_service_hourly_tiers (per config_id + rate). Templates keep structural tiers without dollar amounts.
contract_line_service_rate_tiers	Tier pricing ladder (server/migrations/20251008000001_rename_billing_to_contracts.cjs:1154-1194)	Usage tier pricing	Client instance	Move rate fields to client_contract_service_rate_tiers; templates retain min/max_quantity.
contract_line_service_usage_config	unit_of_measure, enable_tiered_pricing, base_rate, minimum_usage (server/migrations/20251008000001_rename_billing_to_contracts.cjs:1196-1244)	Usage pricing baseline	Mixed	Keep descriptive fields (unit_of_measure, enable_tiered_pricing) on template; relocate base_rate, minimum_usage to client usage config table.
contract_line_discounts	Discount linkages (server/migrations/20251008000001_rename_billing_to_contracts.cjs:896-940)	Template-level discount association	Mixed	Maintain template associations for suggestion; create client_contract_line_discounts for actual applied discounts per client.
client_contract_lines	client_id, contract_line_id, start_date, is_active, timestamps (server/migrations/20251008000001_rename_billing_to_contracts.cjs:520-569)	Client assignment roster	Client instance	Add template_contract_line_id, billing_frequency, is_customized, monetary columns or FK out to new pricing tables.

Client Instance Table Additions

• client_contract_line_terms: (client_contract_line_id PK/FK, billing_frequency, enable_overtime, overtime_rate, overtime_threshold, enable_after_hours_rate, after_hours_multiplier). Values copied from contract_lines defaults during migration; future writes require these fields for billing engine parity.
• client_contract_services: (client_contract_service_id uuid, client_contract_line_id, service_id, quantity, rate_cents, effective_date, timestamps). Supersedes contract_line_services pricing data for clients; templates keep a slim contract_template_services table with default_quantity/default_role.
• client_contract_service_configuration tree:
  • client_contract_service_configuration (mirrors schema of contract_line_service_configuration minus template defaults; keyed by client_contract_service_id).
  • client_contract_service_bucket_config, client_contract_service_fixed_config, client_contract_service_hourly_config, client_contract_service_hourly_tiers, client_contract_service_rate_tiers, client_contract_service_usage_config matching existing template tables but referencing client_contract_service_configuration_id.
• client_contract_line_pricing: (client_contract_line_id, source_template_contract_line_id, custom_rate_cents, source_contract_line_mapping_id, notes). Receives data from contract_line_mappings.custom_rate.
• client_contract_line_discounts: (client_contract_line_id, discount_id, applied_rate, start_date, end_date) to represent actual discount applications, distinct from template recommendations.
• contract_line_template_terms: New template companion storing optional defaults for term-related fields stripped from contract_lines (mirrors columns added to client_contract_line_terms but nullable).

Cross-System Considerations

• Billing engine (server/src/lib/billing/billingEngine.ts:409-470): Update all joins to reference client_contract_* tables for pricing, ensuring totals never pull from template columns.
• Reporting (server/src/lib/reports/definitions/contracts/expiration.ts:60-96, .../revenue.ts:45-85): Redirect rate lookups to client_contract_line_pricing and corresponding client config tables.
• Test fixtures (server/test-utils/billingTestHelpers.ts:520-640): Extend helper APIs to seed both template defaults and client-specific pricing, aligned with new schema.
• Historical data: For closed contracts, persist original contract_id/contract_line_id associations via new template_* foreign keys to support audits without keeping legacy rate columns on templates.

Phase 1 – Schema Preparation & Migrations

Goals: Add new columns/constraints to support template semantics and prepare for data migration.

• Draft ERD updates showing final relationships.
• Database migrations (Knex + SQL):
  • Add is_template boolean flags to contracts and contract_lines (default true, enforced via check to keep backward compatibility during migration).
  • Introduce template_contract_id FK on client_contracts (nullable) referencing contracts.id to track origin template.
  • Introduce template_contract_line_id FK on client_contract_lines referencing contract_lines.id.
  • Create template companion tables (contract_line_template_terms, contract_template_services, contract_line_service_defaults) to store recommended values separated from instance pricing.
  • Add nullable JSONB column template_metadata to contracts for wizard hints (e.g. recommended payment cadence, notes).
  • Introduce client-instance tables (client_contract_line_terms, client_contract_services, client_contract_service_configuration and child tables, client_contract_line_pricing, client_contract_line_discounts) mirroring the template schema but keyed to client_contract_line_id.
  • Update existing template tables to drop or rename monetary fields:
    • Convert contract_line_services.quantity → default_quantity, remove custom_rate.
    • Remove rate columns from contract_line_service_* tables or mark nullable defaults pending migration.
    • Drop contract_line_mappings.custom_rate once client pricing table is populated.
• Create forward migration scripts under server/migrations (e.g., YYYYMMDDHHMM_contract_template_overhaul.cjs), run locally with npm run migrate, and document verification queries for teardown.
• Write migration rollback strategy and add to ee/docs/plans/... appendix.

Phase 1 Status – 2025-02-14

• Drafted initial Knex migration (server/migrations/20250214090000_contract_templates_phase1.cjs) that introduces template flags, template metadata JSON, and scaffolds client-specific tables for pricing/config cloning.
• Update existing migrations/services to remove legacy pricing fields once data migration completes (tracked in Phase 4).
• Align migration with rip-and-replace approach (no dual-write): adjust contract assignment services once data backfill happens.

Phase 2 – Backend Domain Updates

Goals: Ensure services treat templates as blueprints and client contracts as concrete instances.

• Update repositories/services:
  • server/src/lib/api/services/ContractService.ts → expose APIs to manage template contracts (CRUD) without pricing fields.
  • server/src/lib/api/services/ClientContractService.ts → accept optional templateContractId and clone relevant lines into client_contract_lines with new pricing inputs.
  • server/src/lib/api/services/ContractLineService.ts → ensure template lines handle service attachment and metadata only.
  • server/src/lib/billing/billingEngine.ts and related modules → confirm all billing amounts come from client_contract_lines.
• Adjust DTOs/interfaces under server/src/interfaces/billing.interfaces.ts to distinguish ContractTemplate vs ClientContract payloads.
• Implement cloning utilities in server/src/lib/billing/utils/templateClone.ts (new file) to copy template scaffolding into client contract context.
• Update validation logic to forbid setting pricing on template endpoints; add API errors.
• Ensure service catalog linkage remains consistent (e.g., plan_service_configuration usage if applicable).
• Extend unit/integration tests:
  • New tests in server/src/test/unit/contracts verifying template CRUD.
  • Adjust existing tests in server/src/test/integration/api/storageHandlers.test.ts and server/src/test/e2e/api/services.e2e.test.ts to use client contract rates only.

Phase 2 Status – 2025-02-14

• Implemented cloneTemplateContractLine utility and integrated it with API and server-action entry points for client contract assignments (ContractLineService.assignPlanToClient, client contract apply/add flows). Assignments now populate the new client_contract_* tables directly (rip-and-replace path).
• Updated shared interfaces/schemas to expose template linkage fields consumed by the revised services.
• Update billing engine/report consumers to rely on client_contract_line_pricing and related tables (pending once data migration completes).

Phase 3 – Frontend Template Wizard & Client Wizard Enhancements

Goals: Provide UX for managing templates and applying them when creating client contracts.

• Create Contract Template Wizard:
  • New component server/src/components/billing-dashboard/contracts/ContractTemplateWizard.tsx based on ContractWizard.tsx but omitting rate/quantity inputs.
  • Update routing in BillingDashboard.tsx to expose template management section.
  • Add API hooks in server/src/components/billing-dashboard/contracts/hooks.ts for template CRUD (useCreateContractTemplate, useUpdateContractTemplate).
  • Ensure UI surfaces template metadata (line types, services) with ability to add/remove lines and attach services.
• Update existing ContractWizard.tsx to support selecting a template:
  • Add initial step that lists templates via new API.
  • On template selection, pre-fill line items, services, default descriptions.
  • Prompt user to enter client-specific pricing/quantities before finalizing.
  • Handle optional template metadata hints (e.g., recommended billing cadence) in the UI.
• Split the contract detail surface into dedicated components:
  • ContractTemplateDetail.tsx focused on template metadata, line composition, and recommended usage notes.
  • ClientContractDetail.tsx focused on client-specific terms (billing cycles, PO info, assignments) with clear linkage back to the originating template.
  • Update routing so the Billing Dashboard chooses the appropriate detail component based on whether the record is a template or a client assignment, and provide navigation between the two where relevant.
• Refresh jest/react-testing-library coverage for wizards (server/src/components/billing-dashboard/contracts/__tests__/).

Phase 4 – Data Migration & Backfill

Goals: Move existing pricing data from templates to client contracts and link instances to their originating templates.

• Author migration script in server/scripts/contract-template-decoupling.ts:
  • For each client_contract referencing a contract_id, set template_contract_id.
  • For each client_contract_line, set template_contract_line_id.
  • Copy pricing fields from template tables into new client-instance tables: contract_line_fixed_config.base_rate, contract_line_services.quantity/custom_rate, contract_line_service_bucket_config.total_minutes/overage_rate, hourly configs, usage configs, and discount links.
  • Populate client_contract_line_pricing using data from contract_line_mappings.custom_rate and fixed-config fallbacks; ensure historical overrides preserved.
  • Null out or remove template pricing columns once client data is populated, leaving only optional defaults.
• Provide dry-run mode and logging (tenant ID, contract ID counts).
• Write verification queries (appendix) to ensure no non-null pricing remains on templates.
• Coordinate deployment window; run migration in staging then production.

Phase 5 – QA, Rollout, & Monitoring

Goals: Validate functionality end-to-end and ensure stability after launch.

• Manual QA checklist:
  • Create new template, apply to new client contract, confirm pricing only captured in client contract.
  • Update template and ensure existing client contracts unaffected.
  • Regression test billing runs/invoice generation.
• Automation updates:
  • Extend Playwright flow ee/server/src/__tests__/integration/contract-wizard-happy-path.playwright.test.ts to cover template selection.
  • Update Cypress/Playwright dashboards (if any) to reflect new UI navigation.
• Monitoring:
  • Add logs/metrics in contract creation endpoints to monitor template usage.
  • Add alert if billing engine encounters template pricing (should be null).
• Launch plan:
  • Enable feature flag billing.contractTemplatesV2 for internal tenants first.
  • Roll out to all tenants after one billing cycle.

Appendix

• Schema Change Checklist:
  • Add Knex migration files covering forward changes and matching rollback scripts.
  • Create SQL migrations for production deployment (shared/db/migrations/20251020120000_contract_template_overhaul.sql).
  • Add rollback scripts in shared/db/migrations/rollback/.
• Documentation:
  • Update docs/billing.md and docs/overview.md sections on contracts.
  • Add how-to guide under ee/docs/guides/contract-templates.md once implemented.
• Decisions (2025-02-14):
  • No third-party integrations currently ingest template pricing fields. Code search across sdk/*, shared/**/*, and enterprise services shows contract_line_mappings.custom_rate is only read by internal billing engines, reports, and UI controllers (server/src/lib/reports/definitions/contracts/*.ts, server/src/lib/api/services/ContractLineService.ts). Safe to migrate pricing without breaking external consumers; document in release notes for partners.
  • Contract template metadata will include a recommended_services array (service IDs plus optional notes) alongside UI hint fields in template_metadata. Wizard will surface these recommendations when initializing client contracts, while actual service bundling remains client-specific via client_contract_services.