quote
quote_item
quote_item_configuration_snapshot
quote_price_item
quote_state_transition
quote_approval_request

SubmitQuote
ApproveQuote
ConvertQuoteToOrder

Quote Worklist:
- quote number
- customer name
- sales owner
- status
- total recurring charge
- total one-time charge
- pending approver
- valid until
- age
- risk flag

1. Aggregate detail reads from normalized tables.
2. Worklist reads from dedicated projection tables.
3. Operational dashboard reads from summary/projection tables.
4. Timeline reads from state transition + audit + event projection.
5. Reporting/export reads from async snapshot/extract path.

{
  "items": [],
  "page": { "nextCursor": "..." },
  "meta": {
    "projectionAsOf": "2026-07-02T10:00:00Z",
    "freshness": "NEAR_REAL_TIME"
  }
}

GET /api/v1/quotes/{quoteId}
GET /api/v1/orders/{orderId}
GET /api/v1/assets/{assetId}

{
  "quoteId": "...",
  "quoteNumber": "Q-2026-000001",
  "status": "PENDING_APPROVAL",
  "revision": 3,
  "customer": {
    "customerId": "...",
    "displayName": "Acme Corp"
  },
  "items": [
    {
      "quoteItemId": "...",
      "productOfferingId": "...",
      "productName": "Enterprise Fiber 1Gbps",
      "configuration": {},
      "prices": []
    }
  ],
  "approval": {
    "required": true,
    "currentStep": "SALES_MANAGER"
  }
}

1. query quote header
2. query quote items
3. query configurations by item ids
4. query price items by item ids
5. assemble DTO

Pending Quote Approval Worklist

create table quote_worklist_projection (
  tenant_id uuid not null,
  quote_id uuid not null,
  quote_number text not null,
  customer_id uuid not null,
  customer_display_name text not null,
  sales_owner_id uuid,
  channel text,
  status text not null,
  revision int not null,
  total_onetime_amount numeric(19,4) not null,
  total_recurring_amount numeric(19,4) not null,
  currency text not null,
  approval_required boolean not null,
  current_approval_step text,
  valid_until timestamptz,
  created_at timestamptz not null,
  updated_at timestamptz not null,
  projection_version bigint not null,
  primary key (tenant_id, quote_id)
);

create index idx_quote_worklist_status_created
  on quote_worklist_projection (tenant_id, status, created_at desc, quote_id);

create index idx_quote_worklist_customer
  on quote_worklist_projection (tenant_id, customer_id, created_at desc, quote_id);

create table order_search_projection (
  tenant_id uuid not null,
  order_id uuid not null,
  order_number text not null,
  customer_id uuid not null,
  customer_display_name text not null,
  source_quote_id uuid,
  status text not null,
  fulfillment_status text not null,
  fallout_flag boolean not null,
  current_blocking_task_type text,
  current_blocking_task_id uuid,
  external_reference text,
  channel text,
  submitted_at timestamptz,
  completed_at timestamptz,
  updated_at timestamptz not null,
  projection_version bigint not null,
  primary key (tenant_id, order_id)
);

create index idx_order_search_status
  on order_search_projection (tenant_id, status, updated_at desc, order_id);

create index idx_order_search_fallout
  on order_search_projection (tenant_id, fallout_flag, updated_at desc, order_id)
  where fallout_flag = true;

create table fulfillment_exception_projection (
  tenant_id uuid not null,
  exception_id uuid not null,
  order_id uuid not null,
  order_number text not null,
  order_item_id uuid,
  task_id uuid not null,
  task_type text not null,
  task_status text not null,
  severity text not null,
  error_code text not null,
  error_message text,
  external_system text,
  retryable boolean not null,
  retry_count int not null,
  next_retry_at timestamptz,
  assigned_to text,
  first_failed_at timestamptz not null,
  last_failed_at timestamptz not null,
  primary key (tenant_id, exception_id)
);

create index idx_fulfillment_exception_open
  on fulfillment_exception_projection (tenant_id, severity, first_failed_at, exception_id)
  where task_status in ('FAILED', 'BLOCKED', 'INCIDENT');

{
  "exceptionId": "...",
  "orderNumber": "O-2026-000123",
  "taskType": "PROVISION_SERVICE",
  "severity": "HIGH",
  "errorCode": "PROVISIONING_TIMEOUT",
  "retryable": true,
  "allowedActions": ["RETRY", "ASSIGN", "MARK_MANUAL", "CANCEL_ORDER"]
}

create table customer_timeline_event (
  tenant_id uuid not null,
  timeline_event_id uuid not null,
  customer_id uuid not null,
  occurred_at timestamptz not null,
  event_type text not null,
  entity_type text not null,
  entity_id uuid not null,
  entity_number text,
  title text not null,
  summary text,
  severity text,
  actor_id text,
  correlation_id text,
  payload jsonb not null,
  primary key (tenant_id, timeline_event_id)
);

create index idx_customer_timeline
  on customer_timeline_event (tenant_id, customer_id, occurred_at desc, timeline_event_id);

{
  "title": "Quote Q-2026-000123 submitted for approval",
  "summary": "Total MRC USD 12,000.00 requires Sales Director approval",
  "entityType": "QUOTE",
  "entityId": "...",
  "occurredAt": "2026-07-02T10:00:00Z"
}

create table audit_record (
  audit_id uuid primary key,
  tenant_id uuid not null,
  actor_id text,
  actor_type text not null,
  action text not null,
  aggregate_type text not null,
  aggregate_id uuid not null,
  previous_state text,
  new_state text,
  reason text,
  before_value jsonb,
  after_value jsonb,
  correlation_id text,
  request_id text,
  occurred_at timestamptz not null
);

create index idx_audit_entity_time
  on audit_record (tenant_id, aggregate_type, aggregate_id, occurred_at desc);

create index idx_audit_actor_time
  on audit_record (tenant_id, actor_id, occurred_at desc);

create index idx_audit_action_time
  on audit_record (tenant_id, action, occurred_at desc);

GET /api/v1/audit-records?aggregateType=QUOTE&aggregateId=...&cursor=...
GET /api/v1/audit-records?action=PRICE_OVERRIDE_APPROVED&from=...&to=...

Quotes Pending Approval: 128
Orders In Progress: 2,341
Orders in Fallout: 47
Outbox Pending: 901
Oldest Outbox Pending: 18 minutes
Provisioning Failure Rate: 3.1%
Average Order Completion Time: 4h 12m

create table operational_metric_snapshot (
  tenant_id uuid not null,
  metric_name text not null,
  metric_window text not null,
  dimension_key text not null,
  dimension_value text not null,
  metric_value numeric(19,4) not null,
  calculated_at timestamptz not null,
  primary key (tenant_id, metric_name, metric_window, dimension_key, dimension_value)
);

create table order_operations_summary (
  tenant_id uuid primary key,
  in_progress_count bigint not null,
  fallout_count bigint not null,
  blocked_count bigint not null,
  completed_today_count bigint not null,
  average_completion_seconds numeric(19,4),
  oldest_open_order_at timestamptz,
  calculated_at timestamptz not null
);

GET /api/v1/orders?from=2024-01-01&to=2026-07-02&pageSize=1000000

POST /api/v1/reports/order-fulfillment/export-requests

{
  "exportRequestId": "...",
  "status": "ACCEPTED",
  "statusUrl": "/api/v1/reports/export-requests/..."
}

GET /api/v1/orders?status=IN_PROGRESS&fallout=true&sort=updatedAt:desc&pageSize=50

{
  "items": [
    {
      "orderId": "...",
      "orderNumber": "O-2026-000123",
      "customer": {
        "customerId": "...",
        "displayName": "Acme Corp"
      },
      "status": "IN_PROGRESS",
      "fulfillmentStatus": "BLOCKED",
      "fallout": true,
      "currentBlockingTask": {
        "taskId": "...",
        "taskType": "PROVISION_SERVICE"
      },
      "updatedAt": "2026-07-02T10:00:00Z"
    }
  ],
  "page": {
    "nextCursor": "eyJ1cGRhdGVkQXQiOiIuLi4ifQ==",
    "pageSize": 50
  },
  "meta": {
    "projectionAsOf": "2026-07-02T10:00:10Z"
  }
}

OrderSearchFilters:
  type: object
  properties:
    status:
      type: array
      items:
        type: string
    fulfillmentStatus:
      type: array
      items:
        type: string
    customerId:
      type: string
      format: uuid
    fallout:
      type: boolean
    updatedFrom:
      type: string
      format: date-time
    updatedTo:
      type: string
      format: date-time

limit 50 offset 100000;

updated_at desc, order_id desc

select *
from order_search_projection
where tenant_id = #{tenantId}
  and status = #{status}
order by updated_at desc, order_id desc
limit #{pageSizePlusOne};

select *
from order_search_projection
where tenant_id = #{tenantId}
  and status = #{status}
  and (
    updated_at < #{cursorUpdatedAt}
    or (updated_at = #{cursorUpdatedAt} and order_id < #{cursorOrderId})
  )
order by updated_at desc, order_id desc
limit #{pageSizePlusOne};

{
  "sort": "updatedAt:desc,orderId:desc",
  "updatedAt": "2026-07-02T10:00:00Z",
  "orderId": "..."
}

sort=anyColumn:desc

select quote_id, quote_number, customer_display_name, status, total_recurring_amount, valid_until
from quote_worklist_projection
where tenant_id = #{tenantId}
  and status = any(#{statuses})
  and created_at >= #{createdFrom}
  and created_at < #{createdTo}
order by created_at desc, quote_id desc
limit #{limit};

create index idx_quote_worklist_status_created
  on quote_worklist_projection (tenant_id, status, created_at desc, quote_id desc);

create index idx_quote_worklist_customer_created
  on quote_worklist_projection (tenant_id, customer_id, created_at desc, quote_id desc);

alter table quote_worklist_projection
  add column search_document tsvector;

create index idx_quote_worklist_search
  on quote_worklist_projection
  using gin (search_document);

update quote_worklist_projection
set search_document =
  to_tsvector('simple',
    coalesce(quote_number, '') || ' ' ||
    coalesce(customer_display_name, '') || ' ' ||
    coalesce(channel, '')
  );

select quote_id, quote_number, customer_display_name
from quote_worklist_projection
where tenant_id = #{tenantId}
  and search_document @@ plainto_tsquery('simple', #{query})
order by updated_at desc
limit #{limit};

BEGIN
  update quote
  insert audit
  insert outbox
  upsert quote_worklist_projection
COMMIT

create table projection_processed_event (
  projection_name text not null,
  event_id uuid not null,
  processed_at timestamptz not null,
  primary key (projection_name, event_id)
);

BEGIN
  insert processed_event
  if duplicate -> no-op
  apply projection upsert
COMMIT

insert into order_search_projection (
  tenant_id,
  order_id,
  order_number,
  customer_id,
  customer_display_name,
  status,
  fulfillment_status,
  fallout_flag,
  updated_at,
  projection_version
)
values (...)
on conflict (tenant_id, order_id)
do update set
  status = excluded.status,
  fulfillment_status = excluded.fulfillment_status,
  fallout_flag = excluded.fallout_flag,
  updated_at = excluded.updated_at,
  projection_version = order_search_projection.projection_version + 1;

where excluded.aggregate_version > order_search_projection.aggregate_version

aggregate_version bigint not null

{
  "aggregateId": "...",
  "aggregateVersion": 12,
  "eventType": "OrderStateChanged"
}

update order_search_projection
set status = #{status},
    aggregate_version = #{aggregateVersion},
    updated_at = #{updatedAt}
where tenant_id = #{tenantId}
  and order_id = #{orderId}
  and aggregate_version < #{aggregateVersion};

{
  "meta": {
    "consistency": "PROJECTION",
    "projectionAsOf": "2026-07-02T10:00:00Z",
    "source": "order_search_projection"
  }
}

where tenant_id = #{tenantId}

sales_owner_id text,
sales_team_id text,
region_id text,
channel text,
product_family text

public final class OrderSearchAccessPolicy {
    public OrderSearchCriteria applyScope(Actor actor, OrderSearchCriteria criteria) {
        if (actor.hasRole("ORDER_OPERATIONS_ADMIN")) return criteria;
        if (actor.hasRole("REGION_OPERATOR")) return criteria.withRegion(actor.regionId());
        throw new ForbiddenException("ORDER_SEARCH_FORBIDDEN");
    }
}

public interface OrderSearchQueryMapper {
    List<OrderSearchRow> searchOrders(OrderSearchSqlCriteria criteria);
}

<select id="searchOrders" resultMap="OrderSearchRowMap">
  select
    order_id,
    order_number,
    customer_id,
    customer_display_name,
    status,
    fulfillment_status,
    fallout_flag,
    current_blocking_task_type,
    current_blocking_task_id,
    updated_at
  from order_search_projection
  where tenant_id = #{tenantId}
  <if test="statuses != null and statuses.size() > 0">
    and status in
    <foreach item="status" collection="statuses" open="(" separator="," close=")">
      #{status}
    </foreach>
  </if>
  <if test="fallout != null">
    and fallout_flag = #{fallout}
  </if>
  <if test="customerId != null">
    and customer_id = #{customerId}
  </if>
  <if test="cursorUpdatedAt != null">
    and (
      updated_at &lt; #{cursorUpdatedAt}
      or (updated_at = #{cursorUpdatedAt} and order_id &lt; #{cursorOrderId})
    )
  </if>
  order by updated_at desc, order_id desc
  limit #{limit}
</select>

Load order
For each item: load task
For each task: load external attempt
For each item: load asset impact

Load order header
Load all items by order_id
Load all tasks by order_id
Load all external attempts by task_ids
Load all asset impacts by order_id
Assemble in memory

OrderHeaderRow selectOrderHeader(...);
List<OrderItemRow> selectOrderItems(...);
List<FulfillmentTaskRow> selectTasksByOrder(...);
List<ExternalAttemptRow> selectAttemptsByTaskIds(...);

{
  "orderId": "...",
  "status": "IN_PROGRESS",
  "fulfillmentStatus": "BLOCKED",
  "blockingReason": {
    "code": "TASK_FAILED",
    "message": "Provisioning service task failed after 3 retries",
    "taskId": "...",
    "taskType": "PROVISION_SERVICE",
    "retryable": true,
    "nextActions": ["RETRY_TASK", "MARK_MANUAL", "CANCEL_ORDER"]
  },
  "items": [],
  "tasks": [],
  "timeline": []
}

select o.order_id
from orders o
left join order_search_projection p
  on p.tenant_id = o.tenant_id
 and p.order_id = o.order_id
where p.order_id is null
   or p.status <> o.status
   or p.updated_at < o.updated_at;

1. detect drift
2. log drift record
3. rebuild projection row from source tables
4. emit repair audit/event optional
5. update metric

refresh materialized view concurrently order_daily_summary_mv;

{
  "meta": {
    "readSource": "REPLICA",
    "replicaLagMillis": 1200
  }
}

10 tenants
5 million quotes
2 million orders
40 million order items
100 million fulfillment tasks
200 million audit records
50 million timeline events

GET /api/v1/quotes/{quoteId}
GET /api/v1/quotes
GET /api/v1/quotes/{quoteId}/timeline

GET /api/v1/orders/{orderId}
GET /api/v1/orders
GET /api/v1/orders/{orderId}/fulfillment-tasks
GET /api/v1/orders/{orderId}/timeline

GET /api/v1/customers/{customerId}/timeline
GET /api/v1/assets
GET /api/v1/subscriptions

GET /api/v1/fulfillment/exceptions
GET /api/v1/audit-records
GET /api/v1/operations/dashboard
GET /api/v1/operations/outbox
GET /api/v1/operations/inbox

{
  "items": [
    {
      "order": {
        "items": [...],
        "tasks": [...],
        "audit": [...],
        "configurationSnapshots": [...]
      }
    }
  ]
}

{
  "items": [
    {
      "orderId": "...",
      "orderNumber": "O-2026-000123",
      "customerDisplayName": "Acme Corp",
      "status": "IN_PROGRESS",
      "fulfillmentStatus": "BLOCKED",
      "fallout": true,
      "updatedAt": "2026-07-02T10:00:00Z"
    }
  ]
}

customerId in 5,000 accounts

create table actor_customer_scope (
  tenant_id uuid not null,
  actor_id text not null,
  customer_id uuid not null,
  primary key (tenant_id, actor_id, customer_id)
);

select p.*
from order_search_projection p
join actor_customer_scope s
  on s.tenant_id = p.tenant_id
 and s.customer_id = p.customer_id
where s.actor_id = #{actorId}
  and p.status = #{status}
order by p.updated_at desc
limit #{limit};

quote search:
  maxPageSize = 100
  defaultPageSize = 25
  maxDateRange = 180 days unless admin export
  allowedSort = createdAt, updatedAt, validUntil

Given order projection row
When GET /orders
Then response matches OpenAPI schema

Given orders with statuses A/B/C
When filter status=B
Then only B returned

Given 101 orders sorted by updatedAt desc
When page size 50
Then no duplicate across pages
And no missing row

Given actor scoped to region A
When search orders
Then region B rows are not returned

Given event processed once
When same event processed again
Then projection unchanged

Given projection aggregateVersion=10
When event version=9 arrives
Then projection not downgraded

explain analyze
select ...

cpq_query_duration_ms{endpoint,query_type}
cpq_query_result_count{endpoint}
cpq_query_timeout_total{endpoint}
cpq_query_rejected_total{endpoint,reason}
cpq_projection_lag_seconds{projection_name}
cpq_projection_drift_count{projection_name}
cpq_projection_rebuild_total{projection_name}
cpq_dashboard_calculation_duration_ms{dashboard}

{
  "event": "SLOW_QUERY",
  "endpoint": "/api/v1/orders",
  "queryType": "ORDER_SEARCH",
  "tenantId": "...",
  "durationMs": 1280,
  "pageSize": 100,
  "filters": ["status", "fallout"],
  "sort": "updatedAt:desc",
  "correlationId": "..."
}

{
  "event": "PROJECTION_UPDATED",
  "projectionName": "order_search_projection",
  "eventType": "OrderStateChanged",
  "aggregateId": "...",
  "aggregateVersion": 12,
  "durationMs": 8
}

Detail shows order completed, search still shows in progress.

Projection status differs from source table permanently.

Order search P95 grows from 300 ms to 4 s.

User sees quote/order outside allowed scope.

Duplicate/missing rows across pages.

query-model/
  quote-worklist/
  order-search/
  fulfillment-exception/
  customer-timeline/
  audit-explorer/
  operations-dashboard/

quote_worklist_projection
order_search_projection
fulfillment_exception_projection
customer_timeline_event
projection_processed_event
operational_metric_snapshot

quote-query-api
quote-query-application
quote-query-persistence-mybatis
order-query-api
order-query-application
order-query-persistence-mybatis
operations-query-api
projection-updater-worker

[ ] Siapa user endpoint ini?
[ ] Keputusan apa yang akan dibuat dari data ini?
[ ] Apakah butuh strong consistency atau projection cukup?
[ ] Apakah response list atau detail?
[ ] Apa filter yang diizinkan?
[ ] Apa sort yang diizinkan?
[ ] Apa index yang mendukung filter/sort?
[ ] Apakah pagination cursor atau offset?
[ ] Apa max page size?
[ ] Apa authorization row-scope?
[ ] Apakah response perlu freshness metadata?
[ ] Apakah query bisa menjadi report besar?
[ ] Jika ya, apakah harus async export?
[ ] Apa metric latency dan timeout?
[ ] Apa reconciliation path kalau projection drift?