Write Patterns

Overview

Write operations modify data through CoreService API endpoints routed internally to CoreWrite backend. All writes are metadata-driven, validated, transactional, and event-sourced.

Write Operations:

POST - Create new records (TREC='N')
PUT - Full update existing records (TREC='M')
PATCH - Partial update specific fields (TREC='M')
DELETE - Soft or force delete records (TREC='C' or physical removal)

Write Flow Architecture

Request Flow

Client Application
    ↓
POST /api/v4/core/PRD
    ↓
CoreService (API Gateway)
    ↓ [Routes to WRITE_API]
    ↓
CoreWrite (PHP/Symfony)
    ↓
1. Token validation
2. Grant validation
3. Required field validation
4. COD_ON_OFF validation
5. Pre-insert functions
6. Database write (ACID transaction)
7. Outbox event (RabbitMQ)
    ↓
Response + Event published

Internal Routing

CoreService automatically routes write requests:

// In CoreService routingController.go
func RoutingDefault(c buffalo.Context) error {
  routeName := c.Value("current_route").(buffalo.RouteInfo).PathName

  switch routeName {
  case "coreCreatePath":   // POST
    return routingPost(c, "WRITE_API")
  case "coreUpdatePath":   // PUT
    return routingPut(c, "WRITE_API")
  case "corePatchPath":    // PATCH
    return routingPatch(c, "WRITE_API")
  case "coreDeletePath":   // DELETE
    return routingDelete(c, "WRITE_API")
  }
}

User perspective: Single API endpoint with different HTTP methods.

TREC Lifecycle

Record States

TREC (Transaction Record) tracks lifecycle:

State	Meaning	Set By	Description
N	New	POST	Record created
M	Modified	PUT/PATCH	Record updated
C	Cancelled	DELETE (soft)	Soft deleted
P	Processed	Special	Custom state (rare)

State Transitions

POST (Create)
    ↓
  TREC='N' (New)
    ↓
PUT/PATCH (Update)
    ↓
  TREC='M' (Modified)
    ↓
DELETE?force=false (Soft Delete)
    ↓
  TREC='C' (Cancelled)

DELETE?force=true (Force Delete)
    ↓
  Record physically removed

Audit Fields

Automatically managed on writes:

On CREATE (POST):

{
  "TREC": "N",
  "CDATA": "20251219153045",  // Create timestamp
  "OWNER": "user@example.com" // Creator
}

On UPDATE (PUT/PATCH):

{
  "TREC": "M",
  "LDATA": "20251219160000",  // Last update timestamp
  "LOWNER": "admin@example.com" // Last modifier
}

On DELETE (soft):

{
  "TREC": "C",
  "LDATA": "20251219170000",
  "LOWNER": "admin@example.com"
}

Write Operations Summary

POST - Create Record

Endpoint:

POST /api/v4/core/{dim}
Content-Type: application/json

Behavior:

Validates required fields
Executes pre-insert functions (uuid, counter, md5, etc.)
Sets TREC='N', CDATA, OWNER
Executes INSERT_CASCADE operations
Publishes creation event to outbox

Example:

POST /api/v4/core/PRD
{
  "data": {
    "XPRD01": "New Product",
    "XPRD02": 99.99,
    "XPRD05": "cat_electronics"
  }
}

PUT - Full Update

Endpoint:

PUT /api/v4/core/{dim}/{dim_id}
Content-Type: application/json

Behavior:

Updates ALL fields in request body
Sets TREC='M', LDATA, LOWNER
Executes UPDATE_CASCADE operations
Publishes update event to outbox

Example:

PUT /api/v4/core/PRD/123
{
  "data": {
    "XPRD01": "Updated Product Name",
    "XPRD02": 109.99,
    "XPRD05": "cat_electronics",
    "XPRD06": true
  }
}

PATCH - Partial Update

Endpoint:

PATCH /api/v4/core/{dim}/{dim_id}
Content-Type: application/json

Behavior:

Updates ONLY specified fields
Other fields unchanged
Sets TREC='M', LDATA, LOWNER
Executes UPDATE_CASCADE operations
Publishes update event to outbox

Example:

PATCH /api/v4/core/PRD/123
{
  "data": {
    "XPRD02": 89.99  // Only update price
  }
}

DELETE - Soft or Force Delete

Soft Delete (default):

DELETE /api/v4/core/PRD/123

Behavior:

Sets TREC='C', LDATA, LOWNER
Record remains in database (soft deleted)
Excluded from queries by default
Executes DELETE_CASCADE (soft) operations
Publishes deletion event to outbox

Force Delete:

DELETE /api/v4/core/PRD/123?force=true

Behavior:

Physically removes record from database
Irreversible operation
Executes DELETE_CASCADE (force) operations
Publishes deletion event to outbox

Multi-Level Validation

Validation Layers

1. Token Validation

Authorization: Bearer <JWT_TOKEN>
↓
Validates token signature, expiration, issuer

2. Grant Validation

User grants checked via TB_MENU nested set
↓
Verifies user has permission for operation on dimension

3. Required Field Validation

TB_COST.REQUIRED='1' fields checked
↓
Ensures all mandatory fields present

4. COD_ON_OFF Validation

TB_COST.COD_ON_OFF flags checked
↓
N flag required for POST, M flag for PUT/PATCH

5. Business Logic Validation

Custom validation rules (if defined)
↓
Application-specific constraints

Validation Example

// POST request validation flow
POST /api/v4/core/PRD
{
  "data": {
    "XPRD01": "Product Name"
    // Missing XPRD05 (required field)
  }
}

// Validation error response
{
  "error": "ValidationError",
  "message": "Required field missing: XPRD05",
  "code": "REQUIRED_FIELD_MISSING",
  "field": "XPRD05"
}

Pre-Insert Functions

Automatic Field Population

TB_COST.VALUE_TEMP defines pre-insert functions:

Function	Purpose	Example
`{uuid}`	Generate UUID	`550e8400-e29b-41d4-a716-446655440000`
`{md5}`	Hash field value	`5d41402abc4b2a76b9719d911017c592`
`{counter}`	Sequential counter	`PRD-2025-001`
`{sequence}`	Database sequence	`12345`
`{slugify}`	URL-friendly slug	`product-name`
`{timestamp}`	Current timestamp	`20251219153045`

Metadata Example:

-- XPRD03 auto-generates formatted counter
INSERT INTO TB_COST (COD_DIM, NUM_COST, COD_VAR, VALUE_TEMP) VALUES
('PRD', 3, 'XPRD03', '{counter:PRD-{YYYY}-{NNN}}');

Result:

POST /api/v4/core/PRD
{
  "data": {
    "XPRD01": "New Product"
    // XPRD03 auto-populated: "PRD-2025-001"
  }
}

Cascade Operations

Metadata-Driven Cascades

TB_COST cascade fields trigger automatic operations:

INSERT_CASCADE - Create related records on POST
UPDATE_CASCADE - Update related records on PUT/PATCH
DELETE_CASCADE - Delete related records on DELETE

Example:

-- Create order items when order is created
INSERT INTO TB_COST (COD_DIM, NUM_COST, INSERT_CASCADE) VALUES
('ORD', 10, 'ORDITEM:XORDITEM_ORDER_ID');

Behavior:

POST /api/v4/core/ORD
{
  "data": {
    "XORD01": "ORD-2025-001",
    "items": [
      {"XORDITEM01": "Product A", "XORDITEM02": 2},
      {"XORDITEM01": "Product B", "XORDITEM02": 1}
    ]
  }
}

# Automatically creates:
# 1. TB_ANAG_ORD00 record (order)
# 2. TB_ANAG_ORDITEM00 records (2 items)

Outbox Pattern

Event Sourcing

Every write operation publishes event to RabbitMQ:

Write Operation (POST/PUT/PATCH/DELETE)
    ↓
1. Insert to TB_ANAG_{DIM}00 (data table)
2. Insert to OUTBOX table (write-ahead log)
3. Commit transaction (ACID)
    ↓
Background process publishes to RabbitMQ
    ↓
Event consumed by subscribers

Event Structure:

{
  "eventType": "ProductCreated",
  "aggregateId": "123",
  "dimension": "PRD",
  "operation": "POST",
  "timestamp": "2025-12-19T15:30:45Z",
  "payload": {
    "PRD_ID": 123,
    "XPRD01": "New Product",
    "XPRD02": 99.99
  },
  "user": "user@example.com"
}

Benefits:

At-least-once delivery guarantee
Eventual consistency across microservices
Event replay for debugging
Audit trail

Transaction Management

ACID Guarantees

All write operations are transactional:

// CoreWrite DataStore.php
public function insert(string $dimension, array $data): array {
  $this->pdo->beginTransaction();

  try {
    // 1. Insert main record
    $recordId = $this->insertRecord($dimension, $data);

    // 2. Execute cascades
    $this->executeCascades($dimension, $recordId, $data);

    // 3. Insert outbox event
    $this->insertOutboxEvent($dimension, $recordId, 'INSERT', $data);

    // 4. Commit transaction
    $this->pdo->commit();

    return ['id' => $recordId, 'status' => 'success'];
  } catch (\Exception $e) {
    // Rollback on any error
    $this->pdo->rollBack();
    throw $e;
  }
}

Atomicity:

All operations succeed or all fail
No partial writes
Database consistency maintained

Write Patterns by Use Case

Pattern 1: Simple Create

Single record creation:

async function createProduct(productData) {
  const response = await fetch(`${apiBase}/api/v4/core/PRD`, {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${token}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({ data: productData })
  });

  return response.json();
}

Pattern 2: Create with Cascades

Order with items (master-detail):

async function createOrder(orderData) {
  const response = await fetch(`${apiBase}/api/v4/core/ORD`, {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${token}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      data: {
        XORD01: 'ORD-2025-001',
        XORD_CUSTOMER_ID: 'cust_123',
        items: [
          { XORDITEM10: 'prd_1', XORDITEM_QTY: 2 },
          { XORDITEM10: 'prd_2', XORDITEM_QTY: 1 }
        ]
      }
    })
  });

  return response.json();
}

Pattern 3: Partial Update

Update single field:

async function updateProductPrice(productId, newPrice) {
  const response = await fetch(`${apiBase}/api/v4/core/PRD/${productId}`, {
    method: 'PATCH',
    headers: {
      'Authorization': `Bearer ${token}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      data: { XPRD02: newPrice }
    })
  });

  return response.json();
}

Pattern 4: Soft Delete with Undo

Trash functionality:

async function deleteProduct(productId) {
  // Soft delete
  await fetch(`${apiBase}/api/v4/core/PRD/${productId}`, {
    method: 'DELETE',
    headers: { 'Authorization': `Bearer ${token}` }
  });
}

async function restoreProduct(productId) {
  // Restore by setting TREC='M'
  await fetch(`${apiBase}/api/v4/core/PRD/${productId}`, {
    method: 'PATCH',
    headers: {
      'Authorization': `Bearer ${token}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      data: { TREC: 'M' }
    })
  });
}

Best Practices

✅ DO:

Use POST for creation:

// ✅ Good
POST /api/v4/core/PRD

Use PATCH for partial updates:

// ✅ Good - only update price
PATCH /api/v4/core/PRD/123
{ "data": { "XPRD02": 89.99 } }

Use soft delete by default:

// ✅ Good - can be restored
DELETE /api/v4/core/PRD/123

Handle validation errors:

try {
  await createProduct(data);
} catch (error) {
  if (error.code === 'REQUIRED_FIELD_MISSING') {
    console.error('Missing required field:', error.field);
  }
}

❌ DON'T:

Don't use PUT for partial updates:

// ❌ Bad - overwrites all fields
PUT /api/v4/core/PRD/123
{ "data": { "XPRD02": 89.99 } }  // Other fields cleared!

// ✅ Good
PATCH /api/v4/core/PRD/123
{ "data": { "XPRD02": 89.99 } }

Don't manipulate TREC manually:

// ❌ Bad - TREC managed automatically
POST /api/v4/core/PRD
{ "data": { "TREC": "N", ... } }

// ✅ Good - let system manage TREC
POST /api/v4/core/PRD
{ "data": { "XPRD01": "Product", ... } }

Don't force delete without reason:

// ❌ Bad - irreversible
DELETE /api/v4/core/PRD/123?force=true

// ✅ Good - soft delete (reversible)
DELETE /api/v4/core/PRD/123

Summary

✅ Write operations route to CoreWrite via CoreService
✅ POST creates (TREC='N'), PUT updates (TREC='M'), DELETE soft-deletes (TREC='C')
✅ Multi-level validation: token, grant, required fields, COD_ON_OFF
✅ Pre-insert functions auto-populate fields (uuid, counter, md5, etc.)
✅ Cascades propagate operations to related dimensions
✅ Outbox pattern ensures event sourcing and eventual consistency
✅ ACID transactions guarantee data integrity

Key Takeaways:

Use PATCH for partial updates, PUT for full replacement
Soft delete (TREC='C') by default, force delete only when necessary
Trust automatic field management (TREC, CDATA, OWNER, counters)
Validation happens at multiple layers before write
Every write publishes event to RabbitMQ

Next Topics:

Create Operations → - POST in detail
Update Operations → - PUT lifecycle
Partial Updates → - PATCH patterns
Delete Operations → - Soft vs. force delete
Validation → - Validation layers explained
Cascade Operations → - Automatic propagation
Outbox Pattern → - Event sourcing details

TREC Filtering - Query by lifecycle state
Entity Lifecycle - TREC state machine
Field Visibility - COD_ON_OFF validation
Dimensions - Dimension structure

Write Patterns

Overview​

Write Flow Architecture​

Request Flow​

Internal Routing​

TREC Lifecycle​

Record States​

State Transitions​

Audit Fields​

Write Operations Summary​

POST - Create Record​

PUT - Full Update​

PATCH - Partial Update​

DELETE - Soft or Force Delete​

Multi-Level Validation​

Validation Layers​

Validation Example​

Pre-Insert Functions​

Automatic Field Population​

Cascade Operations​

Metadata-Driven Cascades​

Outbox Pattern​

Event Sourcing​

Transaction Management​

ACID Guarantees​

Write Patterns by Use Case​

Pattern 1: Simple Create​

Pattern 2: Create with Cascades​

Pattern 3: Partial Update​

Pattern 4: Soft Delete with Undo​

Best Practices​

✅ DO:​

❌ DON'T:​

Summary​

Related Concepts​

Overview

Write Flow Architecture

Request Flow

Internal Routing

TREC Lifecycle

Record States

State Transitions

Audit Fields

Write Operations Summary

POST - Create Record

PUT - Full Update

PATCH - Partial Update

DELETE - Soft or Force Delete

Multi-Level Validation

Validation Layers

Validation Example

Pre-Insert Functions

Automatic Field Population

Cascade Operations

Metadata-Driven Cascades

Outbox Pattern

Event Sourcing

Transaction Management

ACID Guarantees

Write Patterns by Use Case

Pattern 1: Simple Create

Pattern 2: Create with Cascades

Pattern 3: Partial Update

Pattern 4: Soft Delete with Undo

Best Practices

✅ DO:

❌ DON'T:

Summary

Related Concepts