Version & Lifecycle Management

Version 1 (Master_ID: 100, Current_ID: 100, Last_ID: null, Next_ID: 201)
    ↓
Version 2 (Master_ID: 100, Current_ID: 105, Last_ID: 200, Next_ID: 202)
    ↓
Version 3 (Master_ID: 100, Current_ID: 110, Last_ID: 201, Next_ID: null, IsCurrent: 1)

SELECT Live FROM aurora_tables WHERE table_database_name = 'your_table_name'

-- Live = 'True'  → Live table (no versioning)
-- Live = 'False' → Versioned table (full lifecycle support)

// Internal versioning logic (simplified)

// 1. Find current Aurora_Data record
SELECT id FROM Aurora_Data 
WHERE Source_table = 'products' 
  AND IsCurrent = 1 
  AND Current_ID = 100;

// 2. Copy the current record to create history
INSERT INTO Aurora_Data (...) 
SELECT ... FROM Aurora_Data WHERE ID = current_aurora_id;

// 3. Mark old version as not current
UPDATE Aurora_Data 
SET IsCurrent = 0, Lock = 0, Next_ID = new_aurora_id 
WHERE ID = current_aurora_id;

// 4. Update new version metadata
UPDATE Aurora_Data 
SET Title = 'New Title', 
    IsCurrent = 1, 
    Status = 'Editing', 
    Published = 0, 
    Staged = 0,
    Current_ID = new_record_id,
    Last_ID = old_aurora_id,
    Updated_By = user_id,
    Updated = GetDate() 
WHERE ID = new_aurora_id;

// API Request - Get version history
POST /getDataRows
{
  "table": "aurora_data",
  "fields": "id, current_id, title, status, published, staged, created, updated, updated_by",
  "isAppGrid": true,
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "master_id",
        "operator": "equals",
        "value": "100"
      },
      {
        "field": "source_table",
        "operator": "equals", 
        "value": "products"
      }
    ]
  },
  "orderBy": "id DESC"
}

{
  "status": "Success",
  "data": [
    {
      "id": 503,
      "current_id": 110,
      "title": "Product v3 - Latest",
      "status": "Editing",
      "published": 0,
      "staged": 0,
      "created": "2024-01-15T14:30:00Z",
      "updated": "2024-01-15T14:30:00Z",
      "updated_by": 5
    },
    {
      "id": 502,
      "current_id": 105,
      "title": "Product v2 - Published",
      "status": "Published",
      "published": 1,
      "staged": 1,
      "created": "2024-01-10T09:15:00Z",
      "updated": "2024-01-12T10:00:00Z",
      "updated_by": 3
    },
    {
      "id": 501,
      "current_id": 100,
      "title": "Product v1 - Original",
      "status": "Published",
      "published": 0,
      "staged": 0,
      "created": "2024-01-01T12:00:00Z",
      "updated": "2024-01-01T12:00:00Z",
      "updated_by": 1
    }
  ]
}

// First get the version's Current_ID from Aurora_Data
POST /getDataRows
{
  "table": "aurora_data",
  "fields": "current_id",
  "isAppGrid": true,
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      { "field": "id", "operator": "equals", "value": "502" }
    ]
  },
  "record": true
}

// Then fetch the actual content using that Current_ID
POST /getDataRows
{
  "table": "products",
  "fields": "*",
  "isAppGrid": true,
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      { "field": "id", "operator": "equals", "value": "105" }
    ]
  },
  "record": true
}

// Rollback process (internal)

// 1. Mark current version as not current
UPDATE Aurora_Data 
SET IsCurrent = 0, Updated = GetDate(), Updated_By = @userId 
WHERE ID = @currentVersionId;

// 2. Restore selected version as current
UPDATE Aurora_Data 
SET Status = 'Rolled Back', 
    IsCurrent = 1, 
    Updated = GetDate(), 
    Updated_By = @userId 
WHERE ID = @rollbackToVersionId;

┌──────────┐     ┌──────────┐     ┌───────────┐     ┌───────────┐
│   NEW    │ ──► │ EDITING  │ ──► │  STAGED   │ ──► │ PUBLISHED │
└──────────┘     └──────────┘     └───────────┘     └───────────┘
                      │                                    │
                      │                                    ▼
                      │                             ┌───────────┐
                      │                             │  EXPIRED  │
                      │                             └───────────┘
                      ▼
                ┌───────────┐
                │  DELETED  │
                └───────────┘

// Publishing updates Aurora_Data with staging flags
UPDATE Aurora_Data 
SET Status = 'Staged',
    IsExpired = 0,
    Published = 0,
    Staged = 1,
    IsCurrent = 1,
    Lock = 0,
    Updated = GetDate(),
    Updated_By = @userId,
    Publish_By = @userId,
    Publish_Set_Date = @publishDate
WHERE ID = @auroraDataId;

// Publishing updates Aurora_Data with published flags
UPDATE Aurora_Data 
SET Status = 'Published',
    IsExpired = 0,
    Published = 1,
    Staged = 1,
    IsCurrent = 1,
    Lock = 0,
    Updated = GetDate(),
    Updated_By = @userId,
    Publish_By = @userId,
    Publish_Set_Date = @publishDate,
    Publish_Expire_Date = @expireDate
WHERE ID = @auroraDataId;

// Unpublish previous version
UPDATE Aurora_Data 
SET Published = 0, Staged = 0 
WHERE (Staged = 1 OR Published = 1) 
  AND Source_Table = @table 
  AND Master_ID = @masterId
  AND ID != @auroraDataId;

// Query condition for scheduled content
WHERE Aurora_Data.Published = 1 
  AND Aurora_Data.Publish_Set_Date <= GetDate()
  AND (Aurora_Data.Publish_Expire_Date > GetDate() 
       OR Aurora_Data.Publish_Expire_Date IS NULL)
  AND Aurora_Data.IsExpired != 1

POST /getDataRows
{
  "table": "products",
  "fields": "id, title, price, description",
  "isPublished": true,
  "orderBy": "title ASC"
}

SELECT ... FROM products
INNER JOIN Aurora_Data ON products.id = Aurora_Data.Current_ID
WHERE Aurora_Data.Source_Table = 'products'
  AND Aurora_Data.Published = 1
  AND Aurora_Data.Publish_Set_Date <= GetDate()
  AND (Aurora_Data.Publish_Expire_Date > GetDate() 
       OR Aurora_Data.Publish_Expire_Date IS NULL)
  AND Aurora_Data.IsExpired != 1

POST /getDataRows
{
  "table": "products",
  "fields": "id, title, price, description",
  "isStage": true,
  "orderBy": "title ASC"
}

SELECT ... FROM products
INNER JOIN Aurora_Data ON products.id = Aurora_Data.Current_ID
WHERE Aurora_Data.Source_Table = 'products'
  AND Aurora_Data.Staged = 1
  AND Aurora_Data.Publish_Set_Date <= GetDate()
  AND (Aurora_Data.Publish_Expire_Date > GetDate() 
       OR Aurora_Data.Publish_Expire_Date IS NULL)
  AND Aurora_Data.IsExpired != 1

POST /getDataRows
{
  "table": "products",
  "fields": "id, title, price, description",
  "isLive": true,
  "orderBy": "title ASC"
}

SELECT ... FROM products
INNER JOIN Aurora_Data ON products.id = Aurora_Data.Current_ID
WHERE Aurora_Data.Source_Table = 'products'
  AND Aurora_Data.IsCurrent = 1

POST /getDataRows
{
  "table": "products",
  "fields": "id, title, price",
  "isAppGrid": true,
  "orderBy": "id DESC"
}

SELECT id, title, price FROM products ORDER BY id DESC

// Expiration is set during the publish action
UPDATE Aurora_Data 
SET Published = 1,
    Staged = 1,
    Publish_Set_Date = '2024-03-01',
    Publish_Expire_Date = '2024-03-31'
WHERE ID = @auroraDataId;

UPDATE Aurora_Data 
SET Status = 'Expired',
    Published = 0,
    Staged = 0,
    IsExpired = 1,
    Publish_Expire_Date = GetDate(),
    Updated = GetDate(),
    Updated_By = @userId
WHERE Master_ID = @masterId 
  AND (Published = 1 OR Staged = 1)
  AND Source_Table = @table;

{
  "_id": "encrypted_master_id_string",
  "meta_master_id": 100,
  "meta_current_id": 110,
  "meta_status": "Published",
  "meta_title": "Product Name",
  "meta_updated": "2024-01-15T14:30:00Z",
  "id": 110,
  "title": "Product Name",
  "price": 99.99
}