TREC Lifecycle

Overview

TREC (Transaction Record) is a system-managed field that tracks the lifecycle state of every record in Q01 Core APIs. TREC values indicate whether a record is new, modified, cancelled (soft deleted), or in a special processing state.

TREC States:

N - New (just created, never modified)
M - Modified (updated at least once)
C - Cancelled (soft deleted, excluded from queries by default)
P - Processed (special workflow state, rarely used)

Key Principle: TREC is automatically managed by CoreWrite. Applications should never manually set TREC values.

State Definitions

N - New

Meaning: Record was just created and has never been updated

Set By: POST (create operation)

Characteristics:

✅ First state in lifecycle
✅ Indicates pristine record
✅ Visible in standard queries (TREC != 'C')
✅ Can transition to M (via PUT/PATCH) or C (via DELETE)

Automatic Fields Set:

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

Example:

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

# Response
{
  "data": {
    "PRD_ID": 123,
    "TREC": "N",              # ← Automatically set
    "CDATA": "20251219153045",
    "OWNER": "user@example.com",
    "XPRD01": "New Product",
    "XPRD02": 99.99
  }
}

M - Modified

Meaning: Record has been updated at least once since creation

Set By: PUT/PATCH (update operations)

Characteristics:

✅ Second state in lifecycle (after N)
✅ Indicates record has change history
✅ Visible in standard queries (TREC != 'C')
✅ Can transition to C (via DELETE) but never back to N

Automatic Fields Updated:

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

Example:

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

# Response
{
  "data": {
    "PRD_ID": 123,
    "TREC": "M",              # ← Changed from 'N' to 'M'
    "LDATA": "20251219160000",
    "LOWNER": "admin@example.com",
    "XPRD01": "New Product",
    "XPRD02": 89.99           # ← Updated
  }
}

C - Cancelled (Soft Delete)

Meaning: Record is soft deleted (logically deleted but physically present)

Set By: DELETE (without ?force=true parameter)

Characteristics:

✅ Terminal state for soft delete
✅ Record remains in database
✅ Excluded from standard queries (filtered by TREC != 'C')
✅ Can be restored by setting TREC='M' via PATCH
✅ Can be force deleted (physical removal) via DELETE?force=true

Automatic Fields Updated:

{
  "TREC": "C",
  "LDATA": "20251219170000",        // Deletion timestamp
  "LOWNER": "admin@example.com"     // Deleter
}

Example:

DELETE /api/v4/core/PRD/123

# Record updated with TREC='C', not physically deleted
{
  "message": "Record soft deleted",
  "record": {
    "PRD_ID": 123,
    "TREC": "C",              # ← Changed to 'C'
    "LDATA": "20251219170000",
    "LOWNER": "admin@example.com"
  }
}

Restore Example:

# Restore soft-deleted record by setting TREC='M'
PATCH /api/v4/core/PRD/123
{
  "data": {
    "TREC": "M"
  }
}

# Record is now active again
{
  "data": {
    "PRD_ID": 123,
    "TREC": "M",              # ← Restored
    "LDATA": "20251219180000"
  }
}

P - Processed

Meaning: Special workflow state for batch processing or custom workflows

Set By: Custom workflow logic (rarely used)

Characteristics:

✅ Indicates record is in processing state
✅ Application-specific meaning
✅ Visible in standard queries (TREC != 'C')
✅ Can transition to M or C

Example Use Cases:

Batch import processing (TREC='P' during import, 'M' when complete)
Approval workflows (TREC='P' while pending approval)
Queue processing (TREC='P' in queue, 'M' when processed)

Example:

# Custom workflow sets TREC='P'
PATCH /api/v4/core/ORD/456
{
  "data": {
    "TREC": "P",
    "XORD_STATUS": "processing"
  }
}

# After processing completes
PATCH /api/v4/core/ORD/456
{
  "data": {
    "TREC": "M",
    "XORD_STATUS": "completed"
  }
}

State Transitions

Complete State Machine

        POST (Create)
             ↓
        ┌────────┐
        │ TREC=N │ (New)
        └────────┘
             ↓
    PUT/PATCH (Update)
             ↓
        ┌────────┐
        │ TREC=M │ (Modified)
        └────────┘
             ↓
   DELETE (Soft Delete)
             ↓
        ┌────────┐
        │ TREC=C │ (Cancelled)
        └────────┘
             ↓
   DELETE?force=true
             ↓
     Physical Deletion
   (Record removed from DB)

Valid Transitions

From	To	Operation	Description
(none)	N	POST	Create new record
N	M	PUT/PATCH	First update
M	M	PUT/PATCH	Subsequent updates
N	C	DELETE	Soft delete new record
M	C	DELETE	Soft delete modified record
C	M	PATCH	Restore soft-deleted record
N	(deleted)	DELETE?force=true	Force delete new record
M	(deleted)	DELETE?force=true	Force delete modified record
C	(deleted)	DELETE?force=true	Force delete cancelled record
P	M	PATCH	Complete processing
N/M	P	PATCH	Enter processing state

Invalid Transitions

From	To	Why Invalid
M	N	Cannot "un-modify" a record
C	N	Cannot restore to "new" state
P	N	Cannot reset to "new" after processing

Automatic TREC Management

Creation (POST)

CoreWrite Logic:

public function insert(string $dimension, array $data): array {
  // System sets TREC automatically
  $data['TREC'] = 'N';
  $data['CDATA'] = date('YmdHis');
  $data['OWNER'] = $this->getAuthenticatedUser();

  // Insert record
  $this->pdo->insert($dimension, $data);
}

User Attempt to Override:

# ❌ BAD - Attempting to set TREC manually
POST /api/v4/core/PRD
{
  "data": {
    "TREC": "M",              # Ignored! System sets to 'N'
    "XPRD01": "Product"
  }
}

# Actual result: TREC='N' (system override)

Update (PUT/PATCH)

CoreWrite Logic:

public function update(string $dimension, string $id, array $data): array {
  // System updates TREC automatically
  $data['TREC'] = 'M';
  $data['LDATA'] = date('YmdHis');
  $data['LOWNER'] = $this->getAuthenticatedUser();

  // Update record
  $this->pdo->update($dimension, $id, $data);
}

User Attempt to Keep TREC='N':

# ❌ BAD - Attempting to prevent TREC update
PATCH /api/v4/core/PRD/123
{
  "data": {
    "TREC": "N",              # Ignored! System sets to 'M'
    "XPRD02": 89.99
  }
}

# Actual result: TREC='M' (system override)

Soft Delete (DELETE)

CoreWrite Logic:

public function delete(string $dimension, string $id, bool $force = false): void {
  if ($force) {
    // Physical deletion
    $this->pdo->delete($dimension, $id);
  } else {
    // Soft deletion - update TREC
    $this->pdo->update($dimension, $id, [
      'TREC' => 'C',
      'LDATA' => date('YmdHis'),
      'LOWNER' => $this->getAuthenticatedUser()
    ]);
  }
}

Soft vs Force Delete:

# Soft delete - TREC='C'
DELETE /api/v4/core/PRD/123

# Force delete - physically removed
DELETE /api/v4/core/PRD/123?force=true

Filtering by TREC

Default Behavior

Standard queries exclude cancelled records:

-- CoreQuery automatically adds TREC filter
SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'ecommerce'
  AND TREC != 'C'            -- ← Automatically added
LIMIT 100;

API Request:

GET /api/v4/core/PRD?$num_rows=100

# Returns only active records (TREC='N' or TREC='M')

Include Deleted Records

Explicitly filter for all TREC states:

GET /api/v4/core/PRD?filter[TREC][$in]=N,M,C

# Returns all records including soft-deleted (TREC='C')

Filter by Specific State

New records only:

GET /api/v4/core/PRD?filter[TREC]=N

Modified records only:

GET /api/v4/core/PRD?filter[TREC]=M

Cancelled records only (trash):

GET /api/v4/core/PRD?filter[TREC]=C

Active records (default):

GET /api/v4/core/PRD?filter[TREC][$ne]=C

# Equivalent to default behavior

Processing State

Records in processing:

GET /api/v4/core/ORD?filter[TREC]=P

Common Use Cases

Soft Delete with Trash

Implementation:

class ProductService {
  // Soft delete product
  async deleteProduct(productId) {
    await coreAPI.delete(`/api/v4/core/PRD/${productId}`);
    // TREC automatically set to 'C'
  }

  // Get trash (soft-deleted products)
  async getTrash() {
    return await coreAPI.get('/api/v4/core/PRD', {
      params: {
        filter: { TREC: 'C' }
      }
    });
  }

  // Restore from trash
  async restoreProduct(productId) {
    await coreAPI.patch(`/api/v4/core/PRD/${productId}`, {
      data: { TREC: 'M' }
    });
  }

  // Permanent delete
  async permanentDelete(productId) {
    await coreAPI.delete(`/api/v4/core/PRD/${productId}`, {
      params: { force: true }
    });
    // Record physically removed
  }
}

Audit Trail

Track record history:

class AuditService {
  async getRecordHistory(dimension, recordId) {
    const record = await coreAPI.get(`/api/v4/core/${dimension}/${recordId}`);

    return {
      created: {
        timestamp: record.data.CDATA,
        user: record.data.OWNER,
        state: 'N'
      },
      lastModified: record.data.TREC === 'M' ? {
        timestamp: record.data.LDATA,
        user: record.data.LOWNER,
        state: 'M'
      } : null,
      currentState: record.data.TREC
    };
  }
}

Change Detection

Find recently created records:

GET /api/v4/core/PRD?filter[TREC]=N&sort=-CDATA

Find recently modified records:

GET /api/v4/core/PRD?filter[TREC]=M&sort=-LDATA

Batch Processing

Process pending records:

class BatchProcessor {
  async processPendingOrders() {
    // Get orders in processing state
    const pending = await coreAPI.get('/api/v4/core/ORD', {
      params: {
        filter: { TREC: 'P', XORD_STATUS: 'pending' }
      }
    });

    for (const order of pending.data) {
      await this.processOrder(order);

      // Mark as processed
      await coreAPI.patch(`/api/v4/core/ORD/${order.ORD_ID}`, {
        data: {
          TREC: 'M',
          XORD_STATUS: 'completed'
        }
      });
    }
  }
}

Best Practices

✅ DO

Rely on automatic TREC management:

// ✅ Good - let system manage TREC
await coreAPI.post('/api/v4/core/PRD', {
  data: {
    XPRD01: 'Product',
    XPRD02: 99.99
    // TREC automatically set to 'N'
  }
});

Use soft delete by default:

// ✅ Good - reversible
await coreAPI.delete('/api/v4/core/PRD/123');

// Can be restored later
await coreAPI.patch('/api/v4/core/PRD/123', {
  data: { TREC: 'M' }
});

Filter out cancelled records explicitly when needed:

// ✅ Good - clear intent
const activeProducts = await coreAPI.get('/api/v4/core/PRD', {
  params: {
    filter: { TREC: { $ne: 'C' } }
  }
});

❌ DON'T

Don't manually set TREC on create:

// ❌ Bad - TREC will be overridden to 'N'
await coreAPI.post('/api/v4/core/PRD', {
  data: {
    TREC: 'M',        // Ignored
    XPRD01: 'Product'
  }
});

Don't try to prevent TREC='M' on update:

// ❌ Bad - TREC will always become 'M' on update
await coreAPI.patch('/api/v4/core/PRD/123', {
  data: {
    TREC: 'N',        // Ignored, will be 'M'
    XPRD02: 89.99
  }
});

Don't force delete without good reason:

// ❌ Bad - irreversible
await coreAPI.delete('/api/v4/core/PRD/123', {
  params: { force: true }
});

// ✅ Better - soft delete
await coreAPI.delete('/api/v4/core/PRD/123');

Troubleshooting

Issue 1: Expecting TREC='N' After Update

Symptom: Record has TREC='M' but I want it to be TREC='N'

Cause: Any update automatically sets TREC='M'

Solution: Accept that modified records have TREC='M' - this is correct behavior

Issue 2: Soft-Deleted Records Not Visible

Symptom: Record exists in database but not returned by API

Cause: Record has TREC='C' and is excluded from default queries

Solution:

# Include cancelled records in query
GET /api/v4/core/PRD?filter[TREC][$in]=N,M,C

Issue 3: Cannot Force Delete

Symptom: DELETE?force=true returns permission error

Cause: User lacks 'force_delete' grant

Solution: Use soft delete instead (DELETE without force parameter)

Issue 4: TREC Override Not Working

Symptom: Setting TREC manually has no effect

Cause: TREC is system-managed and cannot be manually controlled

Solution: Trust automatic TREC management - it ensures data integrity

Summary

✅ TREC tracks record lifecycle: N (new) → M (modified) → C (cancelled)
✅ System-managed - never set TREC manually
✅ Automatic transitions on POST (N), PUT/PATCH (M), DELETE (C)
✅ Soft delete default - TREC='C', record remains in database
✅ Force delete optional - physical removal with ?force=true
✅ Filter by TREC to include/exclude cancelled records

Key Takeaways:

TREC is automatic - trust the system
Soft delete (TREC='C') is reversible
Force delete is permanent - use sparingly
Default queries exclude TREC='C'
Filter by TREC for trash functionality
Never manually set TREC in create/update operations

Common Filters:

Active records: ?filter[TREC][$ne]=C (default)
New records: ?filter[TREC]=N
Modified records: ?filter[TREC]=M
Cancelled records: ?filter[TREC]=C
All records: ?filter[TREC][$in]=N,M,C

Entity Lifecycle - TREC concept explained
Write Patterns - Create, update, delete operations
Delete Operations - Soft vs force delete
Metadata Tables - TB_COST TREC configuration

TREC Lifecycle

Overview​

State Definitions​

N - New​

M - Modified​

C - Cancelled (Soft Delete)​

P - Processed​

State Transitions​

Complete State Machine​

Valid Transitions​

Invalid Transitions​

Automatic TREC Management​

Creation (POST)​

Update (PUT/PATCH)​

Soft Delete (DELETE)​

Filtering by TREC​

Default Behavior​

Include Deleted Records​

Filter by Specific State​

Processing State​

Common Use Cases​

Soft Delete with Trash​

Audit Trail​

Change Detection​

Batch Processing​

Best Practices​

✅ DO​

❌ DON'T​

Troubleshooting​

Issue 1: Expecting TREC='N' After Update​

Issue 2: Soft-Deleted Records Not Visible​

Issue 3: Cannot Force Delete​

Issue 4: TREC Override Not Working​

Summary​

Related Concepts​

Overview

State Definitions

N - New

M - Modified

C - Cancelled (Soft Delete)

P - Processed

State Transitions

Complete State Machine

Valid Transitions

Invalid Transitions

Automatic TREC Management

Creation (POST)

Update (PUT/PATCH)

Soft Delete (DELETE)

Filtering by TREC

Default Behavior

Include Deleted Records

Filter by Specific State

Processing State

Common Use Cases

Soft Delete with Trash

Audit Trail

Change Detection

Batch Processing

Best Practices

✅ DO

❌ DON'T

Troubleshooting

Issue 1: Expecting TREC='N' After Update

Issue 2: Soft-Deleted Records Not Visible

Issue 3: Cannot Force Delete

Issue 4: TREC Override Not Working

Summary

Related Concepts