UI Prototype Proposal 2.0: SQLite-Backed Order Fulfillment System

Executive Summary

Based on user feedback and analysis of the rea-06-sonnet45 reference implementation, this proposal refines the original three-pane UI prototype to demonstrate a cohesive e-commerce order fulfillment workflow using:

Frontend: Vanilla HTML/CSS/JS + datastar.dev (no React/Vue/Svelte)
Backend: Chi + Restate Go SDK + pithomlabs/rea framework
Persistence: SQLite for demonstration data (in addition to Restate durable state)
Business Process: Complete order workflow with Awakeables, Sagas, and compensations

Changes from PROPOSAL.md

Aspect	Original	Updated
Database	In-memory Restate state only	SQLite for user/product data + Restate for durable state
Business Logic	Generic CRUD	Order Fulfillment Workflow (ShippingService, UserSession, OrderFulfillmentWorkflow)
Scope	Abstract entity management	Concrete e-commerce scenario (cart → checkout → payment → fulfillment)
Patterns	Basic CRUD operations	Awakeables (payment), Sagas (compensations), Durable RPC
Real-time	Generic notifications	Order status updates, payment confirmations, shipment tracking

Business Process Overview

The Cohesive Workflow

┌─────────────────────────────────────────────────────────────┐
│                   USER JOURNEY                              │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  1. Browse Products (SQLite)                                │
│     ↓                                                       │
│  2. Add Items to Cart (UserSession Virtual Object)         │
│     ↓                                                       │
│  3. Initiate Checkout (UserSession.Checkout)               │
│     ├─ Create Payment Awakeable                            │
│     ├─ [SUSPEND] Wait for External Payment Gateway         │
│     └─ [RESUME] on Payment Resolution                      │
│        ↓                                                    │
│  4. Launch Order Fulfillment Workflow                      │
│     ├─ Admin Approval (Durable Promise)                    │
│     ├─ Initiate Shipping (ShippingService with retries)    │
│     ├─ Durable Sleep (wait for delivery)                   │
│     └─ Mark as Complete                                    │
│                                                             │
│  Compensation Stack (if failures)                          │
│     ├─ Cancel Shipping                                     │
│     └─ Release Inventory                                   │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Three Service Patterns (from rea-06-sonnet45)

1. ShippingService (Stateless Service)

Purpose: External integration with shipping providers
Pattern: Durable retry logic using rea.RunWithRetry()
ErrorHandling: Transient errors retry, business errors return TerminalError
Compensation: CancelShipment() method for rollback

Example:

func (ShippingService) InitiateShipment(ctx restate.Context, shipment ShipmentRequest) (bool, error) {
    cfg := rea.RunConfig{
        MaxRetries: 3,
        InitialDelay: 100 * time.Millisecond,
        BackoffFactor: 2.0,
    }
    
    success, err := rea.RunWithRetry(ctx, cfg, func(ctx restate.RunContext) (bool, error) {
        // Non-deterministic HTTP call to FedEx/UPS API
        return callShippingAPI(shipment)
    })
    
    if !success {
        return false, restate.TerminalError(fmt.Errorf("Shipping rejected"), 400)
    }
    return true, nil
}

2. UserSession (Virtual Object - Stateful Actor)

Purpose: Per-user shopping cart and checkout orchestration
State Isolation: Each user gets isolated state via restate.Key(ctx)
Idempotency: Explicit deduplication using state-based completion markers
Awakeables: Suspend execution waiting for external payment gateway

Example:

func (UserSession) Checkout(ctx restate.ObjectContext, orderID string) (bool, error) {
    userID := restate.Key(ctx)
    
    // Pattern C: Explicit State-Based Deduplication
    dedupKey := fmt.Sprintf("checkout:exec:%s", orderID)
    executed, err := restate.Get[bool](ctx, dedupKey)
    if executed {
        return true, nil // Already processed
    }
    
    // Create Awakeable for payment processing
    awakeable := restate.Awakeable[PaymentReceipt](ctx)
    
    // Send awakeable ID to external payment gateway
    restate.Run(ctx, func(ctx restate.RunContext) (bool, error) {
        return sendToPaymentGateway(awakeable.Id()), nil
    })
    
    // SUSPEND: Wait for payment gateway to resolve awakeable
    receipt, err := awakeable.Result()
    if err != nil {
        return false, restate.TerminalError(err, 500)
    }
    
    // Mark as executed for idempotency
    restate.Set(ctx, dedupKey, true)
    
    // Launch workflow asynchronously
    restate.WorkflowSend(ctx, "OrderFulfillmentWorkflow", orderID, "Run").Send(order)
    
    return true, nil
}

3. OrderFulfillmentWorkflow (Saga Pattern)

Purpose: Orchestrate multi-step order fulfillment with compensations
Compensation Stack: LIFO defer-based rollback on failures
Durable Promises: Human-in-the-loop approval steps
Durable RPC: Call ShippingService with automatic retry

Example:

func (OrderFulfillmentWorkflow) Run(ctx restate.WorkflowContext, order Order) error {
    // Register compensations in LIFO order
    defer func() {
        ctx.Log().Info("COMPENSATION: Inventory released")
    }()
    
    shippingCompensated := false
    defer func() {
        if shippingCompensated {
            restate.ServiceSend(ctx, "ShippingService", "CancelShipment").Send(order.OrderID)
        }
    }()
    
    // Wait for admin approval (Durable Promise)
    approval := restate.Promise[bool](ctx, "admin_approval")
    approved, err := approval.Result()
    if !approved {
        return restate.TerminalError(fmt.Errorf("Rejected"), 400)
    }
    
    // Call Shipping Service (Durable RPC)
    _, err = restate.Service[bool](ctx, "ShippingService", "InitiateShipment").Request(shipmentReq)
    if err != nil {
        return restate.TerminalError(err, 500) // Triggers compensations
    }
    shippingCompensated = true
    
    // Durable Sleep (wait for delivery)
    restate.Sleep(ctx, 5*time.Second)
    
    ctx.Log().Info("Order fulfilled successfully")
    return nil
}

SQLite Integration Strategy

Data Separation Philosophy

┌────────────────────────────────────────────────────────┐
│                    DATA LAYER                          │
├────────────────────────────────────────────────────────┤
│                                                        │
│  SQLite (Read-Heavy, Static Reference Data)           │
│  ├─ products (id, name, price, description)           │
│  ├─ users (id, name, email, address)                  │
│  └─ shipping_zones (id, zone, base_cost)              │
│                                                        │
│  Restate State (Durable, Transactional, Workflow)     │
│  ├─ UserSession.basket (per-user cart items)          │
│  ├─ UserSession.checkout_exec_{orderID} (dedup)       │
│  └─ WorkflowState (approval promises, step progress)  │
│                                                        │
└────────────────────────────────────────────────────────┘

Why SQLite?

Prototype Simplicity: Single-file database, no external dependencies
Reference Data: Products, users, and static config don't need durable execution
Query Flexibility: Complex joins for product catalogs
Familiar Tooling: Standard SQL for data seeding

Why NOT Restate for Everything?

Restate State is optimized for transactional, workflow-driven state
SQLite is optimized for reference data and read-heavy queries
Mixing both demonstrates real-world architectural patterns

Example SQLite Schema

-- Reference data for product catalog
CREATE TABLE products (
    id          INTEGER PRIMARY KEY,
    sku         TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    description TEXT,
    price_cents INTEGER NOT NULL,
    stock_qty   INTEGER DEFAULT 0
);

-- User accounts (in production, would be auth system)
CREATE TABLE users (
    id       INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email    TEXT NOT NULL,
    address  TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Shipping zones for cost calculation
CREATE TABLE shipping_zones (
    id         INTEGER PRIMARY KEY,
    zone_name  TEXT NOT NULL,
    base_cost_cents INTEGER NOT NULL
);

Data Flow Pattern

HTTP Request → Ingress Handler
                ↓
           Load user from SQLite (reference)
           Load product prices from SQLite
                ↓
           Call Restate Services (durable execution)
           UserSession.AddItem(productID)
                ↓
           Restate State: basket updated
                ↓
           HTTP Response (200 OK)

UI Layout with Concrete Business Context

Left Sidebar (Navigation)

┌────────────────┐
│ 🏠 Dashboard   │
│ 🛍️ Products     │  ← Browse SQLite catalog
│ 🛒 Cart         │  ← View UserSession.basket state
│ 📦 Orders       │  ← Workflow status list
│ ⚙️ Admin        │  ← Approve pending workflows
└────────────────┘

Middle Pane (Tabs)

Input Tab

Products Page: Form to add items to cart
Checkout Page: Initiate checkout with shipping address
Admin Approval Page: Approve/reject orders

Output Tab (Markdown)

Cart Summary: Markdown table of basket items with prices
Order Status: Step-by-step workflow progress
Shipment Tracking: Shipping service logs

Example Markdown Output:

# Order #order-123

**Status**: Awaiting Approval  
**User**: alice@example.com  
**Total**: $125.00

## Items
| SKU | Product | Qty | Price |
|-----|---------|-----|-------|
| P001 | Widget A | 2 | $50.00 |
| P002 | Widget B | 1 | $25.00 |

## Workflow Progress
- ✅ Payment Confirmed (Transaction: txn-456)
- ⏳ Awaiting Admin Approval
- ⬜ Shipping Initiation
- ⬜ Delivery Confirmation

Right Sidebar (Live Updates)

┌──────────────────────────────────┐
│ 🔔 Notifications                 │
├──────────────────────────────────┤
│ • Payment confirmed order-123    │
│ • Admin approval needed order-123│
│ • Shipment initiated order-456   │
│ • Order delivered order-789      │
└──────────────────────────────────┘

┌──────────────────────────────────┐
│ 📊 Workflow Metadata             │
├──────────────────────────────────┤
│ Current Step: Awaiting Approval  │
│ Elapsed Time: 2m 34s             │
│ Retry Count: 0                   │
│ Next Action: Admin Approve       │
└──────────────────────────────────┘

Technical Implementation Updates

Backend Structure

backend/
├── main.go                      (Chi server + Restate server)
├── db/
│   ├── sqlite.go                (SQLite connection pool)
│   ├── migrations/
│   │   └── 001_init.sql         (Schema creation)
│   └── seed.go                  (Sample data)
├── handlers/
│   ├── static.go                (Serve frontend files)
│   ├── sse.go                   (SSE streaming endpoints)
│   ├── products.go              (SQLite product queries)
│   └── orders.go                (Ingress → Restate calls)
├── services/
│   ├── shipping_service.go      (Restate Service)
│   ├── user_session.go          (Restate Virtual Object)
│   └── order_workflow.go        (Restate Workflow)
├── models/
│   ├── product.go               (SQLite models)
│   ├── user.go
│   ├── order.go                 (Restate models)
│   └── payment.go
└── middleware/
    ├── auth.go                  (Extract userID from header)
    └── idempotency.go           (Validate Idempotency-Key)

Key Endpoints

Method	Path	Purpose	Backend
GET	`/api/products`	List products	SQLite query
GET	`/api/products/{id}`	Product details	SQLite query
POST	`/api/cart/add`	Add to cart	Restate: UserSession.AddItem
GET	`/api/cart`	View cart	Restate: UserSession.GetBasket
POST	`/api/checkout`	Initiate checkout	Restate: UserSession.Checkout (Awakeable)
POST	`/api/payment/callback`	Payment gateway webhook	Resolve Awakeable → Resume checkout
POST	`/api/workflow/{id}/approve`	Admin approval	Resolve Workflow Promise
GET	`/stream/notifications`	SSE for real-time updates	Broadcast order events
GET	`/stream/workflow/{id}`	SSE for workflow progress	Stream workflow state changes

Datastar.dev Integration Examples

1. Product List (SSE Streaming)

HTML:

<div data-on-load="$get('/stream/products')">
    <div id="product-list">
        <div data-text="$product.name" data-for="product in $products"></div>
    </div>
</div>

Backend SSE:

func streamProducts(w http.ResponseWriter, r *http.Request) {
    w.Header().Set("Content-Type", "text/event-stream")
    
    // Query SQLite
    products, _ := db.QueryProducts()
    
    // Send datastar-merge event
    fmt.Fprintf(w, "event: datastar-merge\n")
    fmt.Fprintf(w, "data: {\"products\": %s}\n\n", jsonProducts)
    flusher.Flush()
}

2. Add to Cart (POST with SSE Response)

HTML:

<form data-on-submit="$post('/api/cart/add')">
    <input name="product_id" data-model="product_id" />
    <button type="submit">Add to Cart</button>
</form>

Backend:

func handleAddToCart(w http.ResponseWriter, r *http.Request) {
    userID := getUserIDFromContext(r)
    var req AddToCartRequest
    json.NewDecoder(r.Body).Decode(&req)
    
    // Call Restate Virtual Object
    restateClient := restateingress.NewClient("http://localhost:9080")
    _, err := restateingress.Object[AddItemRequest, bool](
        restateClient, "UserSession", userID, "AddItem",
    ).Request(r.Context(), req)
    
    // Return SSE update
    w.Header().Set("Content-Type", "text/event-stream")
    fmt.Fprintf(w, "event: datastar-merge\n")
    fmt.Fprintf(w, "data: {\"fragments\": [{\"selector\": \"#cart-count\", \"html\": \"3 items\"}]}\n\n")
}

3. Workflow Status Streaming

HTML:

<div data-on-load="$get('/stream/workflow/order-123')">
    <div id="workflow-status">
        <h3 data-text="$workflow.currentStep"></h3>
        <progress data-value="$workflow.progress" max="100"></progress>
    </div>
</div>

Backend SSE:

func streamWorkflowStatus(w http.ResponseWriter, r *http.Request) {
    orderID := chi.URLParam(r, "orderID")
    
    // Subscribe to workflow state changes
    for update := range workflowUpdates {
        if update.OrderID == orderID {
            fmt.Fprintf(w, "event: datastar-merge\n")
            fmt.Fprintf(w, "data: {\"workflow\": {\"currentStep\": \"%s\", \"progress\": %d}}\n\n",
                update.Step, update.Progress)
            flusher.Flush()
        }
    }
}

Verification Plan

Manual Testing Scenarios

Scenario 1: Happy Path (Complete Order)

Browse Products
- Navigate to Products page (left sidebar)
- View product list from SQLite
- Output tab shows markdown table
Add to Cart
- Input tab: Select product, enter quantity
- Click "Add to Cart"
- Right sidebar notification: "Item added"
- Output tab updates with cart summary
Checkout
- Input tab: Enter shipping address
- Click "Checkout"
- Awakeable created, payment gateway called
- Right sidebar: "Awaiting payment..."
- Input tab switches to "Payment Pending" view

Payment Resolution

Simulate payment gateway callback:

curl -X POST http://localhost:8080/api/payment/callback \
  -H "Content-Type: application/json" \
  -d '{"awakeable_id": "...", "transaction_id": "txn-123", "amount": 12500, "status": "success"}'

Right sidebar: "Payment confirmed!"
Workflow launched automatically

Admin Approval
- Admin navigates to Admin page (left sidebar)
- Sees pending approval in Output tab (markdown list)
- Input tab: Approve button
- Clicks "Approve"
- Workflow promise resolved
Shipping Initiation
- Workflow calls ShippingService
- rea.RunWithRetry handles external API call
- Right sidebar: "Shipment initiated with FedEx"
- Output tab shows tracking number
Delivery Confirmation
- Workflow sleeps 5 seconds (durable timer)
- Right sidebar: "Estimated delivery: 5s"
- Timer expires
- Right sidebar: "Order delivered!"
- Output tab: Final success message

Scenario 2: Payment Failure

Steps 1-3 same as Happy Path

Payment Rejection:

curl -X POST http://localhost:8080/api/payment/callback \
  -H "Content-Type: application/json" \
  -d '{"awakeable_id": "...", "transaction_id": "", "amount": 0, "status": "failed"}'

Expected:
- Awakeable rejects with error
- UserSession.Checkout returns TerminalError
- Right sidebar: "Payment failed: insufficient funds"
- Workflow NOT launched
- Cart state preserved (idempotency dedupKey not set)

Scenario 3: Admin Rejection (Compensation)

Steps 1-5 same as Happy Path
Admin Rejects:
- Admin clicks "Reject" button
- Workflow promise resolves with false
Expected:
- Workflow returns TerminalError
- Compensations run in LIFO order:
  1. Inventory released (deferred compensation)
- Right sidebar: "Order canceled by admin"
- Output tab explains cancellation reason

Scenario 4: Shipping Failure (Compensation)

Steps 1-6 same as Happy Path
Shipping API Fails:
- ShippingService receives FAIL_SHIP order ID
- External API returns rejection
Expected:
- rea.RunWithRetry exhausts retries
- ShippingService returns TerminalError
- Workflow receives error
- Compensations run:
  1. CancelShipment NOT called (guard: shippingCompensated == false)
  2. Inventory released
- Right sidebar: "Order failed: Shipping rejected"

Scenario 5: Idempotent Retry

First Checkout Attempt:
- User clicks "Checkout"
- Payment awakeable created
- Crashes before restate.Set(ctx, dedupKey, true)
Second Checkout Attempt (same order ID):
- User retries checkout
- restate.Get[bool](ctx, dedupKey) returns false (not executed)
- New payment awakeable created
- Payment succeeds
- dedupKey set to true
Third Attempt (idempotent):
- User retries again with same order ID
- restate.Get[bool](ctx, dedupKey) returns true
- Early return: "Already processed"
- No duplicate payment, no duplicate workflow launch

Automated Tests

# Backend unit tests
cd backend
go test ./services -v        # Test Restate service logic
go test ./handlers -v        # Test HTTP endpoints
go test ./db -v              # Test SQLite queries

# Integration tests
go test -tags=integration ./tests -v

# Load test (hey tool)
hey -n 1000 -c 10 -m POST http://localhost:8080/api/cart/add

Datastar.dev Testing

Open browser DevTools → Network tab
Filter for EventSource connections
Verify /stream/notifications and /stream/workflow/{id} are active
Trigger actions (add to cart, checkout)
Verify real-time updates appear without page refresh
Check datastar-merge events in Network tab

Summary of Improvements from PROPOSAL.md

Concrete Business Process: Replaced generic CRUD with cohesive order fulfillment workflow
SQLite Integration: Added persistent database for reference data (products, users)
Real Patterns from rea-06-sonnet45:
- Awakeables for payment suspension/resume
- Sagas with defer-based compensations
- Durable promises for human-in-the-loop
- State-based idempotency (Pattern C)
Enhanced UI Context: Left sidebar navigation, output tab shows workflow progress, right sidebar for live order updates
Comprehensive Testing: Added 5 manual test scenarios covering happy path, failures, and compensations

Open Questions

SQLite File Location: Should it be in /tmp, ./data, or embedded in binary?
Payment Gateway Simulation: Mock service or just curl commands for callbacks?
Admin UI: Separate admin portal or tabs within the same UI?
Notification Persistence: Store notifications in SQLite or only SSE streaming?

Next Steps

Review and approve PROPOSAL2.MD
Update implementation_plan.md with SQLite and workflow details
Begin implementation starting with:
- SQLite schema and seed data
- Restate services (ShippingService, UserSession, OrderWorkflow)
- Chi ingress handlers
- Frontend three-pane layout with datastar.dev

Author: Claude (Gemini Agent)
Date: 2025-12-02
Version: 2.0
Based On: rea-06-sonnet45

FilesExpand file tree

PROPOSAL2.md

Latest commit

History

PROPOSAL2.md

File metadata and controls

UI Prototype Proposal 2.0: SQLite-Backed Order Fulfillment System

Executive Summary

Changes from PROPOSAL.md

Business Process Overview

The Cohesive Workflow

Three Service Patterns (from rea-06-sonnet45)

1. ShippingService (Stateless Service)

2. UserSession (Virtual Object - Stateful Actor)

3. OrderFulfillmentWorkflow (Saga Pattern)

SQLite Integration Strategy

Data Separation Philosophy

Why SQLite?

Why NOT Restate for Everything?

Example SQLite Schema

Data Flow Pattern

UI Layout with Concrete Business Context

Left Sidebar (Navigation)

Middle Pane (Tabs)

Input Tab

Output Tab (Markdown)

Right Sidebar (Live Updates)

Technical Implementation Updates

Backend Structure

Key Endpoints

Datastar.dev Integration Examples

1. Product List (SSE Streaming)

2. Add to Cart (POST with SSE Response)

3. Workflow Status Streaming

Verification Plan

Manual Testing Scenarios

Scenario 1: Happy Path (Complete Order)

Scenario 2: Payment Failure

Scenario 3: Admin Rejection (Compensation)

Scenario 4: Shipping Failure (Compensation)

Scenario 5: Idempotent Retry

Automated Tests

Datastar.dev Testing

Summary of Improvements from PROPOSAL.md

Open Questions

Next Steps