Sync Log
Data Entity
Description
Audit log entries for every outbound sync attempt to external systems, capturing connector type, status, records processed, error messages, and timestamps. Provides debugging visibility and is surfaced in the admin integration status screen.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Immutable primary key for the sync log entry, generated server-side on creation | PKrequiredunique |
integration_config_id |
uuid |
Foreign key referencing the integration_configs record that was targeted by this sync attempt. Links the log to the specific org-connector configuration. | required |
organization_id |
uuid |
Denormalized organization ID copied from integration_configs at write time to support fast RLS-scoped queries without a join on every read. | required |
connector_type |
enum |
Identifies which external system connector executed the sync. Determines which sync service wrote the entry. | required |
sync_type |
enum |
Classifies the category of sync operation performed, distinguishing accounting data pushes from member roster syncs and portal event syncs. | required |
status |
enum |
Current lifecycle state of this sync attempt. Updated in place as the attempt progresses or retries. | required |
records_processed |
integer |
Count of records successfully transmitted to the external system during this sync attempt. Null until the attempt completes. | - |
records_failed |
integer |
Count of records that were attempted but rejected or errored by the external system during this sync. Null until the attempt completes. | - |
records_total |
integer |
Total count of records that were in scope for this sync attempt, regardless of outcome. Set at attempt start. | - |
error_code |
string |
Machine-readable error code returned by the external system or generated by the connector on failure. Null on success. | - |
error_message |
text |
Human-readable error description from the external system or internal connector. Truncated to 4000 characters. Null on success. | - |
retry_count |
integer |
Number of times this sync attempt has been retried after initial failure. Zero on first attempt. | required |
triggered_by |
enum |
Indicates what initiated the sync: an automated background job, a manual admin trigger from the UI, or an inbound webhook event from the external system. | required |
triggered_by_user_id |
uuid |
User ID of the administrator who manually triggered the sync. Null for automatic and webhook-triggered syncs. | - |
source_record_id |
uuid |
ID of the primary source record that was synced (e.g., reimbursement_approval.id or course_enrollment.id). Null for full roster syncs. Enables tracing a specific record's sync history. | - |
source_record_type |
string |
Table name or domain type of the source record referenced by source_record_id. Enables polymorphic lookup without joins (e.g., 'reimbursement_approvals', 'driver_honorariums', 'course_enrollments'). | - |
external_reference_id |
string |
Identifier assigned by the external system upon successful ingestion (e.g., Xledger transaction ID, Dynamics entity ID). Null on failure or if the external system does not return an ID. | - |
request_payload_hash |
string |
SHA-256 hash of the serialized outbound payload. Stored for deduplication checks and audit trail without persisting sensitive financial or personal data. | - |
http_status_code |
integer |
HTTP response status code received from the external system API. Null for non-HTTP failures (e.g., network timeout, serialization error). | - |
duration_ms |
integer |
Wall-clock duration of the sync attempt in milliseconds, computed as completed_at minus started_at. Populated when the attempt reaches a terminal status. | - |
started_at |
datetime |
UTC timestamp when the sync attempt began execution. Set on creation and never updated. | required |
completed_at |
datetime |
UTC timestamp when the sync attempt reached a terminal state (success, failed, retried_success, retried_failed). Null while status is pending or in_progress. | - |
metadata |
json |
Arbitrary connector-specific context captured at sync time, such as API endpoint URLs, batch identifiers, field mapping version, or OAuth token expiry. Not surfaced in UI but available for deep debugging. | - |
Database Indexes
idx_sync_logs_integration_config_id
Columns: integration_config_id
idx_sync_logs_organization_id_started_at
Columns: organization_id, started_at
idx_sync_logs_connector_type_status
Columns: connector_type, status
idx_sync_logs_source_record
Columns: source_record_type, source_record_id
idx_sync_logs_started_at_desc
Columns: started_at
idx_sync_logs_status_retry_count
Columns: status, retry_count
Validation Rules
integration_config_id_must_exist
error
Validation failed
connector_type_must_match_integration_config
error
Validation failed
started_at_not_in_future
error
Validation failed
completed_at_must_follow_started_at
error
Validation failed
error_fields_required_on_failure
error
Validation failed
records_processed_non_negative
error
Validation failed
records_failed_not_exceed_total
warning
Validation failed
http_status_code_range
error
Validation failed
triggered_by_user_id_required_for_manual
error
Validation failed
source_record_type_required_when_id_present
error
Validation failed
request_payload_hash_format
error
Validation failed
Business Rules
immutable_after_terminal_state
Once a sync log entry reaches a terminal status (success, failed, retried_success, retried_failed), its started_at, connector_type, sync_type, organization_id, and source_record fields must not be modified. Only status, completed_at, duration_ms, error fields, and retry_count may be updated during retry cycles.
one_active_attempt_per_source_record
For a given source_record_id and connector_type combination, at most one sync log entry may exist in 'pending' or 'in_progress' status at any time. New attempts must wait for the previous attempt to reach a terminal state. Prevents duplicate concurrent submissions to external APIs.
retry_limit_enforcement
A sync attempt may not be retried more than 10 times (retry_count max: 10). After 10 retries the status must be set to 'retried_failed' and no further retry attempts are enqueued. Prevents infinite retry loops consuming API quota.
rls_org_scoped_read
Supabase RLS policy restricts read access to sync_logs rows where organization_id matches the authenticated user's active organization context. Admins may only read logs for their own organization scope. Global admins may read across all organizations.
no_delete_for_non_admins
Sync log entries are append-only audit records. Delete operations are restricted to admin-level database maintenance tasks only, not exposed to application-level service calls. Ensures the audit trail is tamper-resistant.
completed_at_set_on_terminal_status
When status transitions to any terminal state, completed_at must be populated with the current UTC timestamp and duration_ms must be computed. Null completed_at is only permitted for pending and in_progress statuses.
organization_id_denormalized_on_create
When a new sync log entry is created, organization_id must be copied from the referenced integration_configs record at write time. This denormalization supports RLS enforcement and dashboard queries without requiring a join on every read.
CRUD Operations
Storage Configuration
Entity Relationships
Every sync attempt against an integration configuration creates an audit log entry for monitoring