Why I Used Aurora DSQL Instead of PostgreSQL for Real-Time Collaborative Schema Design

#h0hackathon #aws #vercel #auroradsql

I built this project as my entry for the H0: Hack the Zero Stack hackathon, sponsored by Vercel and AWS. This post covers the core architectural decision behind VersionZero — a real-time collaborative database schema designer built on Next.js, Vercel, Amazon Aurora DSQL, and Amazon DynamoDB. #H0Hackathon

The Problem That Started Everything

Two engineers. Different timezones. Same database schema to design.

This is the daily reality for most distributed engineering teams today. And the tooling has not caught up. Teams either use Notion docs (not schema-aware, no SQL output), dbdiagram.io (only one person drives at a time), or they get on a Zoom call and share a screen — which means one engineer types while the other watches.

I wanted to build a tool that solved this at the infrastructure level, not just the UI level. That distinction became everything.

Why This Is Not Just a UX Problem

When two engineers simultaneously design a shared schema, the backend storing that schema must guarantee both users always see the same state.

If engineer A in Bengaluru sees that table orders has a column status, but engineer B in Berlin hasn't received that update yet, they will design conflicting foreign keys. The schema becomes incoherent. That's not a UI glitch — that's a distributed consistency failure.

This is what ruled out most databases immediately.

Why Not Traditional PostgreSQL?

Aurora PostgreSQL is my default choice for 90% of projects. But for this specific use case, it has one hard problem: a primary region.

All writes go to the primary. Read replicas are eventually consistent. If engineer B is in Europe and the primary is in us-east-1, every schema mutation they make goes cross-continent to the primary before it's committed. This adds latency and creates a bottleneck. The "globally fast collaborative tool" narrative breaks the moment you look at where writes actually land.

I also considered multi-master setups like Patroni, but that requires operational overhead that's completely out of scope for a product built in a hackathon window — and frankly, it's out of scope for most small teams in production too.

Why Not DynamoDB?

DynamoDB Global Tables give you multi-region writes with sub-millisecond latency. That sounds perfect. But DynamoDB Global Tables use eventual consistency — which means two users in different regions can briefly see different states.

For presence data (cursor positions, who's online), eventual consistency is completely fine. Seeing a cursor 200ms out of date is not a problem.

For schema state — the tables, columns, and relationships that form the contract the entire team builds against — eventual consistency is catastrophic. You cannot build a correct design tool on a database that allows two users to momentarily see different schemas.

Why Aurora DSQL Is the Only Correct Answer

Aurora DSQL is Amazon's serverless distributed SQL database with multi-region active-active writes and synchronous consistency at commit time.

No primary region. Any region can accept writes.
All regions see the same logical state.
Conflicts are detected at commit time using Optimistic Concurrency Control (OCC).

That last point is what makes it fundamentally different from a traditional PostgreSQL setup.

How OCC Works in DSQL

Traditional databases use pessimistic locking: when you start editing a row, the database locks it. Other writers wait. This prevents conflicts but creates deadlocks and kills throughput.

DSQL uses optimistic concurrency: no locks are held during transaction execution. Every transaction runs against a consistent snapshot of the database. At commit time, DSQL checks whether any other concurrent transaction modified the same rows.

If no conflict: both transactions commit successfully.
If conflict: one succeeds, the other receives error code 40001 (serialization_failure) and must retry from scratch.

For a collaborative tool, this model is strictly better. Two engineers editing different tables simultaneously will always both succeed. Only two engineers editing the exact same column at the exact same millisecond conflict — and even then, the retry resolves it in under 100ms.

The OCC Retry Wrapper

Every mutation in VersionZero goes through this function:

// lib/dsql.ts
// Aurora DSQL uses Optimistic Concurrency Control.
// Concurrent writes to the same rows cause one transaction to fail
// with error code 40001 (serialization_failure) at commit time.
// This wrapper retries automatically with exponential backoff.

export async function withDSQLRetry<T>(
  fn: (client: PoolClient) => Promise<T>,
  maxRetries = 3
): Promise<T> {
  let attempt = 0;

  while (attempt < maxRetries) {
    const client = await getPool().connect();
    try {
      await client.query('BEGIN');
      const result = await fn(client);
      await client.query('COMMIT');
      return result;

    } catch (err: any) {
      await client.query('ROLLBACK');

      // 40001 = serialization_failure — safe to retry
      if (err.code === '40001' && attempt < maxRetries - 1) {
        attempt++;
        await new Promise(r => setTimeout(r, 50 * attempt)); // 50ms, 100ms, 150ms
        continue;
      }
      throw err;

    } finally {
      client.release();
    }
  }

  throw new Error(`DSQL: max retries (${maxRetries}) exceeded`);
}

This function is called on every POST, PATCH, and DELETE route in the API. The schema mutation and its audit log entry are written in the same transaction — they are atomic. Either both succeed or both roll back. The audit trail is correct by construction, not by convention.

What DSQL Does Not Support (And Why That's Fine)

DSQL breaks from standard PostgreSQL in a few important ways. Every limitation exists because of the distributed architecture:

No REFERENCES (foreign key constraints): Enforcing foreign keys globally requires coordination that undermines OCC. I enforce referential integrity in the API layer instead.
No JSONB: Use TEXT with JSON.stringify / JSON.parse. Simpler than it sounds.
No SERIAL / BIGSERIAL: Sequences cannot be globally consistent across regions. Use gen_random_uuid() instead — correct and safe in distributed environments.
No triggers, views, or PL/pgSQL: Server-side compute belongs in the application layer for a distributed database.

Understanding why each constraint exists made me a better distributed systems designer. It is not a list of PostgreSQL features DSQL forgot to implement. It is a list of features that are fundamentally incompatible with synchronous multi-region consensus.

The Result

VersionZero is a real-time collaborative database schema designer where:

Multiple engineers can simultaneously design tables, define columns, and draw relationships.
DSQL guarantees they always see the same schema state.
OCC conflict resolution is visible in the UI as a non-blocking toast.
Every edit is logged atomically in the same transaction as the mutation itself.
One button exports production-ready PostgreSQL DDL.

The live demo is at https://versionzero.vercel.app

The full source code is at https://github.com/Roopalgn/VercelAWS

*Built for the H0: Hack the Zero Stack hackathon by Vercel and AWS.