Designer Assignment

Overview

The designer assignment system links designers to product tags via macrodata_artifact (kind: agent_assignment), then aggregates metrics per designer. This enables the Creative team to understand which designers’ work is generating the strongest sales performance.

Goal: Attribute sales metrics to individual designers via their assignment records in macrodata_artifact (kind: agent_assignment).

API endpoints

Designer assignments (specced)

Method	Path	Spec	Description
GET	/creative-ops/agent-assignments	creative-ops.yaml	List all designer-to-tag assignments (filterable by agent_name, tag_name)
POST	/creative-ops/agent-assignments	creative-ops.yaml	Create a designer assignment (returns 409 on duplicate)
DELETE	/creative-ops/agent-assignments/{assignment_id}	creative-ops.yaml	Remove (soft-archive) a designer assignment

Backing catalog endpoints (specced)

Method	Path	Spec	Description
GET	/macrodata-artifacts	macrodata.yaml	List artifacts (filter by kind=agent_assignment)
GET	/macrodata-artifacts/{artifact_id}	macrodata.yaml	Get a single artifact
GET	/tags	product.yaml	List available tags for assignment
GET	/analytics/commerce/products	analytics.yaml	Product performance data for attribution

Legacy endpoints (internal, being replaced by creative-ops spec)

Method	Path	Description
GET	/api/designers	All designers with assigned tags and aggregated metrics
GET	/api/designers/names	List of unique designer names
POST	/api/designers/assign	Create designer assignment (tag to designer)
DELETE	/api/designers/unassign	Remove a designer assignment
DELETE	/api/designers/:designerName	Delete designer and all their assignments

Schema tables

Table	Module	Role
macrodata_artifact (kind: agent_assignment)	Macrodata	CRUD: designer-to-tag assignment records
product	Product	Read: products linked to assigned tags
product_tag	Product	Read: tag-to-product links
measurement.sales (R2 Iceberg)	Analytics	Read: sales metrics for attribution

Infrastructure

Resource	Type	Purpose
dashboard-api	CF Worker (internal)	Designer assignment CRUD + metric queries
measurement.sales	R2 Iceberg table	Sales metric source for attribution
PlanetScale (Hyperdrive)	Managed Postgres	Assignment + product + tag data

Participants

Actor	Role
Staff user (Creative lead)	Assigns/unassigns tags to designers, reviews metrics
Dashboard API Worker	Serves designer assignment CRUD and metric queries
PlanetScale	Store for assignments, computes aggregates

Data flow

sequenceDiagram
    participant User as Creative Lead
    participant API as Dashboard API
    participant DB as PlanetScale

    User->>API: POST /creative-ops/agent-assignments
    API->>DB: INSERT macrodata_artifact (kind: agent_assignment)
    DB-->>API: { uid, agent_name, tag_name }
    API-->>User: Assignment created

    User->>API: GET /creative-ops/agent-assignments
    API->>DB: JOIN macrodata_artifact (agent_assignment) + product + measurement.sales (R2)
    DB-->>API: Designers with tags and metrics
    API-->>User: Aggregated designer performance

Designer list response DTO

interface DesignerWithMetrics {
  designerName: string;
  tags: Array<{
    tagName: string;
    tagId: string;
    assignedAt: string;
    totalUnits: number;       // Lifetime sales
    units30d: number;         // 30-day window
    productCount: number;     // Product count
  }>;
  totalUnits: number;         // Aggregated metrics
  units30d: number;
  totalProducts: number;
}

Metric computation

Designer performance metrics are computed by joining:

1. macrodata_artifact (kind: agent_assignment) — which tags belong to which designer
2. product — which products have those tags (via product_tag)
3. measurement.sales on R2 — sales metrics for those products

The aggregation sums totalUnits, units30d, and counts distinct products per tag, then rolls up to per-designer totals.

Database table

The macrodata_artifact table with kind: agent_assignment stores assignments. The spec JSONB column holds:

Field	Type	Constraint
agent_name	string	Designer name
tag_name	string	Tag identifier
assigned_at	ISO timestamp	Default: creation time
		UNIQUE (agent_name, tag_name) enforced at application level

Acceptance criteria

• Creating a designer assignment produces a unique (designer, tag) association
• Duplicate assignment (same designer + tag) is rejected gracefully (409)
• Designer list includes correct aggregated metrics from measurement.sales on R2
• Deleting a designer removes all their assignments
• Unassigning a tag updates the designer’s metric totals on next query