Write Operations (POST/PUT/PATCH/DELETE)

Overview

Write operations create, update, and delete data using POST, PUT, PATCH, and DELETE requests to CoreService. Requests are transparently routed to CoreWrite backend for transactional CRUD operations.

Key Principle:

POST/PUT/PATCH/DELETE requests to CoreService automatically route to CoreWrite. CoreWrite handles validation, pre-insert functions, cascades, TREC management, and outbox publishing - all in a single ACID transaction.

Operation Types

HTTP Method	Operation	Endpoint	TREC State
POST	Create new record	/api/v4/core/{DIM}	Sets TREC='N'
PUT	Full update (all fields)	/api/v4/core/{DIM}/{ID}	Sets TREC='M'
PATCH	Partial update (some fields)	/api/v4/core/{DIM}/{ID}	Sets TREC='M'
DELETE	Delete record (soft/hard)	/api/v4/core/{DIM}/{ID}	Sets TREC='C' or removes

CREATE Operation (POST)

Basic Endpoint

POST /api/v4/core/{DIM}

Request Format

Headers:

Authorization: Bearer {JWT_TOKEN}
Content-Type: application/json

Body:

{
  "XPRD01": "Premium Widget",
  "XPRD02": 49.99,
  "XPRD06": true,
  "source": "productCreate"
}

Required:

• source: Security area (must have grant level ≥ 4 for create)
• Fields marked as required in TB_COST

Example Request

curl -X POST "https://coreservice.q01.io/api/v4/core/PRD" \
  -H "Authorization: Bearer eyJhbGc..." \
  -H "Content-Type: application/json" \
  -d '{
    "XPRD01": "Premium Widget",
    "XPRD02": 49.99,
    "XPRD06": true,
    "XPRD05": "Electronics",
    "source": "productCreate"
  }'

Response Format

Success (201 Created):

[{
  "code": 201,
  "insertedId": "123",
  "body": {
    "XPRD01": "Premium Widget",
    "XPRD02": 49.99,
    "XPRD06": true,
    "XPRD05": "Electronics"
  },
  "fields": {
    "PRD_ID": {
      "type": "int",
      "value": "123"
    },
    "XPRD01": {
      "type": "varchar",
      "value": "Premium Widget"
    }
  }
}]

Response Fields:

• code: HTTP status code (201 for success)
• insertedId: Primary key value of created record
• body: Echo of request data
• fields: Field metadata from TB_COST

What Happens Internally

CoreWrite transaction flow:

1. Validate JWT & permissions (grant ≥ 4 for create)
2. Load metadata (TB_DIM, TB_COST)
3. Validate required fields
4. Execute pre-insert functions (uuid, slugify, current_user, etc.)
5. BEGIN TRANSACTION
6. INSERT into TB_ANAG_{DIM}00
   - Auto-populate: OWNER, CDATA, LOWNER, LDATA, TIMESTAMP, TREC='N'
7. Execute INSERT_CASCADE (create child records)
8. Write to TB_ANAG_OUTBOX00 (event: "create")
9. COMMIT TRANSACTION
10. Return insertedId + metadata

Guarantee: Either all steps succeed or all are rolled back (ACID).

Pre-Insert Functions

Fields with pre-insert functions are auto-populated:

TB_COST Configuration:

-- UUID field
INSERT INTO TB_COST (DIM_COD, COD_COST, FUNCTION, ...)
VALUES ('PRD', 'XPRD10', 'BI|uuid|microtime', ...);

-- Slug field
INSERT INTO TB_COST (DIM_COD, COD_COST, FUNCTION, ...)
VALUES ('PRD', 'XPRD11', 'BI|slugify|XPRD01', ...);

Request (minimal):

{
  "XPRD01": "Premium Widget",
  "source": "productCreate"
}

Result (auto-populated):

{
  "PRD_ID": "123",
  "XPRD01": "Premium Widget",
  "XPRD10": "A7F3C2B1-5D4E-5678-9ABC-DEF012345678",
  "XPRD11": "premium-widget",
  "OWNER": "user_5",
  "CDATA": "20250119120000",
  "TREC": "N"
}

Documentation: Pre-Insert Functions

Validation Errors

Missing Required Field:

curl -X POST "https://coreservice.q01.io/api/v4/core/PRD" \
  -H "Authorization: Bearer eyJhbGc..." \
  -d '{"XPRD02": 49.99, "source": "productCreate"}'
# Missing XPRD01 (required)

Response (400 Bad Request):

{
  "code": 400,
  "message": "Validation failed",
  "errors": [
    {
      "field": "XPRD01",
      "message": "Field is required"
    }
  ]
}

Insufficient Permissions:

# User has grant level 1, but create requires 4
curl -X POST "https://coreservice.q01.io/api/v4/core/PRD" \
  -H "Authorization: Bearer eyJhbGc..." \
  -d '{"XPRD01": "Widget", "source": "productCreate"}'

Response (403 Forbidden):

{
  "code": 403,
  "message": "Forbidden: insufficient grant level for create operation",
  "errors": [
    {
      "field": "source",
      "message": "Required grant: 4, User grant: 1"
    }
  ]
}

UPDATE Operation (PUT)

Full Update - Replace All Fields

Endpoint:

PUT /api/v4/core/{DIM}/{ID}

Purpose: Replace all modifiable fields (requires all field values)

Request:

curl -X PUT "https://coreservice.q01.io/api/v4/core/PRD/123" \
  -H "Authorization: Bearer eyJhbGc..." \
  -H "Content-Type: application/json" \
  -d '{
    "XPRD01": "Premium Widget Updated",
    "XPRD02": 59.99,
    "XPRD06": true,
    "XPRD05": "Electronics",
    "source": "productEdit"
  }'

Response (200 OK):

{
  "code": 200,
  "modifiedId": "123",
  "body": {
    "XPRD01": "Premium Widget Updated",
    "XPRD02": 59.99,
    "XPRD06": true,
    "XPRD05": "Electronics"
  }
}

Transaction Flow

1. Validate JWT & permissions (grant ≥ 2 for update)
2. Load existing record
3. Validate all required fields present
4. BEGIN TRANSACTION
5. UPDATE TB_ANAG_{DIM}00 SET
   - Fields from request body
   - LOWNER = current user
   - LDATA = current timestamp
   - TREC = 'M'
6. Execute UPDATE_CASCADE (update child foreign keys if needed)
7. Write to TB_ANAG_OUTBOX00 (event: "update")
8. COMMIT TRANSACTION
9. Return modifiedId

PARTIAL UPDATE Operation (PATCH)

Update Specific Fields Only

Endpoint:

PATCH /api/v4/core/{DIM}/{ID}

Purpose: Update only specified fields (don't need all fields)

Request:

curl -X PATCH "https://coreservice.q01.io/api/v4/core/PRD/123" \
  -H "Authorization: Bearer eyJhbGc..." \
  -H "Content-Type: application/json" \
  -d '{
    "XPRD02": 59.99,
    "source": "productEdit"
  }'

Only XPRD02 is updated, other fields unchanged.

Response (200 OK):

{
  "code": 200,
  "modifiedId": "123",
  "body": {
    "XPRD02": 59.99
  }
}

PUT vs PATCH

Aspect	PUT (Full Update)	PATCH (Partial Update)
Fields	All modifiable fields required	Only fields to change
Unspecified fields	Set to NULL or default	Keep existing values
Use case	Replace entire record	Change specific fields
Request size	Larger (all fields)	Smaller (only changes)

Example Comparison:

PUT Request:

{
  "XPRD01": "Widget",
  "XPRD02": 59.99,
  "XPRD06": true,
  "XPRD05": "Electronics",
  "XPRD04": "Updated description",
  "source": "productEdit"
}

Must include all fields or risk losing data.

PATCH Request:

{
  "XPRD02": 59.99,
  "source": "productEdit"
}

Only update price, keep everything else.

Recommendation: Use PATCH for most update operations (safer, more efficient).

DELETE Operation

Soft Delete (Default)

Endpoint:

DELETE /api/v4/core/{DIM}/{ID}

Purpose: Mark record as cancelled (TREC='C'), recoverable

Request:

curl -X DELETE "https://coreservice.q01.io/api/v4/core/PRD/123?source=productDelete" \
  -H "Authorization: Bearer eyJhbGc..."

Response (200 OK):

{
  "code": 200,
  "message": "Record soft deleted successfully",
  "deletedId": "123"
}

What Happens:

UPDATE TB_ANAG_PRD00 SET
  TREC = 'C',
  LOWNER = 'user_5',
  LDATA = '20250119140000'
WHERE PRD_ID = 123;

Record still exists but filtered out from default queries (TREC != 'C').

Documentation: Entity Lifecycle

Physical Delete (Force)

Endpoint:

DELETE /api/v4/core/{DIM}/{ID}?forceDelete=true

Purpose: Permanently remove record from database (irreversible)

Request:

curl -X DELETE "https://coreservice.q01.io/api/v4/core/PRD/123?source=productDelete&forceDelete=true" \
  -H "Authorization: Bearer eyJhbGc..."

Response (200 OK):

{
  "code": 200,
  "message": "Record permanently deleted",
  "deletedId": "123"
}

What Happens:

DELETE FROM TB_ANAG_PRD00 WHERE PRD_ID = 123;

Warning: Irreversible, no recovery, use sparingly.

Use Cases:

• Test data cleanup
• GDPR compliance (right to be forgotten)
• Data corruption fix

Delete with Cascades

If dimension has DELETE_CASCADE configured:

TB_COST Configuration:

-- Order dimension has cascade to order items
INSERT INTO TB_COST (DIM_COD, COD_COST, DELETE_CASCADE, ...)
VALUES ('ORD', 'ORD_ID', 'ORDITEM:XORDITEM01', ...);

Delete Order:

curl -X DELETE "https://coreservice.q01.io/api/v4/core/ORD/555?source=orderDelete" \
  -H "Authorization: Bearer eyJhbGc..."

Result:

-- Parent soft deleted
UPDATE TB_ANAG_ORD00 SET TREC = 'C' WHERE ORD_ID = 555;

-- Children soft deleted (cascaded)
UPDATE TB_ANAG_ORDITEM00 SET TREC = 'C' WHERE XORDITEM01 = 555;

Both parent and children marked TREC='C', recoverable as unit.

Documentation: Cascade Operations

Field Visibility and COD_ON_OFF

Creating Records (center_dett=nuovo)

Only fields with COD_ON_OFF containing 'N' can be set:

TB_COST:

INSERT INTO TB_COST (COD_COST, COD_ON_OFF, ...)
VALUES
  ('XPRD01', 'L,D,N,M,R', ...),  -- ✅ Can set on create (has N)
  ('XPRD02', 'L,D,N,M', ...),    -- ✅ Can set on create (has N)
  ('XPRD15', 'L,D,M', ...); -- ❌ Cannot set on create (no N)

Request:

{
  "XPRD01": "Widget",
  "XPRD02": 49.99,
  "XPRD15": "secret",  // ❌ Will be rejected
  "source": "productCreate"
}

Response (400 Bad Request):

{
  "code": 400,
  "message": "Validation failed",
  "errors": [
    {
      "field": "XPRD15",
      "message": "Field not visible in 'nuovo' context"
    }
  ]
}

Updating Records (center_dett=modifica)

Only fields with COD_ON_OFF containing 'M' can be updated:

Request:

curl -X PATCH "https://coreservice.q01.io/api/v4/core/PRD/123" \
  -d '{
    "XPRD02": 59.99,           // ✅ Has M in COD_ON_OFF
    "XPRD16": "test",  // ❌ Has only L,D (no M)
    "source": "productEdit"
  }'

Result: XPRD02 updated, XPRD16 rejected.

Documentation: Field Visibility

Outbox Pattern Integration

Every Write Operation Publishes Event

Create Event:

{
  "XOUTBOX04": "PRD",
  "XOUTBOX05": "create",
  "XOUTBOX06": "{\"PRD_ID\":\"123\",\"XPRD01\":\"Widget\"}",
  "XOUTBOX10": 0  // Pending publish
}

Update Event:

{
  "XOUTBOX04": "PRD",
  "XOUTBOX05": "update",
  "XOUTBOX06": "{\"PRD_ID\":\"123\",\"XPRD02\":59.99}",
  "XOUTBOX10": 0
}

Delete Event (Soft):

{
  "XOUTBOX04": "PRD",
  "XOUTBOX05": "logicalDelete",
  "XOUTBOX06": "{\"PRD_ID\":\"123\"}",
  "XOUTBOX10": 0
}

Relay Process:

1. Reads pending events (XOUTBOX10=0)
2. Publishes to RabbitMQ fanout exchange
3. Marks as published (XOUTBOX10=1)

Guarantee: At-least-once delivery to all subscribers.

Documentation: Outbox Pattern

Practical Examples

Example 1: Create Product with Validation

Scenario: Create product with auto-generated UUID and slug

async function createProduct(name, price, category) {
  const response = await fetch('/api/v4/core/PRD', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${token}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      XPRD01: name,
      XPRD02: price,
      XPRD05: category,
      XPRD06: true,
      source: 'productCreate'
    })
  });

  if (!response.ok) {
    const error = await response.json();
    throw new Error(error.message);
  }

  const result = await response.json();
  return result[0].insertedId;
}

// Usage
try {
  const productId = await createProduct('Premium Widget', 49.99, 'Electronics');
  console.log(`Product created with ID: ${productId}`);
} catch (error) {
  console.error('Failed to create product:', error.message);
}

Example 2: Update Product Price

Scenario: Update only price field

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

  if (!response.ok) {
    const error = await response.json();
    throw new Error(error.message);
  }

  return response.json();
}

// Usage
await updatePrice(123, 59.99);

Example 3: Soft Delete with Recovery

Delete:

curl -X DELETE "https://coreservice.q01.io/api/v4/core/PRD/123?source=productDelete" \
  -H "Authorization: Bearer eyJhbGc..."

Result: TREC='C', not visible in lists.

Recover (Undelete):

curl -X PATCH "https://coreservice.q01.io/api/v4/core/PRD/123" \
  -H "Authorization: Bearer eyJhbGc..." \
  -d '{"TREC": "M", "source": "productEdit"}'

Result: TREC='M', visible again.

Example 4: Bulk Create with Transaction

async function createProducts(products) {
  const results = [];

  for (const product of products) {
    const response = await fetch('/api/v4/core/PRD', {
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${token}`,
        'Content-Type': 'application/json'
      },
      body: JSON.stringify({
        ...product,
        source: 'productCreate'
      })
    });

    const result = await response.json();
    results.push(result[0].insertedId);
  }

  return results;
}

// Usage
const products = [
  { XPRD01: 'Widget A', XPRD02: 29.99 },
  { XPRD01: 'Widget B', XPRD02: 39.99 },
  { XPRD01: 'Widget C', XPRD02: 49.99 }
];

const ids = await createProducts(products);
console.log('Created products:', ids);

Note: Each request is independent transaction. For atomic bulk operations, use Batch Operations.

Error Handling

Common Error Patterns

1. Validation Error (400):

try {
  await fetch('/api/v4/core/PRD', {
    method: 'POST',
    body: JSON.stringify({ source: 'productCreate' })
    // Missing required XPRD01
  });
} catch (error) {
  // Handle: { code: 400, errors: [{field: "XPRD01", message: "..."}] }
}

2. Permission Error (403):

try {
  await fetch('/api/v4/core/PRD', {
    method: 'POST',
    body: JSON.stringify({ XPRD01: 'Widget', source: 'productAdmin' })
    // User lacks grant for productAdmin
  });
} catch (error) {
  // Handle: { code: 403, message: "Forbidden: insufficient permissions" }
}

3. Not Found (404):

try {
  await fetch('/api/v4/core/PRD/99999', {
    method: 'PATCH',
    body: JSON.stringify({ XPRD02: 59.99, source: 'productEdit' })
    // Record doesn't exist
  });
} catch (error) {
  // Handle: { code: 404, message: "Record not found" }
}

4. Conflict (409):

try {
  await fetch('/api/v4/core/PRD', {
    method: 'POST',
    body: JSON.stringify({
      XPRD03: 'WDG-2025-001',  // Unique code already exists
      source: 'productCreate'
    })
  });
} catch (error) {
  // Handle: { code: 409, message: "Duplicate key violation" }
}

Best Practices

✅ DO: Use PATCH for Updates

Preferred:

// Only send changed fields
await fetch(`/api/v4/core/PRD/${id}`, {
  method: 'PATCH',
  body: JSON.stringify({ XPRD02: 59.99, source: 'productEdit' })
});

Avoid:

// Must send all fields, risk losing data
await fetch(`/api/v4/core/PRD/${id}`, {
  method: 'PUT',
  body: JSON.stringify({ XPRD01: '...', XPRD02: 59.99, ... })
});

✅ DO: Use Soft Delete by Default

Preferred:

DELETE /api/v4/core/PRD/123?source=productDelete
# TREC='C', recoverable

Avoid:

DELETE /api/v4/core/PRD/123?source=productDelete&forceDelete=true
# Permanent deletion, irreversible

✅ DO: Validate Before Sending

Client-side validation:

function validateProduct(product) {
  const errors = [];

  if (!product.XPRD01 || product.XPRD01.trim() === '') {
    errors.push({ field: 'XPRD01', message: 'Name is required' });
  }

  if (!product.XPRD02 || product.XPRD02 <= 0) {
    errors.push({ field: 'XPRD02', message: 'Price must be positive' });
  }

  return errors;
}

const errors = validateProduct(product);
if (errors.length > 0) {
  // Show errors to user, don't send request
  return;
}

// Send request
await createProduct(product);

✅ DO: Handle Errors Gracefully

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

    if (!response.ok) {
      const error = await response.json();

      if (error.code === 400) {
        // Validation errors - show to user
        showValidationErrors(error.errors);
      } else if (error.code === 403) {
        // Permission error - show message
        showError('You do not have permission to create products');
      } else {
        // Generic error
        showError('Failed to create product. Please try again.');
      }

      return null;
    }

    const result = await response.json();
    return result[0].insertedId;
  } catch (error) {
    // Network error
    showError('Network error. Please check your connection.');
    return null;
  }
}

❌ DON'T: Send Fields Not Visible in Context

Wrong:

{
  "XPRD01": "Widget",
  "XPRD17": "secret",  // COD_ON_OFF='L,D' (no N)
  "source": "productCreate"
}

Right:

{
  "XPRD01": "Widget",
  "XPRD02": 49.99,  // Only fields with N in COD_ON_OFF
  "source": "productCreate"
}

❌ DON'T: Manually Set Audit Fields

Wrong:

{
  "XPRD01": "Widget",
  "OWNER": "user_99",  // Manual override
  "CDATA": "20250101000000",
  "TREC": "N",
  "source": "productCreate"
}

Why: OWNER, CDATA, LOWNER, LDATA, TREC are auto-populated by CoreWrite. Manual values ignored or cause errors.

Right:

{
  "XPRD01": "Widget",
  "XPRD02": 49.99,
  "source": "productCreate"
}

❌ DON'T: Use forceDelete Without Confirmation

Wrong:

// Deletes permanently without user confirmation
await fetch(`/api/v4/core/PRD/${id}?source=productDelete&forceDelete=true`, {
  method: 'DELETE'
});

Right:

// Confirm before permanent deletion
if (confirm('Permanently delete this product? This cannot be undone.')) {
  await fetch(`/api/v4/core/PRD/${id}?source=productDelete&forceDelete=true`, {
    method: 'DELETE'
  });
}

Summary

Write Operations provide:

1. ✅ Full CRUD capabilities - Create, update, delete with one request
2. ✅ Automatic field population - Pre-insert functions, audit fields, TREC
3. ✅ Transaction guarantees - ACID compliance, rollback on error
4. ✅ Cascade support - Automatic propagation to related records
5. ✅ Event publishing - Outbox pattern for eventual consistency
6. ✅ Multi-level validation - Permissions, required fields, COD_ON_OFF

Key Insight:

Write operations are more than database INSERTs/UPDATEs/DELETEs - they're orchestrated transactions that validate, transform, cascade, and publish events, all while maintaining ACID guarantees and audit trails.

Next Steps:

Continue to Field Metadata → to learn how to retrieve field definitions for dynamic forms.