Integration and API Spec

Integration and API Spec

Data Quality Monitoring

Read-only connection to the primary database. The Data Check Agent issues parameterised SELECT queries against configured tables on each scheduled run. No write operations are performed against PostgreSQL by any agent.

// Input
Ruleset array loaded from Google Sheets (table_name, field_name, rule_type, threshold)
// Query pattern
SELECT <monitored_fields> FROM <table_name> WHERE updated_at >= NOW() - INTERVAL '24 hours'
// Output
Row array: { table, field, row_id, raw_value, rule_violated, severity_score }

Used in two roles: as a write target for the issue log (Agents 1 and 2) and as a read source for weekly aggregation (Agent 3). A single base contains the Issues table and the Runs table.

// Input (Agent 1 write)
Issue object: { source_table, field_name, rule_violated, affected_row_count, severity, detected_at }
// Output (Agent 3 read)
Filtered records: issues WHERE detected_at >= start_of_week AND detected_at <= end_of_week
// Aggregated output to Reporting Agent
{ total_issues, critical_count, high_count, moderate_count, low_count, resolved_count, open_count }

Read-only at runtime. The Data Check Agent reads the active ruleset from a designated Google Sheet at the start of each check run. This sheet is the single source of truth for which tables, fields, and rule types are active.

// Input
GET https://sheets.googleapis.com/v4/spreadsheets/{RULESET_ID}/values/Rules
// Output
Ruleset array: [{ table_name, field_name, rule_type, parameter, severity_weight, active }]
// Filtered at runtime
Only rows where active == true are passed to the rules engine

Used by the Issue Routing and Notification Agent to create tickets for Critical and High severity issues. Each ticket is pre-filled with structured issue detail. No ticket update or deletion operations are performed by the automation.

// Input
Critical/High issues from Airtable write: { field_name, source_table, rule_violated, affected_row_count, severity, airtable_record_url }
// POST /rest/api/3/issue
{ project: { key: JIRA_PROJECT_KEY }, issuetype: { id: JIRA_ISSUE_TYPE_ID }, summary, description (ADF), priority, labels }
// Output
{ id, key, self }  -> key stored back to Airtable Issues row as jira_ticket_key

Used by the Issue Routing and Notification Agent to post a formatted summary message to the IT channel after each check run. One message per run, not one message per issue.

// Input
Run summary: { run_timestamp, total_issues, critical_count, high_count, moderate_count, low_count, jira_tickets_created: [key, url], airtable_view_url }
// POST https://slack.com/api/chat.postMessage
{ channel: SLACK_CHANNEL_ID, blocks: [...Block Kit JSON...] }
// Output
{ ok: true, ts, channel }  -> ts stored for optional thread replies

Used by the Reporting and Metrics Agent to push aggregated weekly quality metrics as custom metric series. The Datadog dashboard is pre-configured with widgets that visualise these metrics over time.

// Input
Weekly aggregates from Airtable: { week_start, total_issues, critical, high, moderate, low, resolved, open }
// POST https://api.datadoghq.com/api/v2/series
{ series: [{ metric: 'dq.issues.total', type: 'gauge', points: [{ timestamp, value }], tags }] }
// Output
HTTP 202 Accepted -> log response; non-202 triggers retry with exponential backoff

// CREDENTIAL STORE CONTENTS
// PostgreSQL
PG_HOST=<database-hostname-or-ip>
PG_PORT=5432
PG_DBNAME=<target-database-name>
PG_USER=dq_monitor_ro
PG_PASSWORD=<read-only-service-account-password>

// Google Sheets (service account)
GOOGLE_SERVICE_ACCOUNT_JSON=<full JSON key content or file path>
GOOGLE_SHEETS_RULESET_ID=<spreadsheet-id-from-sheet-url>

// Airtable
AIRTABLE_PAT=<personal-access-token>
AIRTABLE_BASE_ID=<base-id-starting-app...>
AIRTABLE_ISSUES_TABLE_ID=<table-id-starting-tbl...>
AIRTABLE_VIEW_URL=<url-of-Issues-view-for-Slack-links>

// Jira
JIRA_BASE_URL=https://<your-subdomain>.atlassian.net
JIRA_USER_EMAIL=dq-automation@yourdomain.com
JIRA_API_TOKEN=<api-token-from-atlassian-account-settings>
JIRA_PROJECT_KEY=DQ
JIRA_ISSUE_TYPE_ID=<numeric-id-retrieved-from-issuetype-endpoint>

// Slack
SLACK_BOT_TOKEN=xoxb-<bot-token>
SLACK_CHANNEL_ID=<channel-id-not-channel-name>
SLACK_SUPPRESS_CLEAN_RUNS=true

// Datadog
DATADOG_API_KEY=<api-key>
DATADOG_APP_KEY=<application-key>
DATADOG_SITE=datadoghq.com
DATADOG_DASHBOARD_ID=<dashboard-id-for-reference>

// Inbound webhook (Agent 1 import event trigger)
WEBHOOK_SECRET=<hmac-sha256-shared-secret>

// Orchestration config
CHECK_SCHEDULE_UTC=02:00
REPORT_SCHEDULE_UTC=Sunday 04:00