Database Design for Microservices

-- Order Service Database Schema
-- Stores order data with external references

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,  -- Reference to Customer Service
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10,2) NOT NULL,
    shipping_address TEXT NOT NULL
);

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id),
    book_id INTEGER NOT NULL,  -- Reference to Catalog Service
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    book_title VARCHAR(255),   -- Cached from Catalog Service
    book_author VARCHAR(255)   -- Cached from Catalog Service
);

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);

-- Order Service needs customer data
-- But customer table is in Customer Service
-- Solution: API call, then store the order

INSERT INTO orders (
    customer_id,
    total_amount,
    shipping_address
) VALUES (
    12345,
    89.99,
    'Retrieved via Customer Service API'
);

-- Cached book data in Order Service
-- Updated when Catalog Service publishes changes

CREATE TABLE book_cache (
    book_id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_name VARCHAR(255),
    current_price DECIMAL(10,2),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Use cached data for order creation
INSERT INTO order_items (
    order_id, book_id, quantity, unit_price, book_title
) 
SELECT 1, 101, 2, current_price, title
FROM book_cache 
WHERE book_id = 101;

-- Event log table for Order Service
-- Tracks all events this service publishes

CREATE TABLE order_events (
    event_id SERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,  -- 'order_created', 'order_shipped'
    order_id INTEGER NOT NULL,
    event_data JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    published BOOLEAN DEFAULT FALSE
);

-- Example: Order created event
INSERT INTO order_events (event_type, order_id, event_data)
VALUES (
    'order_created',
    1001,
    '{"customer_id": 12345, "total": 89.99, "items": [{"book_id": 101, "qty": 2}]}'
);

-- Saga orchestration table in Order Service
-- Tracks multi-step transaction progress

CREATE TABLE order_saga (
    saga_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL,
    current_step VARCHAR(50) NOT NULL,
    saga_status VARCHAR(20) DEFAULT 'in_progress',
    steps_completed JSONB DEFAULT '[]',
    compensation_needed JSONB DEFAULT '[]',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Example saga record for order processing
INSERT INTO order_saga (order_id, current_step, steps_completed)
VALUES (
    1001,
    'inventory_reserved',
    '["order_created", "inventory_reserved"]'
);

-- Compensation tracking when saga fails
-- Records what needs to be undone

UPDATE order_saga 
SET 
    saga_status = 'compensating',
    compensation_needed = '[
        {"action": "release_inventory", "book_id": 101, "quantity": 2},
        {"action": "cancel_order", "order_id": 1001}
    ]'
WHERE order_id = 1001;

-- Cancel the order
UPDATE orders 
SET status = 'cancelled' 
WHERE order_id = 1001;

-- Conflict resolution with version numbers
-- Each update increments the version

ALTER TABLE book_cache 
ADD COLUMN version_number INTEGER DEFAULT 1,
ADD COLUMN source_service VARCHAR(50) DEFAULT 'catalog';

-- Update only if version is newer
UPDATE book_cache 
SET 
    current_price = 24.99,
    version_number = version_number + 1,
    last_updated = CURRENT_TIMESTAMP
WHERE 
    book_id = 101 
    AND version_number < 5;  -- Only if our version is older