Skip to main content

jira.psom

Daily snapshot of every PSOM Jira ticket, one row per ticket per snapshot.

Daily snapshot of all PSOM Jira Service Management tickets.

One row per Jira issue per snapshot. Each run writes a full snapshot of the tickets currently in the PSOM project to a new date= partition. Previous partitions are preserved as an audit trail, enabling point-in-time analysis. SLA clocks and comments are in their own tables, psom_sla and psom_comment, which join to this one on key and date.

Jira source: jtickets.atlassian.net, project PSOM, fetched via POST /rest/api/3/search/jql with full comment history via /rest/api/3/issue/{key}/comment.

Keywords: jira, psom, tickets, service requests, support, form topic

Provenance

Queried from PSOM Jira Cloud via API by the psom-sync Lambda function.

  • Updated: Daily, at ~06:00 ET
  • Retention: 2 years
  • Steward: EDCP Data and Research Team
  • Contact: Yinlin Zhang (Data and Research team)

Fields

One row per Jira issue per snapshot (date partition).

Field Name Type Description
date varchar Partition key: the date the Lambda ran (YYYY-MM-DD).
key varchar Jira ticket key, e.g. PSOM-42.
summary varchar One-line ticket title.
description varchar Full ticket description, extracted from Atlassian Document Format (ADF) to plain text.
issue_type varchar Jira issue type name, one of Email Request, Partner Support Request form.
status varchar Current workflow status, one of In progress, Waiting for customer, Escalated to PTT, Escalated to Auth PT, Done, Canceled, Ready to archive.
status_changed varchar ISO 8601 timestamp when the ticket’s status category last changed.
priority varchar Priority level, one of High, Normal, Low, Critical.
assignee varchar Display name of the current assignee; empty string if unassigned.
reporter varchar Display name of the person who reported the ticket.
creator varchar Display name of the person who created the ticket (may differ from reporter).
created varchar ISO 8601 timestamp when the ticket was created.
updated varchar ISO 8601 timestamp of the most recent field or comment update.
due_date int Jira due date. Unused.
labels varchar Semicolon-separated list of labels applied to the ticket.
request_type varchar JSM service request type name, e.g. New Access Request. Unused.
request_participants varchar Semicolon-separated display names of request participants (CC’d users).
organizations varchar Semicolon-separated names of JSM organizations the request is associated with.
form_topic varchar The ticket’s “Form topic” (a multi-select field), as a ;-separated list of the chosen options. Options are grouped under Already Using, Onboarding, and Escalated from Call Center. A ticket can carry more than one topic.
solution_provided varchar The ticket’s “Solution provided” checkbox: Yes, No, or Planned for future.
comment_count bigint Total number of comments on the ticket. Equals the ticket’s row count in psom_comment for the same date.
fetched_at varchar UTC timestamp when the Lambda wrote this row (YYYY-MM-DD HH:MM:SS).

Notes

All timestamp columns (created, updated, status_changed, fetched_at) are stored as varchar. The ISO 8601 ones carry an offset from UTC, e.g. 2026-09-25T10:31:43.801-0400.

Schema rework on 2026-09-28. The sla_first_response_* and sla_time_to_done_* columns moved to psom_sla, and the JSON comments column moved to psom_comment. The older partitions were rewritten to the new schema, so every partition has the same columns. form_topic and solution_provided were added at the same time. They are null in every partition before 2026-09-29, while new rows use an empty string when no option is chosen. Treat null and empty string alike.