KArtSell.Aegis/docs/contracts/data/VS-00_DATA_CONTRACT.md

VS-00 Platform Bootstrap - DATA_CONTRACT

Version: 1.0
Status: APPROVED (AEG-VS-00-02)
Date: 2026-08-04
Author: Data Architect/DBA
Gateway: G0 (Platform Foundation)

---

1. Acceptance Criteria (from WBS_MASTER.csv)

✅ Requirement: published_at/revision/valid-time/hash/단위/격리/재처리와 소유자가 정의되고 overwrite 경로가 없음

---

2. Temporal Dimensions (PIT Envelope)

2.1 published_at (Publication Timestamp)

Property	Value
Type	timestamp without time zone (UTC)
Nullable	NO
Default	now() at insert time
Invariant	published_at <= now() (at query time)
Usage	Point-in-time snapshot marker; used in all queries as WHERE published_at <= @cutoff

Schema:

published_at TIMESTAMP NOT NULL DEFAULT now()

Examples:

✅ 2026-08-04 10:30:45.123 UTC
❌ 2026-08-05 10:30:45.123 UTC (future date forbidden)

2.2 revision (Data Version)

Property	Value
Type	int (sequential, non-negative)
Nullable	NO
Range	0 to 2,147,483,647 (INT32_MAX)
Increment	Always increases; never decreases or repeats
Uniqueness	(aggregate_id, revision) unique constraint

Schema:

revision INT NOT NULL DEFAULT 1,
CONSTRAINT uk_aggregate_id_revision UNIQUE(aggregate_id, revision)

Invariant:

revision(version_N) > revision(version_N-1)

2.3 valid-time (Business Validity Window)

Property	Value
Type	valid_from TIMESTAMP NOT NULL, valid_to TIMESTAMP NULL
Semantics	Period during which this record represents reality
Null Handling	valid_to = NULL means "currently valid" (open-ended)
Non-Overlapping	For same aggregate_id, valid-time intervals must not overlap

Schema:

valid_from TIMESTAMP NOT NULL,
valid_to TIMESTAMP NULL,
CONSTRAINT ck_valid_time CHECK (valid_from < valid_to OR valid_to IS NULL),
CONSTRAINT uk_valid_time UNIQUE(aggregate_id, valid_from)

Examples:

Scenario: Interest rate change
- Record 1: valid_from=2026-01-01, valid_to=2026-06-30 (past)
- Record 2: valid_from=2026-07-01, valid_to=NULL (current)
✅ No overlap; continuous coverage

---

3. Integrity Dimensions

3.1 hash (Content Hash)

Property	Value
Type	varchar(64) (SHA-256 hex)
Nullable	NO
Purpose	Detect data corruption; enable row-level replay detection
Computation	SHA256(serialized_payload)

Schema:

content_hash VARCHAR(64) NOT NULL,
INDEX idx_content_hash (content_hash)

Replay Detection (Idempotency):

IF EXISTS (SELECT 1 FROM shadow_runs 
           WHERE aggregate_id = @id 
           AND content_hash = @newHash)
  THEN SKIP (already applied)
ELSE INSERT (new data)

3.2 단위 (Measurement Unit / Currency)

Property	Value
Type	varchar(10) (code, e.g., 'KRW', 'USD', 'SHARES')
Nullable	NO
Immutable	YES; cannot change across revisions for same aggregate
Constraint	Must match expected unit for field type

Schema:

unit_code VARCHAR(10) NOT NULL,
CONSTRAINT fk_unit_code FOREIGN KEY (unit_code) REFERENCES ref.units(code),
CONSTRAINT ck_unit_consistency CHECK (unit_code NOT NULL)

Examples:

✅ Field: price, unit: KRW
✅ Field: shares, unit: SHARES
❌ Field: price, unit: SHARES (mismatch)

---

4. Isolation & Replay

4.1 격리 (Isolation Level)

Property	Value
Type	Snapshot Isolation (SQL Standard: SERIALIZABLE for writes)
Read Consistency	✅ No dirty reads, no phantom reads within PIT window
Write Consistency	Append-only; no UPDATE or DELETE

Transaction Pattern:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  -- Verify row doesn't exist (idempotency check via hash)
  IF NOT EXISTS (...) THEN
    INSERT INTO events (...) VALUES (...);
  END IF;
COMMIT;

4.2 재처리 (Replay)

Property	Value
Pattern	Idempotent; same input = same result, always
Scope	(aggregate_id, published_at, revision) uniquely identifies record
Recovery	If handler crashes, event can be replayed from outbox without duplication

Replay Guarantee:

Event(id=123, published_at=T1, revision=R1, hash=H1)
  ├─ Replay 1: Creates row (success)
  ├─ Replay 2: Detects duplicate hash, skips (idempotent)
  └─ Replay N: Always skips (no side effects)

---

5. Ownership & Mutation Control

5.1 소유자 (Owner / Module Authority)

Property	Value
Concept	Each table/aggregate is owned by exactly one module
Access Pattern	Only owning module writes; others read via contracts
No Cross-Module Access	module_A cannot directly INSERT/UPDATE module_B's tables

Schema Pattern:

-- Table owned by model_operations module
CREATE TABLE model_operations.shadow_runs (
  ...
) TABLESPACE model_ops_space;

-- Only model_operations app-role can INSERT/UPDATE this table
GRANT INSERT, UPDATE ON model_operations.shadow_runs TO role_model_ops_write;
GRANT SELECT ON model_operations.shadow_runs TO public; -- read-only

Cross-Module Read:

// Module: signal_engine (read-only)
// Pattern: Use stored procedure or materialized view, never direct table access
var results = dbContext.ShadowRunsProjection
    .Where(x => x.published_at <= cutoffDate)
    .Select(x => new { x.Id, x.Score })
    .ToList();

5.2 overwrite 경로 불가 (No Direct Mutation)

Guarantee	Mechanism
No UPDATE	Row state is immutable once inserted
No DELETE	Historical data is retained for audit trail
No TRUNCATE	Table can only grow (append-only)
State Changes	Expressed as new row with incremented revision and new valid_to

Schema Enforcement:

-- Revoke all mutation permissions except INSERT
REVOKE UPDATE, DELETE, TRUNCATE ON model_operations.shadow_runs FROM PUBLIC;
REVOKE UPDATE, DELETE, TRUNCATE ON model_operations.shadow_runs FROM role_model_ops_write;

-- Only INSERT is permitted
GRANT INSERT ON model_operations.shadow_runs TO role_model_ops_write;

Example: State Transition (not overwrite)

-- OLD: Update is forbidden
UPDATE shadow_runs SET status = 'COMPLETED' WHERE id = 123;  -- ❌ DENIED

-- NEW: Insert new revision (append-only)
INSERT INTO shadow_runs 
  (aggregate_id, revision, published_at, valid_from, status, ...)
VALUES
  (123, 2, now(), now(), 'COMPLETED', ...);  -- ✅ ALLOWED

---

6. Data Quality Rules (DQ & Lineage)

6.1 Completeness

Field	Nullability	Reason
aggregate_id	NOT NULL	Identity
revision	NOT NULL	Version
published_at	NOT NULL	PIT marker
valid_from	NOT NULL	Validity window start
valid_to	NULL OK	Open-ended validity
content_hash	NOT NULL	Integrity check
unit_code	NOT NULL (domain-specific)	Measurement unit
Domain fields	Domain-specific	Per business rule

6.2 Lineage

Dimension	Source	Tracking
Data Provenance	Outbox event → Inbox handler → Write
Audit Trail	published_at + revision	Full history
Correlation	CorrelationId in event metadata	End-to-end tracing
Reproducibility	content_hash (deterministic)	Verify no data corruption

Lineage Query:

SELECT 
  aggregate_id,
  revision,
  published_at,
  valid_from,
  valid_to,
  content_hash,
  'source_system' AS provenance
FROM model_operations.shadow_runs
WHERE aggregate_id = @id
ORDER BY revision ASC;

---

7. Migration & Schema Versioning

7.1 Migration Files

MIG ID	Purpose	Status
MIG-0000	Create platform bootstrap schema	✅ Applied
MIG-0013	Create inbox/outbox tables	✅ Applied
MIG-00XX	Future VS-00 extensions	PENDING

Location: src/KArtSell.DbMigrator/Scripts/

7.2 Schema Evolution

• Additions: New columns are backward-compatible (nullable or with defaults)
• Deprecations: Columns marked deprecated, not dropped
• Breaking Changes: Require version bump + approval

---

8. Examples & Use Cases

8.1 Query Pattern: PIT (Point-in-Time)

// Acceptance Criteria: All reads must include PIT condition
var shadowRun = dbContext.ShadowRuns
    .Where(x => x.PublishedAt <= cutoffDate)  // ✅ PIT condition
    .Where(x => x.AggregateId == modelId)
    .OrderByDescending(x => x.Revision)       // Latest version
    .FirstOrDefault();

8.2 Insert Pattern: Append-Only with Idempotency

public async Task InsertShadowRunAsync(ShadowRunEvent evt)
{
    using var tx = await dbContext.Database.BeginTransactionAsync();
    try
    {
        // Check idempotency: if this exact hash exists, skip
        var isDuplicate = await dbContext.ShadowRuns
            .AnyAsync(x => x.ContentHash == evt.ContentHash);
        
        if (isDuplicate)
            return; // Idempotent: already inserted
        
        // Insert new record
        dbContext.ShadowRuns.Add(new ShadowRun
        {
            AggregateId = evt.ModelId,
            Revision = evt.Revision,
            PublishedAt = DateTime.UtcNow,
            ValidFrom = DateTime.UtcNow,
            ValidTo = null, // Current (open-ended)
            ContentHash = evt.ContentHash,
            UnitCode = "PROBABILITY",
            Status = "RUNNING",
            ...
        });
        
        await dbContext.SaveChangesAsync();
        await tx.CommitAsync();
    }
    catch
    {
        await tx.RollbackAsync();
        throw;
    }
}

8.3 Historical Query: Audit Trail

-- Show all revisions of a model's validation history
SELECT 
  revision,
  published_at,
  valid_from,
  valid_to,
  status,
  score
FROM model_operations.shadow_runs
WHERE aggregate_id = '00000000-0000-0000-0000-000000000001'
ORDER BY revision ASC;

/*
Result:
revision | published_at | valid_from | valid_to | status | score
1 | 2026-08-04 09:00 | 2026-08-04 09:00 | NULL | RUNNING | NULL
2 | 2026-08-04 10:30 | 2026-08-04 10:30 | NULL | RUNNING | 0.543
3 | 2026-08-04 11:00 | 2026-08-04 11:00 | NULL | COMPLETED | 0.567
*/

---

9. Verification (Testing)

9.1 Schema Conformance Test

[Fact]
public async Task ShadowRunsTable_ConformsToDataContract()
{
    // Verify schema matches contract
    var columnNames = dbContext.Model.FindEntityType(typeof(ShadowRun))!
        .GetProperties()
        .Select(p => p.GetColumnName())
        .ToList();

    Assert.Contains("published_at", columnNames);
    Assert.Contains("revision", columnNames);
    Assert.Contains("valid_from", columnNames);
    Assert.Contains("content_hash", columnNames);
    Assert.Contains("unit_code", columnNames);
}

9.2 Idempotency Test

[Fact]
public async Task Insert_IsIdempotent_SameHashNotDuplicated()
{
    var evt = new ShadowRunEvent { ... };
    
    // Insert twice
    await handler.Handle(evt);
    await handler.Handle(evt);
    
    // Should have only 1 record in database
    var count = dbContext.ShadowRuns
        .Count(x => x.ContentHash == evt.ContentHash);
    
    Assert.Equal(1, count);
}

9.3 PIT Query Test

[Fact]
public async Task Query_WithPitCondition_ReturnsOnlyCutoffData()
{
    // Insert records at different times
    var cutoff = new DateTime(2026, 8, 4, 10, 30, 0);
    
    await dbContext.ShadowRuns.AddRangeAsync(
        new { PublishedAt = cutoff.AddMinutes(-5), ... },  // Before cutoff
        new { PublishedAt = cutoff.AddMinutes(5), ... }    // After cutoff (should not appear)
    );
    await dbContext.SaveChangesAsync();
    
    // Query
    var results = dbContext.ShadowRuns
        .Where(x => x.PublishedAt <= cutoff)
        .ToList();
    
    // Should only return record before cutoff
    Assert.Single(results);
}

---

10. Sign-Off

Role	Name	Date	Approval
Data Architect/DBA	(Primary Owner)	2026-08-04	✅ APPROVED
Quant Lead	(Domain Expert)	2026-08-04	✅ APPROVED
Architect	(Tech Review)	2026-08-04	✅ APPROVED

---

11. Appendix: Related Documents

• Migration: src/KArtSell.DbMigrator/0000_PlatformBootstrap.sql
• Entity Model: src/KArtSell.Modules.Host/BuildingBlocks/PlatformBootstrap/Domain/ShadowRun.cs
• Query Tests: tests/KArtSell.Data.Tests/ShadowRunTests.cs
• WBS Requirement: AEG-VS-00-02 (Gate 0, Priority P0)

---

Status: ✅ APPROVED & ACTIVE
Last Updated: 2026-08-04
Versioning: This document version controls contract; changes require architect approval