144 lines
6.6 KiB
Python
144 lines
6.6 KiB
Python
"""
|
|
Database models.
|
|
|
|
One row per JIRA ticket. ShipStation is purely a label-generation step
|
|
for these tickets (order numbers there match the JIRA ticket number 1:1,
|
|
and it's not used for anything else), so it doesn't get its own rows -
|
|
it enriches the matching row here with tracking_numbers instead. See
|
|
app/workers.py for how that merge happens.
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
import datetime as dt
|
|
|
|
from sqlalchemy import (
|
|
Column,
|
|
Integer,
|
|
String,
|
|
Text,
|
|
DateTime,
|
|
Boolean,
|
|
JSON,
|
|
UniqueConstraint,
|
|
)
|
|
from sqlalchemy.orm import declarative_base
|
|
|
|
Base = declarative_base()
|
|
|
|
|
|
class Order(Base):
|
|
__tablename__ = "orders"
|
|
__table_args__ = (
|
|
UniqueConstraint("source", "external_id", name="uq_source_external_id"),
|
|
)
|
|
|
|
id = Column(Integer, primary_key=True)
|
|
|
|
# "source" is kept for schema stability / possible future sources,
|
|
# but in practice this is always "jira" today - see module docstring.
|
|
source = Column(String(32), nullable=False, index=True)
|
|
external_id = Column(String(128), nullable=False, index=True)
|
|
|
|
# The AR-###### style ticket number - same as external_id for JIRA
|
|
# rows, kept as its own column since it's also how ShipStation
|
|
# tracking numbers get matched back to this row.
|
|
ticket_number = Column(String(64), nullable=True, index=True)
|
|
|
|
# Signify Health / Oak Street Health / Unknown - derived from the
|
|
# SKU prefix. See app/companies.py.
|
|
company = Column(String(100), nullable=False, default="Unknown", index=True)
|
|
|
|
# SKUs found on this order/ticket. A ticket can have several, but
|
|
# per business rule they never mix companies on one ticket.
|
|
skus = Column(JSON, nullable=True)
|
|
|
|
# SKU + item name pairs, e.g. [{"sku": "OK704", "item_name": "Peripheral
|
|
# - Webcam"}]. Kept separately from `skus` (which stays just the bare
|
|
# codes, used for company resolution/search/display) because the
|
|
# ShipStation CSV template needs SKU and Item Name as distinct columns.
|
|
line_items = Column(JSON, nullable=True)
|
|
|
|
# Recipient info for shipping - name/phone/email/address/city/state/zip/npi.
|
|
# Only needed for the emergency "Send to ShipStation" action; not shown
|
|
# in the main table.
|
|
shipping_info = Column(JSON, nullable=True)
|
|
|
|
# Display name of whoever created the JIRA ticket.
|
|
creator = Column(String(200), nullable=True)
|
|
# Display name of whoever the ticket is assigned to (who's working it) -
|
|
# distinct from creator (who opened it).
|
|
assignee = Column(String(200), nullable=True)
|
|
|
|
# Plain-text version of the JIRA ticket's Description field (parsed
|
|
# from Atlassian Document Format - see app/adf.py). Mainly useful for
|
|
# the emailed-return-label workflow, where staff write the box
|
|
# requirements here rather than in a structured field.
|
|
description = Column(Text, nullable=True)
|
|
|
|
# Tracking numbers pulled from ShipStation and merged onto this
|
|
# ticket - e.g. [{"number": "782758401696", "carrier": "ups",
|
|
# "is_return": false}]. Populated by the "Pull Tracking Numbers"
|
|
# action, separately from the JIRA import.
|
|
tracking_numbers = Column(JSON, nullable=True)
|
|
# Service level used for the outbound label (e.g. "Priority Overnight",
|
|
# "Ground") - read off the ShipStation label during the same pull that
|
|
# gets tracking numbers, not something JIRA knows about.
|
|
shipping_method = Column(String(100), nullable=True)
|
|
|
|
# Freeform {label: value} pairs, e.g. {"Laptop Serial Number": "6NJLP54",
|
|
# "Laptop Asset Tag": "30882"} - entered by staff (often via barcode
|
|
# scanner) as devices are packed. Deliberately not fixed columns per
|
|
# device type: which fields are relevant varies by company and by kit,
|
|
# and hard-coding that would fight the "versatile" requirement. See
|
|
# app/serial_suggestions.py for how likely fields get suggested from
|
|
# the ticket's line items.
|
|
serial_numbers = Column(JSON, nullable=True)
|
|
# Staff-set "packed and ready to ship" flag - the Ship Sheet's "Done"
|
|
# column. Deliberately separate from JIRA's own status: a ticket can be
|
|
# packed=True while still sitting in JIRA as "Created", waiting on the
|
|
# (currently manual, eventually automated) end-of-day push that sets
|
|
# the real JIRA status.
|
|
packed = Column(Boolean, nullable=False, default=False)
|
|
packed_at = Column(DateTime, nullable=True)
|
|
|
|
summary = Column(String(500), nullable=False, default="")
|
|
status = Column(String(100), nullable=False, default="")
|
|
|
|
# When the order/ticket was created in the source system
|
|
source_created_at = Column(DateTime, nullable=True)
|
|
# When we pulled it into this app
|
|
imported_at = Column(DateTime, nullable=False, default=dt.datetime.now)
|
|
# When this ticket first transitioned into a fulfilled status - used
|
|
# for "fulfilled today" counts and as a rough audit trail. Set once,
|
|
# on the transition; not touched again while it stays fulfilled.
|
|
fulfilled_at = Column(DateTime, nullable=True)
|
|
# Same idea, for cancellation - also what limits the Cancelled tab to
|
|
# "cancelled today"; a ticket cancelled on a prior day falls through
|
|
# to Done instead of lingering on Cancelled indefinitely.
|
|
cancelled_at = Column(DateTime, nullable=True)
|
|
# When this ticket was successfully sent to ShipStation via the
|
|
# emergency "Send to ShipStation" action's API path specifically -
|
|
# confirmed by ShipStation's own response, not just attempted. The
|
|
# CSV export path intentionally does NOT set this: exporting a file
|
|
# isn't the same as it actually being imported, and this app has no
|
|
# way to confirm that manual step happened.
|
|
shipstation_sent_at = Column(DateTime, nullable=True)
|
|
# Label ID of the "dummy" outbound shipment created for the emailed-
|
|
# return-label workflow, persisted so it's verifiable (in ShipStation's
|
|
# own UI) as a separate step before creating the real return label
|
|
# from it - rather than both happening invisibly in one call.
|
|
dummy_outbound_label_id = Column(String(64), nullable=True)
|
|
|
|
# Legacy/unused - kept only because SQLite doesn't make dropping a
|
|
# column free and nothing reads this. Don't confuse with `packed`
|
|
# above (the real "Done" flag) or the JIRA-status-based fulfilled
|
|
# concept used for the Done tab/dashboard - this column predates both.
|
|
fulfilled = Column(Boolean, nullable=False, default=False)
|
|
|
|
# Full original payload from JIRA, for anything not modeled explicitly
|
|
# above.
|
|
raw_data = Column(JSON, nullable=True)
|
|
|
|
def __repr__(self) -> str: # pragma: no cover - debugging aid
|
|
return f"<Order {self.source}:{self.external_id} '{self.summary[:30]}'>"
|