DynamoDB Schema Pattern: Marketplace (Two-Sided)

A two-sided marketplace - Etsy, Airbnb, Uber, eBay - has the same data model whether you’re moving handcrafted goods or vacation rentals. Buyers and sellers are users with different verbs. Listings flow into orders. Orders flow into reviews. Reviews flow back into seller reputation, which influences future listings.

The schema problem is fitting all these entities and access patterns into a single DynamoDB table without exploding the GSI count. The trick is recognizing that buyers and sellers are the same entity with different roles, and that “reviews about user X” and “reviews written by user X” are two cuts of the same data.

This pattern handles 11 access patterns with 4 entities and 2 GSIs.

Access patterns

Eleven patterns, two GSIs (overloaded).

Entities

• User: buyer, seller, or both. One entity, one role attribute.
• Listing: what’s for sale, scoped to a seller, with a category for browse.
• Order: the purchase transaction. References buyer, seller, and listing.
• Review: mutual feedback after an order. Each order produces up to two reviews (buyer reviews seller, seller reviews buyer).

Table design

Primary key structure

The clever bit: reviews are stored in the partition of the user being reviewed, not in their own partition. That makes “list reviews about user X” (AP10) a single primary-table query - no GSI needed. The Review entity uses ULIDs for the SK so reviews sort chronologically and can be looked up directly.

GSI design (overloaded)

The GSI1 partition prefixes (USER_EMAIL#, SELLER#, BUYER#, AUTHOR#) never collide, so all four entities share the index without stepping on each other. GSI2 is the same trick (CATEGORY# and SELLER# are distinct).

GSI2 for Listings is sparse: only listings with status = "active" write to it. As soon as a listing is marked sold or removed, the activeFlag attribute is removed and the listing drops out of the category browse index. The browse query is always cheap because the index only contains live listings. See DynamoDB Sparse Indexes for the full pattern.

Sample data

Notice the sold listing (l_02) has no gsi2pk - it’s not in the category browse index. Active listing (l_01) is. That’s the sparse index in action.

Resolving each access pattern

AP1 - Get user by ID: GetItem(pk=USER#u_alice, sk=#METADATA)

AP2 - Get user by email: Query(GSI1, gsi1pk=USER_EMAIL#alice@x.com)

AP3 - Get listing by ID: GetItem(pk=LISTING#l_01, sk=#METADATA)

AP4 - List seller’s listings (any status):

Query(GSI1, gsi1pk=SELLER#u_alice, sk begins_with LISTING#)

Returns all listings, sorted by status then ID.

AP5 - List seller’s active listings:

Query(GSI1, gsi1pk=SELLER#u_alice, sk begins_with LISTING#active#)

Same partition, narrower SK prefix. No extra GSI needed - the sort-key-as-hierarchy trick handles status filtering.

AP6 - Browse active listings by category:

Query(GSI2, gsi2pk=CATEGORY#pottery)

Sparse GSI - only active listings are in this index. Sorted by ULID (newest first with ScanIndexForward: false).

AP7 - Get order by ID: GetItem(pk=ORDER#ord_01HV..., sk=#METADATA)

AP8 - List buyer’s order history:

Query(GSI1, gsi1pk=BUYER#u_bob, ScanIndexForward=false)

AP9 - List seller’s sales history:

Query(GSI2, gsi2pk=SELLER#u_alice, ScanIndexForward=false)

AP10 - List reviews about a user:

Query(pk=USER#u_alice, sk begins_with REVIEW#)

Primary table. Reviews live in the partition of the user being reviewed.

AP11 - List reviews written by a user:

Query(GSI1, gsi1pk=AUTHOR#u_bob)

ElectroDB entity definitions

export const UserEntity = new Entity({
  model: { entity: "user", version: "1", service: "marketplace" },
  attributes: {
    userId: { type: "string", required: true },
    name:   { type: "string", required: true },
    email:  { type: "string", required: true },
    role:   { type: ["buyer", "seller", "both"], required: true, default: "buyer" },
    rating: { type: "number" },              // computed avg of reviews about user
    reviewCount: { type: "number", default: 0 },
    createdAt: {
      type: "string", required: true,
      default: () => new Date().toISOString(), readOnly: true,
    },
    updatedAt: {
      type: "string", required: true,
      default: () => new Date().toISOString(),
      set: () => new Date().toISOString(), watch: "*",
    },
  },
  indexes: {
    primary: {
      pk: { field: "pk", composite: ["userId"], template: "USER#${userId}" },
      sk: { field: "sk", composite: [],         template: "#METADATA" },
    },
    byEmail: {
      index: "GSI1",
      pk: { field: "gsi1pk", composite: ["email"],  template: "USER_EMAIL#${email}" },
      sk: { field: "gsi1sk", composite: ["userId"], template: "USER#${userId}" },
    },
  },
}, { client, table });

export const ListingEntity = new Entity({
  model: { entity: "listing", version: "1", service: "marketplace" },
  attributes: {
    listingId: { type: "string", required: true, default: () => ulid() },
    sellerId:  { type: "string", required: true },
    title:     { type: "string", required: true },
    description: { type: "string" },
    price:     { type: "number", required: true },
    category:  { type: "string", required: true },
    status:    { type: ["draft", "active", "sold", "removed"], required: true, default: "active" },
    // sparse: only set when status === "active" - drives the category browse GSI
    activeFlag: {
      type: "string",
      watch: ["status"],
      set: (_, { status }) => (status === "active" ? "ACTIVE" : undefined),
    },
    createdAt: {
      type: "string", required: true,
      default: () => new Date().toISOString(), readOnly: true,
    },
    updatedAt: {
      type: "string", required: true,
      default: () => new Date().toISOString(),
      set: () => new Date().toISOString(), watch: "*",
    },
  },
  indexes: {
    primary: {
      pk: { field: "pk", composite: ["listingId"], template: "LISTING#${listingId}" },
      sk: { field: "sk", composite: [],            template: "#METADATA" },
    },
    bySeller: {
      index: "GSI1",
      pk: { field: "gsi1pk", composite: ["sellerId"], template: "SELLER#${sellerId}" },
      sk: { field: "gsi1sk", composite: ["status", "listingId"],
            template: "LISTING#${status}#${listingId}" },
    },
    byCategory: {
      index: "GSI2",
      pk: { field: "gsi2pk", composite: ["activeFlag", "category"],
            template: "CATEGORY#${category}" },
      sk: { field: "gsi2sk", composite: ["listingId"], template: "LISTING#${listingId}" },
      condition: ({ activeFlag }) => activeFlag !== undefined,
    },
  },
}, { client, table });

export const OrderEntity = new Entity({
  model: { entity: "order", version: "1", service: "marketplace" },
  attributes: {
    orderId:   { type: "string", required: true, default: () => ulid() },
    listingId: { type: "string", required: true },
    buyerId:   { type: "string", required: true },
    sellerId:  { type: "string", required: true },
    total:     { type: "number", required: true },
    status:    { type: ["pending", "paid", "shipped", "delivered", "cancelled"],
                 required: true, default: "pending" },
    createdAt: {
      type: "string", required: true,
      default: () => new Date().toISOString(), readOnly: true,
    },
    updatedAt: {
      type: "string", required: true,
      default: () => new Date().toISOString(),
      set: () => new Date().toISOString(), watch: "*",
    },
  },
  indexes: {
    primary: {
      pk: { field: "pk", composite: ["orderId"], template: "ORDER#${orderId}" },
      sk: { field: "sk", composite: [],          template: "#METADATA" },
    },
    byBuyer: {
      index: "GSI1",
      pk: { field: "gsi1pk", composite: ["buyerId"], template: "BUYER#${buyerId}" },
      sk: { field: "gsi1sk", composite: ["orderId"], template: "ORDER#${orderId}" },
    },
    bySeller: {
      index: "GSI2",
      pk: { field: "gsi2pk", composite: ["sellerId"], template: "SELLER#${sellerId}" },
      sk: { field: "gsi2sk", composite: ["orderId"],  template: "ORDER#${orderId}" },
    },
  },
}, { client, table });

export const ReviewEntity = new Entity({
  model: { entity: "review", version: "1", service: "marketplace" },
  attributes: {
    reviewId:       { type: "string", required: true, default: () => ulid() },
    reviewedUserId: { type: "string", required: true },  // who is being reviewed
    authorId:       { type: "string", required: true },  // who wrote it
    orderId:        { type: "string", required: true },
    rating:         { type: "number", required: true },  // 1-5
    body:           { type: "string" },
    createdAt: {
      type: "string", required: true,
      default: () => new Date().toISOString(), readOnly: true,
    },
  },
  indexes: {
    aboutUser: {
      pk: { field: "pk", composite: ["reviewedUserId"], template: "USER#${reviewedUserId}" },
      sk: { field: "sk", composite: ["reviewId"],       template: "REVIEW#${reviewId}" },
    },
    byAuthor: {
      index: "GSI1",
      pk: { field: "gsi1pk", composite: ["authorId"], template: "AUTHOR#${authorId}" },
      sk: { field: "gsi1sk", composite: ["reviewId"], template: "REVIEW#${reviewId}" },
    },
  },
}, { client, table });

Why this design

Buyer and seller are not different tables. A user transitions from buyer-only to “both” the first time they list something. This avoids duplicating user records and lets reputation aggregate across both sides of their behavior.

Reviews live in the reviewed user’s partition. The most common review query is “show me reviews about this seller” (AP10). Storing reviews as USER#<reviewedUserId> / REVIEW#<ulid> makes that a primary-table query with no GSI cost. The reverse direction (“reviews I’ve written”) is the GSI lookup, which is rarer.

Putting status in the GSI sort key as a prefix lets the same GSI serve “all my listings” and “my active listings” with different prefix scopes. No extra GSI for the status filter.

Active listings represent maybe 5-10% of all listings ever created. Indexing every listing in the category browse GSI would be 10-20x the storage and write cost for no read benefit. The sparse index pattern keeps GSI2 lean.

Order placement should be transactional. Creating an order, decrementing the listing’s status to “sold”, and possibly updating buyer/seller balances are all part of the same logical operation. Wrap them in TransactWriteItems to prevent the “order created but listing still says active” failure mode. The e-commerce orders pattern shows the multi-item transaction shape.

What I’d add for production:

• Search. “Find listings matching ‘blue ceramic mug’” is not a DynamoDB query. Stream listings to OpenSearch via DynamoDB Streams.
• Wishlist / favorites. Junction entity: USER#<userId> / FAVORITE#<listingId> for “things this user has saved.”
• Messages between buyer and seller. A separate conversation pattern - see the chat messaging pattern.
• Rating aggregation. The rating and reviewCount on User are denormalized for fast display. Update them via a Stream-driven projection rather than recomputing on every read.
• Idempotency on order creation. Buyer double-clicks should not create two orders. Use idempotency keys keyed on the buyer’s request.

What this schema doesn’t support

This is the “what you’re not supporting” exercise from unsupported queries - none of these are mistakes, but they’re choices worth being explicit about.

Design this visually → coming soon

Imagine dragging User, Listing, Order, Review onto a canvas and watching the GSI overloads collapse the index count from 4 to 2. That’s what I’m building at singletable.dev.

Join the waitlist →

Pattern #6 of 10 in the SingleTable pattern library. The two-sided design pairs naturally with the e-commerce orders pattern (one-sided variant) and the SaaS multi-tenant pattern (organizational variant). I’m building singletable.dev to make GSI overloading visible at design time.