Report Field Schema
Data Entity
Description
Defines the dynamic form field configuration for post-session reports on a per-organization basis. Specifies field types (text, multiline, checkbox, radio), labels, required flags, and validation rules as a versioned JSON schema. Loaded at runtime by the dynamic field renderer and cached per organization to avoid redundant network calls.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for this schema record, generated server-side on creation | PKrequiredunique |
organization_id |
uuid |
Foreign key to the owning organization. All schema lookups are scoped by this value. Enforced by Supabase RLS so tenants cannot read each other's schemas. | required |
form_type |
enum |
Identifies which report form this schema applies to. Allows an organization to maintain distinct schemas for different report categories (e.g., home-visit vs. phone-session). | required |
field_definitions |
json |
Ordered array of field configuration objects. Each object specifies field_id (string), field_type (text|multiline|checkbox|radio), label (string), required (boolean), order (integer), placeholder (string, optional), options (array of {value, label} for radio/checkbox), and validation_rules (object with min_length, max_length, pattern). Parsed and validated by org-field-config-loader at read time. | required |
version |
integer |
Monotonically increasing version counter. Incremented on every update. Cached schemas are invalidated when a newer version is detected. Starts at 1 on creation. | required |
is_active |
boolean |
Soft-delete / active flag. Only the active schema is served at runtime. Deactivating a schema preserves historical record linkage without breaking existing post_session_report rows that reference it. | required |
label_overrides |
json |
Optional map of field_id to display label string. Allows an organization to override default field labels without changing the schema structure — e.g., renaming 'Health Status' to 'Participant Wellbeing'. Merged with field_definitions at render time by the dynamic-field-renderer. | - |
schema_metadata |
json |
Auxiliary schema-level metadata such as display title, introduction text, and submission confirmation message. Rendered by post-session-report-screen around the dynamic field list. | - |
created_by |
uuid |
User ID of the organization admin who created or last modified this schema. Used for audit trail display in the admin portal. | required |
created_at |
datetime |
UTC timestamp of record creation. Set once by the database default and never updated. | required |
updated_at |
datetime |
UTC timestamp of the last modification. Updated automatically on every write. Used by report-schema-service to detect staleness against the cached version. | required |
Database Indexes
idx_report_field_schema_org_form_active
Columns: organization_id, form_type, is_active
Enforces at most one active schema per organization per form_type. The partial unique constraint covers only rows where is_active = true.
idx_report_field_schema_organization_id
Columns: organization_id
Supports fast lookup of all schemas for a given organization, used by admin listing views.
idx_report_field_schema_updated_at
Columns: updated_at
Supports cache staleness checks by report-schema-service comparing server updated_at against the locally cached timestamp.
idx_report_field_schema_org_form_version
Columns: organization_id, form_type, version
Supports version-based lookups for historical schema reconstruction when auditing submitted reports.
Validation Rules
organization_id_references_active_organization
error
Validation failed
form_type_is_valid_enum
error
Validation failed
field_definitions_is_valid_json_array
error
Validation failed
field_type_values_constrained
error
Validation failed
field_label_max_length
error
Validation failed
field_order_positive_integer
error
Validation failed
options_values_non_empty_strings
error
Validation failed
validation_rules_json_structure
warning
Validation failed
label_overrides_keys_reference_valid_fields
warning
Validation failed
schema_metadata_valid_json_object
warning
Validation failed
Business Rules
single_active_schema_per_org_form_type
At most one schema record with is_active = true may exist for a given (organization_id, form_type) combination. When a new schema is activated, the previous active schema for the same org and form_type must be set to is_active = false atomically within the same transaction.
version_monotonic_increment
The version field must be exactly (previous_version + 1) on every update. Concurrent writes that produce the same version are rejected. This ensures report-schema-cache can detect staleness without a full schema comparison.
cache_invalidation_on_version_change
When report-schema-service detects that the server version is greater than the locally cached version, it must evict the stale entry from report-schema-cache and re-fetch the full schema before returning it to callers. Stale schemas must never be used for new report submissions.
soft_delete_only
Schemas are never hard-deleted because submitted post_session_report rows may reference the schema version active at submission time for audit reconstruction. Deactivation (is_active = false) is the only supported removal operation.
org_rls_isolation
Supabase row-level security policies restrict all reads and writes to rows whose organization_id matches the current session tenant scope. No cross-tenant schema access is permitted. report-schema-service must always pass the authenticated org context when querying.
field_definitions_minimum_fields
A schema must define at least one field in the field_definitions array. An empty schema cannot be activated and will be rejected at save time.
radio_and_checkbox_require_options
Any field with field_type of 'radio' or 'checkbox' must include a non-empty options array in its field_definitions entry. Fields of type 'text' or 'multiline' must not include an options array.
field_ids_unique_within_schema
All field_id values within a single field_definitions array must be unique. Duplicate field IDs cause ambiguous form state and are rejected on save.
CRUD Operations
Storage Configuration
Entity Relationships
A post-session report is rendered using the organization's dynamic field schema active at the time of submission
Each field schema is owned by exactly one organization and defines that organization's post-session report form structure