jira-sync-engine

Syncs Jira Cloud data (issues, comments, worklogs) into PostgreSQL. Supports real-time webhook ingestion and batch backfill via JQL.

Installation

npm install jira-sync-engine drizzle-orm

You also need a PostgreSQL driver that drizzle supports — e.g. pg, postgres, @neondatabase/serverless, etc.

Quick start

Pass your existing drizzle instance so the library shares your connection pool and credentials:

import { drizzle } from "drizzle-orm/node-postgres"; // or postgres-js, neon, etc.
import { JiraSync, runMigrations } from "jira-sync-engine";

const db = drizzle({ connection: process.env.DATABASE_URL });

// Run once to create the `jira` schema and tables
await runMigrations(db);

const sync = new JiraSync({
  jiraHost: "mycompany.atlassian.net",
  jiraEmail: "[email protected]",
  jiraApiToken: process.env.JIRA_API_TOKEN,
  db,
});

// Backfill issues updated in the last 7 days
const result = await sync.syncBackfill({
  jql: "updated >= -7d ORDER BY updated DESC",
});
console.log(result);
// { issues: { synced: 42, errors: 0 }, comments: { synced: 310, errors: 0 }, worklogs: { synced: 8, errors: 0 } }

await sync.close();

Configuration

type JiraSyncConfig = {
  jiraHost: string; // e.g. "mycompany.atlassian.net"
  jiraEmail: string;
  jiraApiToken: string;
  jiraWebhookSecret?: string; // required for webhook verification
  db?: PgAsyncDatabase; // your drizzle instance (any PostgreSQL driver)
  databaseUrl?: string; // fallback when `db` is not provided
  maxPostgresConnections?: number; // default: 10; ignored when `db` is provided
  backfillRelatedEntities?: boolean; // auto-upsert FK parents (users/projects), default: true
  customFieldMappings?: CustomFieldMapping[]; // see "Custom fields" below
  additionalJqlFields?: string[]; // extra Jira fields to fetch alongside mapped ones
  logger?: {
    debug: (obj: unknown, msg?: string) => void;
    error: (obj: unknown, msg?: string) => void;
  };
};

Webhooks

Register a webhook in Jira Cloud pointing to your endpoint, then call processWebhook with the raw request body and the X-Hub-Signature header value:

// Express example
app.post("/jira/webhook", express.raw({ type: "*/*" }), async (req, res) => {
  await sync.processWebhook(req.body, req.headers["x-hub-signature"] as string);
  res.sendStatus(200);
});

Supported events: jira:issue_created, jira:issue_updated, jira:issue_deleted, comment_created, comment_updated, comment_deleted, worklog_created, worklog_updated, worklog_deleted.

Backfill

// Sync all issues in a project
await sync.syncIssues({ jql: "project = MYPROJ ORDER BY updated DESC" });

// Sync comments and worklogs for specific issue IDs
await sync.syncComments(["10001", "10002"]);
await sync.syncWorklogs(["10001", "10002"]);

// Or sync everything at once (issues → then their comments and worklogs)
await sync.syncBackfill({ jql: "project = MYPROJ" });

Custom fields

Jira instances assign different numeric IDs to the same logical custom field (e.g. customfield_10031 on one instance might mean "urgency" and on another it means something else entirely). The customFieldMappings option lets each deployment declare its own mapping from Jira field IDs to stable column names in the issues table.

Built-in extractors

| Strategy | Extracts | | ------------- | ------------------------------------------------------ | | 'direct' | The raw field value as-is (strings, numbers, booleans) | | 'value' | .value from a Jira option object { value: "..." } | | 'accountId' | .accountId from a Jira user object |

For anything more complex, pass a function (raw: unknown) => unknown.

Example

import { JiraSync } from "jira-sync-engine";

const sync = new JiraSync({
  jiraHost: "mycompany.atlassian.net",
  jiraEmail: "[email protected]",
  jiraApiToken: process.env.JIRA_API_TOKEN,
  db,
  customFieldMappings: [
    // "impact" is a plain text field on this instance
    { jiraFieldId: "customfield_10108", column: "impact", extract: "direct" },
    // "urgency" and "systemName" are single-select option objects
    { jiraFieldId: "customfield_10031", column: "urgency", extract: "value" },
    { jiraFieldId: "customfield_10004", column: "systemName", extract: "value" },
    // "ownerId" is a user object — extract the account ID
    { jiraFieldId: "customfield_10213", column: "ownerId", extract: "accountId" },
    // Sprint data: use the reserved "_sprint" column name to trigger sprint table upserts
    { jiraFieldId: "customfield_10020", column: "_sprint", extract: (r) => r },
    // Organization data: use "_organizations" to trigger organization table upserts
    { jiraFieldId: "customfield_10002", column: "_organizations", extract: (r) => r },
  ],
});

A different Jira instance simply uses different jiraFieldId values — the column names and extraction logic stay the same across deployments.

Any custom field that is not covered by a mapping is still captured in the issues.custom_fields JSONB column, so no data is lost.

Reserved column names for relational side-effects

Two column names have special meaning and trigger additional table upserts alongside the issue row:

| Column | Side-effect | | ---------------- | ------------------------------------------------------------------------------------ | | _sprint | Upserts the sprint into jira.sprints and populates issues.sprint_id | | _organizations | Upserts organizations into jira.organizations and syncs jira.issue_organizations |

Built-in extended columns

The following columns on issues are always populated from standard Jira fields when present — no mapping required:

| Column | Source | | --------------------- | --------------------------------------------- | | parentKey | fields.parent.key | | isDone | fields.status.statusCategory.key === "done" | | statusCategoryColor | fields.status.statusCategory.colorName | | timeEstimate | fields.timeoriginalestimate (seconds) | | timeSpent | fields.timespent (seconds) | | description | fields.description (ADF stored as JSONB) |

Querying synced data

The Drizzle schema tables are exported so you can query with your own drizzle instance:

import {
  issues,
  comments,
  worklogs,
  sprints,
  organizations,
  issueOrganizations,
} from "jira-sync-engine";
import { eq } from "drizzle-orm";

// `db` is the same instance you passed to JiraSync / runMigrations
const openIssues = await db.select().from(issues).where(eq(issues.status, "In Progress"));

const issueComments = await db.select().from(comments).where(eq(comments.issueId, "10001"));

// Query using the stable canonical columns populated via customFieldMappings
const criticalIssues = await db.select().from(issues).where(eq(issues.urgency, "Critical"));

// Query sprint membership
const sprintIssues = await db
  .select()
  .from(issues)
  .innerJoin(sprints, eq(issues.sprintId, sprints.jiraId.toString()))
  .where(eq(sprints.state, "active"));

Schema

All tables live in the jira PostgreSQL schema.

| Table | Key columns | | --------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | issues | id, key (unique), summary, status, issueType, priority, assignee, reporter, projectKey, epicId, sprintId, labels, components, customFields (JSONB bag for unmapped fields), impact, urgency, systemName, orderNumber, billingTarget, billingStatus, reporterField, ownerId, timeEstimate, timeSpent, parentKey, isDone, statusCategoryColor, description, deleted, created, updated, lastSyncedAt | | comments | id, issueId, issueKey, author, authorAccountId, authorName, authorAvatar, body (JSONB), jsdPublic, deleted, created, updated, lastSyncedAt | | worklogs | id, issueId, author, timeSpentSeconds, comment, started, deleted, created, updated, lastSyncedAt | | users | accountId, displayName, email, avatarUrl, lastSyncedAt | | projects | id, key, name, projectType, lastSyncedAt | | sprints | jiraId, name, state, boardId | | organizations | jiraId, name | | issue_organizations | issueKey, organizationId (join table, cascade-deleted with issue) |

Soft-deleted records have deleted = true and are retained in the database.

Acknowledgements

Inspired by stripe-sync-engine by Supabase, which pioneered the pattern of syncing third-party API data into PostgreSQL via webhooks and batch backfill. Licensed under Apache 2.0.

Development

vp install   # install dependencies
vp test      # run tests
vp pack      # build
vp run demo  # run the demo script against a real Jira instance (requires .env)