# Bean & Bark: find every customer the bad batch hurt

Part one ended with the table grown 40× and the preview outgrown. Act two opens on the harder truth of scale: **not every question has a key** — and not every mistake has a preview. This chapter you recover from the one error no guard can catch, then build the search surface that finds everyone it touched.

**You'll learn**

- Turn a **`BACKUP`** into a **`RESTORE`** — materialize a snapshot as a *new* table, without touching the live one
- Build the door: provision a **scoped OpenSearch projection** of one entity type, with the whole setup disclosed before consent
- Walk through it: search free text with **`MATCH`**, read **`HIGHLIGHT`** fragments, and let a **`FACET`** surface a trend
- Know what a **derived, eventually consistent** projection is — and when to go back to the table of record

> **Act two runs on your AWS:** Part one runs free on a local DynamoDB. From here the story uses **Professional** capabilities against real AWS services — OpenSearch, S3, managed backups — so these chapters are written to be *read*: every run's receipt is shown, exactly as the studio discloses it. The free sample dataset stays the act-one playground.

> **8:12am — the morning after:** **Nadia:** *"the address-normalization job Sam ran friday — couriers are bouncing deliveries and support is on fire. how bad is it, and can we get the real addresses back?"*

## The mistake no preview can catch

Sam's job was careful by the book: the `WHERE` was scoped, the background job re-proved it on every row, the count matched. But the `SET` itself was wrong — a normalization template that mangled flat numbers on ~1,900 orders. A correct predicate applied a wrong value, and **no preview or per-item guard can know your intent**. This is exactly the mistake the chapter-three ritual exists for, and Sam kept the ritual: one `BACKUP TABLE` before the job, ARN saved.

```sql
-- Sam's seatbelt, clipped before the job — the chapter-3 ritual
BACKUP TABLE "BeanAndBark" AS 'beanandbark-pre-addressfix';

-- this morning: materialize that snapshot as a NEW table
RESTORE TABLE "BeanAndBark-pre-addressfix"
  FROM BACKUP 'arn:aws:dynamodb:eu-west-1:…:table/BeanAndBark/backup/…';
```

- **`RESTORE TABLE` never overwrites.** It lowers to `RestoreTableFromBackup` and builds a **new** table from the snapshot; the target name must not already exist, and the live table is untouched.
- **It runs in the background.** DynamoDB rebuilds the table from the snapshot; the Background Tasks panel tracks it to `ACTIVE`. Plan for minutes, not seconds.
- **Recovery is then an ordinary read.** With `BeanAndBark-pre-addressfix` live, the pre-job `ShipTo` values are one keyed query away, and the fix-up write goes through the same preview-first ritual as any other.

### The restore, as the studio runs it

Materialize Friday's snapshot next to production, so the correct addresses can be read back without touching the live table.

```sql
RESTORE TABLE "BeanAndBark-pre-addressfix"
  FROM BACKUP 'arn:aws:dynamodb:eu-west-1:…:table/BeanAndBark/backup/…'
```

_Executes as:_ RestoreTableFromBackup · new table BeanAndBark-pre-addressfix · source table and backup unchanged

- A read-only prod stage refuses both statements — backup and restore are control-plane writes, unlocked the same conscious way as chapter three's UPDATE.

_Quote this example:_ https://dynostudio.dev/docs/dynostudio-bean-bark-support-search/#restore-receipt

The addresses are recoverable. That's the seatbelt paying out: one line on Friday turned this morning from a disaster into a chore. But recovering the *data* isn't the same as recovering the *customers* — every one of them who noticed is now a free-text note in the support pile.

## Eighteen thousand notes, three ways to say "never arrived"

Support cases landed in the same table this year — `EntityType = 'SupportCase'`, with a free-text `Message`. Finding the affected customers means searching prose, and the table has no door for that. A `Scan` with `contains(Message, 'never arrived')` reads — and bills — every item, matches only that exact substring, and ranks nothing: *"package never showed"*, *"no delivery"*, and *"where is my order"* all slip through. A **GSI** can't fix this either — an index on words isn't an attribute lookup. Free-form text belongs in a **derived search projection**.

```sql
CREATE OPENSEARCH INDEX "bean-and-bark-support" ON "BeanAndBark"
  PROJECT (CaseId, CustomerName, Message, Status, Carrier, CreatedAt)
  MAPPING (Message SEARCH, Status FILTER, Carrier FILTER, CreatedAt FILTER)
  ON INSERT, MODIFY, REMOVE
  WHERE EntityType = 'SupportCase';
```

Read the statement as a contract. `PROJECT` shapes each indexed document to the six fields support triage needs. `MAPPING` declares intent per field: `Message SEARCH` becomes analyzed full text; `Status`, `Carrier` and `CreatedAt` get `FILTER` keyword fields so exact matches, sorting and facets work. `ON INSERT, MODIFY, REMOVE` propagates all three stream events. And the partial `WHERE` scopes the projection to support cases only — an item that ever changes *out* of scope is **deleted from the index**, so the projection never keeps a ghost copy of something it shouldn't hold.

- **The preconditions are checked for you** — the table needs Streams (`NEW_IMAGE` or `NEW_AND_OLD_IMAGES`) and PITR for the seed export, and the stage needs its one-time Search binding (⌘K → Connect Search).
- **The plan is disclosed before consent** — the preview names the PITR export that seeds existing cases, the OpenSearch Ingestion pipeline that keeps the projection current, the IAM and S3 pieces, and the recurring cost lines. Consent comes after the disclosure, never before.
- **Readiness is visible** — the seed export runs, the stream catches up, and the pipeline reaches `ACTIVE` before the registration goes live. The projection is ready — and *stays* honestly labelled as eventually consistent.

## Walk through the door

```sql
SELECT CaseId, CustomerName, Status, Carrier, CreatedAt
FROM OPENSEARCH "bean-and-bark-support"
WHERE Message MATCH 'never arrived'
  AND Status = 'OPEN'
ORDER BY CreatedAt DESC
LIMIT 50
HIGHLIGHT (Message)
FACET Carrier
```

### Every open "never arrived", newest first

Find each open case whose message says a delivery never came — in any of the ways customers actually say it — with the matched fragment highlighted and a per-carrier breakdown in the footer.

```sql
SELECT CaseId, CustomerName, Status, Carrier, CreatedAt
FROM OPENSEARCH "bean-and-bark-support"
WHERE Message MATCH 'never arrived'
  AND Status = 'OPEN'
ORDER BY CreatedAt DESC
LIMIT 50
HIGHLIGHT (Message)
FACET Carrier
```

_Executes as:_ OpenSearch Query DSL · match Message · term Status · sort CreatedAt desc · highlight Message · terms facet Carrier

- `MATCH` is analyzed search, not a substring: "package never showed" and "no sign of my delivery" rank alongside the literal phrase.
- Exact `Status` filtering, `CreatedAt` sorting, and the `Carrier` facet all work because those fields carry `FILTER` mappings.

_Quote this example:_ https://dynostudio.dev/docs/dynostudio-bean-bark-support-search/#never-arrived

The highlights tell the story you expected: mangled street addresses, bounced couriers, Sam's ~1,900 orders. One of them is **Dana** — the very first customer you ever looked up, back when the whole company fit in one afternoon. Nadia sends the apology and the credit personally.

The facet tells the story **nobody was looking for**: over half the open "never arrived" cases predate Friday's job entirely — and they cluster on a single carrier. The bad batch sent you into the inbox; the term buckets found an operational problem that was hiding in prose all along. That's what a search projection is for: not just finding what you came for, but seeing the shape of what's there.

> **The projection is derived — the table is the record:** Search results may lag DynamoDB by seconds: a case closed moments ago can still read `OPEN` in the index. DynamoDB remains the **only system of record** — before acting on a single case, confirm it with an authoritative keyed read against the table. Use the projection to *find*, the table to *decide*.

**Recap**

- **`RESTORE TABLE` materializes a backup as a new table** — the live table and the snapshot are untouched, and recovery becomes an ordinary keyed read. The seatbelt you clip *before* is the whole reason there's an *after*.
- Free-form text doesn't belong in a `Scan` with `contains` — it belongs in a **derived OpenSearch projection**, scoped with a partial `WHERE`, shaped by `PROJECT`, and typed by `MAPPING`.
- **Provisioning is disclosed before consent**: the seed export, the ingestion pipeline, the IAM and S3 wiring, and the recurring cost — then readiness is tracked until the pipeline is `ACTIVE`.
- `MATCH` finds meaning-adjacent phrasings, `HIGHLIGHT` shows *why* each case matched, and a `FACET` can surface a trend you didn't come looking for. The projection finds; the **table of record decides**.

**Search-side check**

**1. Why not just `Scan` the table with `contains(Message, 'never arrived')`?**

- A Scan bills every item, and contains matches one literal substring — no analysis, no ranking, no highlights ✓ — free-text questions need analyzed search. The Scan reads the whole table for one rigid phrase; MATCH finds the phrasings customers actually use and shows the fragment that matched.
- It would work, it's just slower — it's not only slow — it's wrong. "package never showed" doesn't contain the substring "never arrived", so the cases you most need are exactly the ones it misses.
- Scans can't read string attributes — they can — a Scan with a contains filter is legal. The problem is cost (every item billed) and blindness (one literal substring, no ranking).

**2. A support case is edited so `EntityType` is no longer `'SupportCase'`. What happens in the scoped index?**

- The document is deleted from the projection — the scope is enforced, no stale copy stays behind ✓ — a partial WHERE is a contract, not a one-time filter: an item that changes out of scope is removed, so the index never serves ghost documents.
- The old copy stays until the next REINDEX — no — leaving an out-of-scope copy behind is exactly the ghost-document problem the feed is designed to prevent on MODIFY events.
- The whole pipeline stops and waits for you — scope changes are ordinary stream events, handled document by document. The pipeline keeps flowing.

**Try it yourself**

**1. Find the damaged bags**

Write the search for open cases about beans arriving damaged or torn, newest first, with the matched fragments visible and a status breakdown.

_Hint:_ Same door, different words — MATCH takes the phrase, HIGHLIGHT shows why each case matched, and FACET buckets any FILTER-mapped field.

_Solution:_ MATCH handles the phrasing variants ("bag was torn", "arrived damaged"); Status is FILTER-mapped, so it works as both an exact filter and a facet.

```sql
SELECT CaseId, CustomerName, Status, CreatedAt
FROM OPENSEARCH "bean-and-bark-support"
WHERE Message MATCH 'damaged torn bag'
ORDER BY CreatedAt DESC
LIMIT 50
HIGHLIGHT (Message)
FACET Status
```

**2. Scope a projection of your own**

Bean & Bark's wholesale team wants to search only wholesale order notes — items with `EntityType = 'WholesaleOrder'` — without indexing the rest of the table. Sketch the CREATE statement.

_Hint:_ PROJECT the fields the team reads, give the free-text field a SEARCH mapping and anything you'll filter, sort, or facet a FILTER mapping, and let the partial WHERE hold the scope.

_Solution:_ The partial WHERE keeps the projection to one entity type, and the out-of-scope-deletion rule keeps it honest over time.

```sql
CREATE OPENSEARCH INDEX "bb-wholesale-notes" ON "BeanAndBark"
  PROJECT (OrderId, Account, Notes, Status, CreatedAt)
  MAPPING (Notes SEARCH, Status FILTER, CreatedAt FILTER)
  ON INSERT, MODIFY, REMOVE
  WHERE EntityType = 'WholesaleOrder'
```

> **Where next?:** The inbox is triaged and the carrier conversation is booked. The [OpenSearch reference](https://dynostudio.dev/docs/dynostudio-opensearch-index/) has the full lifecycle — `ALTER`, `REINDEX`, partial-scope rules and the drop paths — and [backup & restore](https://dynostudio.dev/docs/dynostudio-backup-restore/) covers the snapshot statements. Next morning, a different clock is ticking: the board call starts in seventeen minutes, and the numbers live in two places at once. [Answer before the replica catches up](https://dynostudio.dev/docs/dynostudio-bean-bark-board-hybrid/).

---
_Source: https://dynostudio.dev/docs/dynostudio-bean-bark-support-search/ · DynoStudio Docs_