Column Ordering Standards & UUIDv7 Keys

To maximize developer ergonomics in C# while enforcing deterministic, professional database schemas in PostgreSQL, GERCIA implements a Hybrid Column Ordering Standard paired with temporal UUIDv7 (RFC 9562) primary keys.

---

1. The Hybrid Ordering Standard

• In C# Domain Entities (Application Tier): All properties MUST be declared in Strict Alphabetical Order (A-Z). This guarantees immediate IDE IntelliSense readability, prevents arbitrary property ordering debates in code reviews, and simplifies class navigation.
• In Physical PostgreSQL Tables (Database Tier): Metadata columns are strictly enforced at ordinal positions 0 to 10 via EF Core .HasColumnOrder(), while business domain columns begin sequentially at position 11+ in logical domain order.

flowchart LR
    subgraph CSHARP ["C# Domain Entity (A-Z Clean Order)"]
        direction TB
        C1["BirthDate"]
        C2["Cpf"]
        C3["CreatedAt"]
        C4["FullName"]
        C5["Id"]
        C6["TenantId"]
    end

    subgraph EF ["EF Core Model Builder Convention"]
        MAP["ConfigureMetadataColumns()<br>Maps metadata to ordinals 0-10<br>Domain columns to ordinals 11+"]
    end

    subgraph PG ["PostgreSQL Physical Table Layout"]
        direction TB
        P0["Ordinal 0: id (UUIDv7)"]
        P1["Ordinal 1: tenant_id"]
        P2["Ordinal 2: branch_id"]
        P3["Ordinals 3-10: Auditing & Version"]
        P11["Ordinal 11+: Business Attributes (birth_date, cpf, full_name...)"]
    end

    CSHARP --> MAP --> PG

---

2. PostgreSQL Ordinal Positions (0 to 10)

Universal platform metadata columns are mapped consistently across every table in the core, clinical, dental, pharmacy, veterinary, and operations schemas:

Ordinal Position	Column Name	Physical PostgreSQL Type	Description & Behavior
0	id	uuid	Primary Key generated via Guid.CreateVersion7().
1	tenant_id	uuid	Tenant boundary key (present if entity implements IMustHaveTenant).
2	branch_id	uuid	Branch/location key (present if entity implements IMustHaveBranch).
3	created_at	timestamptz	Audit timestamp of entity creation (UTC).
4	created_by	uuid	Master identity user_id who created the record.
5	last_modified_at	timestamptz	Audit timestamp of the most recent mutation (UTC).
6	last_modified_by	uuid	Master identity user_id who executed the mutation.
7	is_deleted	boolean	Soft-delete flag (default false).
8	deleted_at	timestamptz	Audit timestamp of soft deletion (UTC, nullable).
9	deleted_by	uuid	Master identity user_id who soft-deleted the record (nullable).
10	version	integer	Optimistic concurrency control token (incremented on each update).
11+	Domain columns	varies	Specific business domain attributes ordered logically.

---

3. Automated EF Core Configuration Convention

Rather than requiring developers to manually write .HasColumnOrder(...) on every entity configuration, the platform foundation provides an automated model convention extension in Gercia.Core:

public static class ModelBuilderMetadataExtensions
{
    public static void ConfigureMetadataColumns(this ModelBuilder modelBuilder)
    {
        foreach (var entityType in modelBuilder.Model.GetEntityTypes())
        {
            var clrType = entityType.ClrType;

            if (typeof(Entity).IsAssignableFrom(clrType))
            {
                entityType.FindProperty(nameof(Entity.Id))?.SetColumnOrder(0);
            }

            if (typeof(IMustHaveTenant).IsAssignableFrom(clrType))
            {
                entityType.FindProperty(nameof(IMustHaveTenant.TenantId))?.SetColumnOrder(1);
            }

            if (typeof(IMustHaveBranch).IsAssignableFrom(clrType))
            {
                entityType.FindProperty(nameof(IMustHaveBranch.BranchId))?.SetColumnOrder(2);
            }

            if (typeof(IAuditable).IsAssignableFrom(clrType))
            {
                entityType.FindProperty(nameof(IAuditable.CreatedAt))?.SetColumnOrder(3);
                entityType.FindProperty(nameof(IAuditable.CreatedBy))?.SetColumnOrder(4);
                entityType.FindProperty(nameof(IAuditable.LastModifiedAt))?.SetColumnOrder(5);
                entityType.FindProperty(nameof(IAuditable.LastModifiedBy))?.SetColumnOrder(6);
            }

            if (typeof(ISoftDeletable).IsAssignableFrom(clrType))
            {
                entityType.FindProperty(nameof(ISoftDeletable.IsDeleted))?.SetColumnOrder(7);
                entityType.FindProperty(nameof(ISoftDeletable.DeletedAt))?.SetColumnOrder(8);
                entityType.FindProperty(nameof(ISoftDeletable.DeletedBy))?.SetColumnOrder(9);
            }

            if (typeof(IVersioned).IsAssignableFrom(clrType))
            {
                entityType.FindProperty(nameof(IVersioned.Version))?.SetColumnOrder(10);
            }
        }
    }
}

---

4. UUIDv7 Architecture & Mathematical Layout

100% of platform primary keys are generated in C# application memory using UUIDv7 (RFC 9562) via modern .NET 10 Guid.CreateVersion7().

 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                           unix_ts_ms                          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|          unix_ts_ms           |  ver  |       rand_a          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|var|                        rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                            rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Bit Allocation:

• unix_ts_ms (48 bits): Current Unix timestamp with millisecond resolution, providing natural chronological sorting.
• ver (4 bits): Binary 0111 (Version 7).
• rand_a (12 bits): Sub-millisecond sequence counter ensuring strict monotonic ordering for IDs generated in the exact same millisecond.
• var (2 bits): Binary 10 (RFC 4122 / 9562 variant).
• rand_b (62 bits): Cryptographically secure pseudo-random bits, ensuring global uniqueness across distributed application containers.

---

5. B-Tree Index Performance: UUIDv4 vs UUIDv7

Using traditional random UUIDv4 as database primary keys is an enterprise anti-pattern that severely degrades PostgreSQL performance at scale.

flowchart TD
    subgraph V4 ["UUIDv4 (Random Distribution)"]
        INSERT_V4["Insert Random Key"] --> SCATTER["Scatters across arbitrary B-Tree leaf pages"]
        SCATTER --> SPLIT["Constant B-Tree Page Splits (50% fill factor)"]
        SPLIT --> BLOAT["Index Bloat & High WAL Write Volume"]
        BLOAT --> THRASH["Buffer Cache Thrashing in shared_buffers"]
    end

    subgraph V7 ["UUIDv7 (Monotonic Temporal Prefix)"]
        INSERT_V7["Insert Temporal Key"] --> APPEND["Appends to rightmost B-Tree leaf page"]
        APPEND --> COMPACT["Dense 90-95% Page Fill Factor"]
        COMPACT --> FAST["Minimal WAL Logging & High Insert Throughput"]
        FAST --> CACHE["Hot Index Working Set fits in shared_buffers"]
    end

Comparative Benchmark Metrics:

Performance Metric	UUIDv4 (Random)	UUIDv7 (Temporal Monotonic)	Architectural Advantage
B-Tree Page Splits	Continuous on every leaf	Virtually eliminated (< 1%)	Prevents index fragmentation
Index Page Density	~50% average fill factor	90% - 95% fill factor	~45% smaller index storage footprint
PostgreSQL Write-Ahead Logging (WAL)	High (full-page writes on splits)	Minimal (sequential leaf appends)	Drastically reduces disk I/O and Cloud SQL WAL archiving volume to Google Cloud Storage
Temporal Sorting & Pagination	Requires secondary index on created_at	Built-in (ORDER BY id DESC)	Eliminates redundant B-Tree indexes on audit timestamps
Application Generation	Client-side (in-memory)	Client-side (Guid.CreateVersion7())	Zero database roundtrip required before persisting aggregate graphs