Loganix Ops

 Order — QA Review Form

Schema Mapping

An order moves through several tables: core order data on orders, the active assignment on order_assignments, and related history tables for status, tags, messages, attachments, cancellation reasons, broadcast offers, and link-check QA.

Orders
spp_order_id←SPP reference
spp_line_item_id←SPP line item
client_email←Client email
is_managed←Managed?
service_type←Service type
niche←Niche
test_order←Test order
site_id←Target site
target_url←Link target
anchor_text←Anchor text
client_notes←Client notes
retail←Retail price
pay_url←Payment URL
content_url←Content doc
content_title←Content title
word_count←Word count
final_url←Final URL
status←Order status
risk_score←Risk score
risk_factors←Risk breakdown
risk_approved←Risk approved
ms_start_date←MS start
ms_accounting_code←MS accounting
started_at←Started
due_at←Due
Assignment
supplier_id←Assigned supplier
cost←Vendor cost
vendor_tat←Agreed TAT
vendor_stated_tat←Stated TAT
extension_hours←Extension
paid_at←Paid timestamp
pay_email←Pay email
pay_notes←Pay notes
rework_reason←Rework reason
cancellation_reason←Primary cancel reason
vendor_notes←Vendor notes
internal_notes←Internal notes
assigned_at←Assigned
accepted_at←Accepted
completed_at←Completed
rejected_at←Rejected
is_current←Active assignment
Reasons & Broadcasts
order_cancellation_reasons
supplier_order_broadcasts
History & Related
order_status_log
order_tags
order_messages
order_attachments
link_checks
ⓘ

QA Review Form — Not Connected to Database

This form represents every field the fulfillment team will edit or read on an order. Use it to verify every data point from the legacy Skynet dashboard has a home in the new orders / order_assignments schema. Compare against /schema for the full table details.

Order

Core order record + active assignment + related history. One order = one link placement (line item). Fulfillment moves it through the status workflow from new to complete.

1

Client & Intake

orders
The client who placed this order. Pulled from SPP on order sync.
orders.client_email — varchar(255), NOT NULL
Parent SPP order hash (8-char hex for normal orders, line-item id for LINKREQUEST).
orders.spp_order_id — varchar(31)
Per-link line item reference from SPP.
orders.spp_line_item_id — varchar(31)
What the client bought.
orders.service_type — varchar(50)
Content niche category. Drives pricing lookup.
orders.niche — varchar(30), default 'general'
Part of an ongoing managed campaign (vs. self-serve). If yes, fill Section 6 too.
orders.is_managed — boolean
Managed campaign order
Internal test order — excluded from analytics / billing reports.
orders.test_order — boolean
Test order
Special instructions from the client (topic preferences, restrictions, etc.).
orders.client_notes — text
2

Link Details

orders
The site we're placing the link on. Looked up from sites.site_id.
orders.site_id — integer FK → sites
Looked up / created in sites by domain.
Client's URL that should be linked to. This is the destination of the placed link.
orders.target_url — varchar(255)
Desired anchor text for the placed link.
orders.anchor_text — varchar(255)
Live URL where the link was placed. Populated once the supplier delivers.
orders.final_url — varchar(255)
3

Content

orders
Title of the content piece (for guest posts / content-included services).
orders.content_title — varchar(255)
Google Doc with the finished content, handed to the supplier.
orders.content_url — varchar(255)
Content length. Used for QA against site's stated minimum.
orders.word_count — integer
4

Pricing & Payment

orders
What the client paid us. (Vendor cost is tracked separately on the assignment.)
orders.retail — numeric(10,2)
$
Client-facing SPP payment link for this order.
orders.pay_url — varchar(255)
5

Risk & Approval

orders
Automated risk level (low / medium / high) from the RISK scorer. Orders scored high may be gated on risk_approved.
orders.risk_score — varchar(20)
JSON breakdown of what drove the score (niche, anchor, URL patterns, etc.). Structured; consumed by the dashboard.
orders.risk_factors — jsonb
Has a human cleared this risky order to proceed? High-risk orders are held until approved.
orders.risk_approved — boolean, default false
Approved by staff
6

Managed Service

orders

Only relevant when is_managed = true.

When the managed campaign started. Used for cohort and attribution reporting.
orders.ms_start_date — date
Internal accounting reference for managed-campaign billing.
orders.ms_accounting_code — varchar(255)
7

Status & Timeline

orders
Current state in the fulfillment workflow. Brackets were dropped in migration — values are plain strings (names unchanged).
orders.status — varchar(50), NOT NULL
When fulfillment began on this order.
orders.started_at — timestamptz
Delivery deadline. Used for overdue filtering and almost-overdue alerts.
orders.due_at — timestamptz
8

Current Assignment

order_assignments

The active supplier assignment for this order (is_current = true). Prior assignments stay in the table with is_current = false for history.

Supplier performing the work. Always looked up by email (with name); stored as supplier_id FK.
order_assignments.supplier_id — integer FK
State of this specific assignment (distinct from order-level status). Tracks the vendor's acceptance lifecycle.
order_assignments.status — varchar(20)
What we pay the supplier for this assignment. Margin = orders.retail - cost.
order_assignments.cost — numeric(10,2)
$
Turnaround time agreed for this assignment.
order_assignments.vendor_tat — smallint
What the vendor originally claimed their TAT was (for comparison with actual).
order_assignments.vendor_stated_tat — varchar(50)
Extra hours granted beyond TAT before penalty / reassignment kicks in.
order_assignments.extension_hours — smallint
When the supplier was paid for this assignment. Null = unpaid. Manually toggled by staff today; may auto-settle later.
order_assignments.paid_at — timestamptz
Leave blank until the supplier is paid.
Email the payment was sent to (PayPal / Payoneer / wire details). Captured at time of payment.
order_assignments.pay_email — varchar(255)
Freetext notes on the payment (reference number, batch, adjustments).
order_assignments.pay_notes — text
Why the work was sent back for rework (content quality, anchor mismatch, etc.). Denormalized “primary” reason; full history lives in order_cancellation_reasons.
order_assignments.rework_reason — varchar(255)
Short reason shown in the dashboard. Full multi-source detail (us / supplier / client) lives in Section 9.
order_assignments.cancellation_reason — varchar(255)
Supplier-facing notes passed along with the assignment.
order_assignments.vendor_notes — text
Staff-only notes, never shown to the supplier or client.
order_assignments.internal_notes — text
True for the current assignment. When we reassign, the old row's is_current flips to false (it stays in the table as history).
order_assignments.is_current — boolean
Current assignment
9

Cancellation / Rework Reasons

order_cancellation_reasons

Multi-source reason log. Captures why an order was canceled or sent to rework, from all three parties — us, the supplier, the client. One row per reason; an order can have several if multiple parties chime in.

Who issued this reason.
source — enum
Optional structured category for reporting (aggregation across orders). Freetext for now; we'll lock an enum as patterns emerge.
reason_category — varchar(50)
Required freetext explanation. Always fill this in even when a category is set.
reason — text, NOT NULL
Who logged this reason — user email for staff, supplier_id for vendor-submitted, or a system identifier.
created_by — varchar(255)
10

Supplier Broadcast Queue

supplier_order_broadcasts

When an order needs a fresh placement — either because the primary assignment failed, or because we want to open it up — we broadcast to every supplier that has the site in their inventory. First to accept wins. One row per supplier-offer.

A supplier who was invited to pick up this order.
supplier_id — integer FK
When this offer was actually sent (not the scheduled time).
broadcast_at — timestamptz
Current state of this specific offer.
response — enum
When the supplier replied, or when the offer was closed out.
responded_at — timestamptz
Leave blank for all-at-once broadcast. Set a number (1, 2, 3...) for staggered dispatch order — supplier #1 is contacted first; if they decline or time out, #2 is contacted, etc.
stagger_position — integer
Unguessable token (32 hex chars) auto-generated for one-click email links — vendors get https://portal/broadcast/{hash} to accept or decline without logging in. Unique per broadcast row, auto-populated on insert via gen_random_bytes.
access_hash — varchar(32), UNIQUE, NOT NULL
Auto-generated on insert — do not edit.
Optional context — why this supplier, or the supplier's freetext reason for denying.
notes — text
11

Related Tables (display-only)

various

These tables are populated by the system, not edited directly from this form. Shown here so the order view reflects every related data point.

Status Log order_status_log
Every status transition, with who made the change and when. Full audit trail for SLA and debugging.
order_id · assignment_id · from_status · to_status · changed_by · changed_at · note
Tags order_tags
Flexible labels on orders (rush, managed, lore, refund, etc.). Legacy denormalized string is migrated into rows.
order_id · tag · created_by · created_at
Messages order_messages
Back-and-forth between staff and the supplier on an order. Replaces SPP's order-message thread.
order_id · sender · body · sent_at · read_at
Attachments order_attachments
Files attached to the order or assignment — proof-of-placement uploads from suppliers, screenshots, email confirmations.
order_id · assignment_id · file_url · uploaded_by · uploaded_at
Link Checks link_checks
Automated QA runs from Atlas — verifies the final URL is live, anchor matches, dofollow, surrounding context. Separate entity; FK-bound by order_id.
order_id · checked_at · is_live · anchor_match · link_type · grade