Radar

Production PostgreSQL at Runway Avenue

4 min read

By Prashant Mehta, 10/26/25

Introduction

If you don’t know anything about RDBMS, I highly recommend the Geeks for Geeks Guide for DBMS, all of it is good material and if you have questions feel free to let someone working on it within the team know. I’m more than willing to answer any questions, but I won’t bother explaining these topics as Geeks for Geeks does a much smoother job.

As for the Runway Avenue specific things, we track a lot of data. Like, a lot. And it would be horrible if we used a NoSQL database but that’s a conversation for another-time. The table structure goes something like this:

Note, (12/3/25): This is unfinished at this time, I will come back for it but also I’d like it to be visible for the time being.

Production Schema:

  • catalog
    • ITEM_ID (PRIMARY KEY)
    • VENDOR_ID (FOREIGN KEY)
    • NAME (TEXT)
    • DESCRIPTION (TEXT)
    • PRICE (DECIMAL(10,2), FULL_PRICE)
    • FULL_PRICE(DECIMAL(10,2))
    • IMG1_LOC (TEXT)
      • SELECT IMG_LOC FROM catalog_images WHERE NUM == 1 AND catalog.ITEM_ID == catalog_images.ITEM_ID
    • ITEM_VEC (FLOAT[32])
    • ACTIVE (BOOLEAN)
    • ATTRIBUTES_LOC (TEXT)
      • Refers to a JSON stored in GCS bucket for training data
    • COMMON_TAG (INDEX)
      • If multiple items have the same common_tag, each item with the same common_tag is considered a Shopify variant of the another item in this group.
  • catalog_inventory
    • ITEM_ID (FOREIGN KEY)
      • Has duplicates in this table
    • SIZE_ID (PRIMARY KEY)
      • Doesn’t have duplicates
    • QTY_AVAILABLE (INT16)
  • catalog_images
    • IMG_ID (PRIMARY_KEY)
      • IMG_ID is "ITEM_ID" + " _ " + "NUM"
    • ITEM_ID (FOREIGN KEY)
    • NUM (INT8)
    • IMG_LOC (TEXT)
  • vendors
    • VENDOR_ID (UUID, PRIMARY KEY)
    • TOTAL_PRODUCTS (INT32)
    • STATUS (ENUM(ACTIVE, SUSPENDED, TERMINATED))
    • LAST_SYNC_AT (TIMESTAMP)
    • COMMISSION_RATE (DECIMAL(7,2))
    • SYNC_TOOL (ENUM(SHOPIFY, SDK, API))
      • Note: Adjustable based on plan to provide services
    • AVAILABLE_REGION (ENUM[“US”, “IN”, “CA”]))
      • Regions vendor can ship to
    • CONTRACT_LOC (TEXT)
      • Link to GCS bucket with verifiable contract
    • CONTRACT_EXPIRY (TIMESTAMP)
      • Note: Utilize data extraction from contract e-sign
  • purchases (REQUIRES: TIMESCALEDB)
    • TIMESTAMP (TIME)
    • CART_ID (FOREIGN KEY)
    • ITEM_ID (FOREIGN KEY)
    • SUBTOTAL
    • PAID
    • VENDOR_ORDER_NUM
    • TRACKING_ID (FOREIGN KEY)
    • PROMO_ID (ARRAY[FOREIGN KEY])
    • PAYMENT_TOKEN (Stripe)
      • May require specific handling, look into SOC2/2 compliance
  • cart (REQUIRES: TIMESCALEDB)
    • TIMESTAMP (TIME)
    • ITEMS (Array[item_id], FOREIGN KEY)
    • SUBTOTAL (DECIMAL(10,2))
    • PROMO_ID ()
    • PROMO_AMT
    • FINAL_PRICE (DECIMAL(10,2))
    • SHIPPING_COST (null, ENUM(TWO-DAY, FREE, null))
      • ENUM instead of boolean incase we want to add PICKUP as an option later
    • PURCHASED (BOOL, True verifies entries / entry exists in purchases based on psql trigger)
  • percent_promotions
    • PROMO_ID (PRIMARY KEY)
  • quantity_promotions
    • PROMO_ID (PRIMARY KEY)
    • AMOUNT_OFF (DECIMAL(10,2), )
  • tracking
    • TRACKING_ID (PRIMARY KEY)
    • SHIPPING_HANDLER_ID (FOREIGN KEY)

Authentication

Please read: https://developer.hashicorp.com/vault/docs/secrets/databases/postgresql as well as Deployment of HashiCorp Vault. Emergency SSH will be available via Boundary but will not be available to all or even most developers. Do not rely on direct connection to any databases.

Connecting to Postgres on Anton

Eventually, we will write an OSI guide for Runway Avenue’s architecture another-time . Since I don’t have one yet, you’re stuck with my brief explanation of L4 and L7. L7 is application based data, which means your request, queries, etc are written as some form of a code or query. L7 is below the presentation layer which is where things like Protobuf live (L6). This can be confusing because L4 is TCP. So when we say that Postgres operates over L4, we mean simply that your L7 request is being shot to another service over L4.

Actually, if we really want to get specific, your L7 request is encrypted by TLS (L6), then given to postgres over L4 transport, routed by L3 network, then it hits postgres where the session is identified (L5) by Postgres; all while traveling through RJ45 ports (L2, ethernet) and copper (L1). However all of this is largely semantics, the reason we only worry about L4 is because we own everything between L4 and L7. L3, L2, and L1 are provided to us by network switch providers and our colocation company. Hence, we utilize L4 in our code when referring to Postgres.

Now this means that the infra people will be pleased to know Envoy makes our life incredibly easy here. Envoy’s proxy allows us to invoke Postgres within the kubernetes cluster; meaning that as far as you can tell as a developer you can’t even see L3 and below. All you know is that you’re writing a request with a TCP destination. Calico can identify and map the DNS and we’re good to go!

Graph View

Backlinks