Etsy and Shopify profit and stockout dashboard

# the app

the app is a solo-seller dashboard that merges Etsy and Shopify orders, computes profit per product after platform fees, payment fees and shipping, and predicts which products will stock out within 30 days. It is a personal tool for one seller, not a multi-user app.

@REVIEW.md

## Stack

- Python 3.12, managed with uv
- DuckDB file at `data/shop.duckdb` (never commit it)
- Streamlit UI in `src/the app/app.py`
- pytest, ruff, mypy

## Commands

- Install deps: `uv sync`
- Tests: `uv run pytest -q`
- Lint and format check: `uv run ruff check . && uv run ruff format --check .`
- Types: `uv run mypy src`
- Ingest fixtures: `uv run python -m the app.ingest --source fixtures`
- Run dashboard: `uv run streamlit run src/the app/app.py`
- Full check: the `verify` skill

## Layout

- `src/the app/`: `ingest.py`, `models.py`, `fees.py`, `forecast.py`, `app.py`
- `sql/`: DuckDB view and table SQL, loaded by name
- `tests/`: pytest suite, no network
- `fixtures/`: anonymized sample Etsy and Shopify exports
- `scripts/smoke.py`: end-to-end check into a temp DuckDB
- `SPEC.md` (scope and contracts), `PLAN.md` (phases), `REVIEW.md` (review rules)

## Data model

- Unified tables: `orders`, `order_lines`, `products`, `inventory_snapshots`, `fee_rules`
- Staging tables: `raw_etsy_*` and `raw_shopify_*`, kept as received
- `product_map` links platform listing or SKU IDs to `products`
- Money is stored as integer cents plus a currency code. Never use floats for money.
- Timestamps are normalized to UTC on ingest.
- Ingest is idempotent: upsert on `(platform, order_id, line_id)`.

## Gotchas

- Etsy and Shopify use different SKU and listing IDs. Always go through `product_map`.
- Etsy fees: listing, transaction, payment processing, offsite ads.
- Shopify fees: plan, payment processing, app fees.
- Refunds and partial refunds reduce revenue. Fees are often not refunded, so check the fee rules.
- Shipping cost paid and shipping charged are separate values. Do not net them silently.
- Discounts reduce revenue at the line level before allocation.

## Forecast rule

- Start with trailing-window velocity: a blend of 28-day and 7-day averages, plus lead time and safety stock.
- Output `days_of_cover` and `stockout_date`.
- No ML until a baseline backtest exists and beats the baseline.

## Conventions

- Pure functions for fee and profit math.
- SQL lives in `sql/*.sql` and is loaded by name, not inline in Python.
- Type hints everywhere.
- Tests make no network calls and are deterministic.
- Golden-number tests use hand-verified values. Recompute by hand before changing an expected value.

## Secrets

- Keys come only from env vars: `ETSY_API_KEY`, `SHOPIFY_ACCESS_TOKEN`.
- `.env.example` holds placeholders only: `ETSY_API_KEY=YOUR_API_KEY`, `SHOPIFY_ACCESS_TOKEN=YOUR_ACCESS_TOKEN`.
- Never commit `.env`, `data/`, `*.duckdb`, or real exports.

## Workflow

- Explore, then plan, then code, then run the `verify` skill before saying a task is done.
- Use `/clear` between unrelated tasks and `/compact` at natural breaks.
- Give every change a way to check itself: a test, a golden number, or the smoke run.

## Do not

- Use floats for money.
- Delete `data/shop.duckdb` without asking.
- Make live API calls unless the user asks.
- Commit or push without asking.
- Put real customer names, addresses or emails in fixtures, logs or tests.

---
name: test-runner
description: Use proactively to run tests and lint for the app and return a short PASS/FAIL summary with only the failing test names and key assertion lines.
model: haiku
tools: Bash, Read, Grep, Glob
disallowedTools: Edit, Write
maxTurns: 12
---

You run the the app verify steps and report results. You never edit files.

Run these commands in order, from the repo root:

1. `uv run ruff check .`
2. `uv run ruff format --check .`
3. `uv run mypy src`
4. `uv run pytest -q -x`
5. `uv run python scripts/smoke.py`

If `scripts/smoke.py` does not exist yet, skip step 5 and say it was skipped.

Report format:

- One line per step: `PASS` or `FAIL`, then the step name.
- For each FAIL, list only the failing test names and the key assertion lines (the `assert` line and the expected versus actual values).
- Trim any other output. Never paste full logs.
- Limit each failure to 30 lines.
- Do not suggest fixes beyond one sentence naming the likely cause.

If a step fails, stop there and report it. Do not run later steps unless the failure is only in lint, in which case still run the tests.

{
  "permissions": {
    "allow": [
      "Bash(uv run pytest:*)", "Bash(uv run ruff:*)", "Bash(uv run mypy:*)", "Bash(uv sync)",
      "Bash(uv run python -m the app:*)", "Bash(uv run python scripts/*)",
      "Bash(git status)", "Bash(git diff:*)", "Bash(git log:*)"
    ],
    "ask": [
      "Bash(git push:*)", "Bash(git commit:*)", "Bash(uv add:*)", "Bash(pip install:*)"
    ],
    "deny": [
      "Read(./.env)", "Read(./.env.*)", "Read(./data/raw/**)",
      "Bash(rm -rf:*)", "Bash(curl:*)", "Bash(wget:*)"
    ],
    "blockWritesOutsideWorkingDirectories": true
  },
  "env": {
    "CLAUDE_CODE_SUBAGENT_MODEL": "haiku"
  },
  "subagentPromptCacheTtl": "1h",
  "hooks": {
    "PostToolUse": [
      {
        "matcher": "Edit|Write",
        "hooks": [
          {
            "type": "command",
            "command": "f=$(cat | sed -n 's/.*\"file_path\": *\"\\([^\"]*\\.py\\)\".*/\\1/p'); if [ -n \"$f\" ] && command -v uv >/dev/null 2>&1; then uv run ruff format \"$f\" >/dev/null 2>&1; fi; exit 0"
          }
        ]
      }
    ]
  }
}

---
name: fee-rules
description: Reference for Etsy and Shopify fee types, refund behaviour and order-level fee allocation in the app.
when_to_use: Use when editing fee, profit or allocation code, fee_rules data, or any test in tests/test_fees.py.
disable-model-invocation: false
---

# Fee rules

This skill explains how the app models platform fees. Rates are not stored in code. They live in the `fee_rules` table, so the seller can change them without a code change.

## Rates are configurable

- Every rate in this document is a placeholder for `fee_rules` rows. Do not hard-code a rate in Python or SQL.
- Confirm current rates from the seller's own Etsy and Shopify statements, not from memory. Platform pricing changes, and fee tiers may differ by country.
- Record the effective date on each `fee_rules` row. Historical orders use the rule in effect on the order date.

## Fee types

### Etsy

| Fee type | Basis | Refundable |
|---|---|---|
| listing | per listing, per renewal | configurable |
| transaction | percent of item price and shipping | configurable |
| payment processing | percent plus fixed, per order | configurable |
| offsite ads | percent of order attributed to ads | configurable |

### Shopify

| Fee type | Basis | Refundable |
|---|---|---|
| plan | monthly subscription, spread over orders | not applicable |
| payment processing | percent plus fixed, per order | configurable |
| app fees | per app, per month or per order | configurable |

Plan and app fees are not per-order costs. The engine spreads them over orders in the period using the method in `fee_rules`, and the spread must be recorded so totals reconcile to the statement.

## Refund rules

- A refund reduces revenue by the refunded item amount.
- A partial refund reduces revenue by the refunded amount only.
- Discounts reduce revenue before refunds are applied, so the refund amount is the discounted line amount.
- Fees marked non-refundable in `fee_rules` stay on the order. Fees marked refundable reverse in proportion to the refund.
- If a refund's refundable status is unknown, treat the fee as non-refundable. Flag the row in the data health page.
- Shipping charged and shipping label cost are separate. Refunding shipping charged does not refund the label cost.

## Allocation across lines

Order-level amounts (payment processing, shipping label cost, transaction fees charged on the order total) are allocated to lines by revenue share.

Algorithm:

1. Compute each line's revenue after discounts and refunds.
2. For each line, compute `share = total_amount * line_revenue / total_revenue` in integer arithmetic. Use `floor` on the integer division.
3. Sum the floors. The remainder `total_amount - sum(floors)` is less than the number of lines.
4. Give one extra cent to each line with the largest fractional remainder, in order. Break ties by ascending `line_id`.
5. Check that the allocated lines sum exactly to `total_amount`. If not, raise an error. Do not silently absorb the difference.

Rules:

- Do not use floats at any step. Use integer cents and integer division only.
- If total revenue is zero (a fully refunded order), allocate the amount to lines by quantity and flag the order.
- Rounding happens once, at allocation. Do not round intermediate values.

## Worked example

Order with two lines and one order-level fee of 333 cents:

| Line | Revenue (cents) | Exact share | Floor | Remainder |
|---|---|---|---|---|
| L1 | 3000 | 199.8 | 199 | 0.8 |
| L2 | 2000 | 133.2 | 133 | 0.2 |

Sum of floors is 332. Remainder is 1 cent. L1 has the largest fraction, so L1 gets the extra cent.

Result: L1 = 200 cents, L2 = 133 cents, total 333 cents.

This matches the test in `tests/test_fees.py`. If the numbers differ, the hand calculation is the source of truth. Check the test before changing this example.

## Tests required for each fee type

- One test per platform fee type.
- One test for a full refund and one for a partial refund.
- One test for a discount applied before a refund.
- One test for a zero-revenue order.
- One test for a multi-currency order: each currency is allocated separately, with no conversion inside the fee engine.

## Reference file

The full list of fee types, rates and effective dates will grow. Keep the full table in `.claude/skills/fee-rules/reference.md` and link to it from here. Keep this file as the short guide.

---
name: stockout-forecast
description: Method, edge cases and backtest procedure for the 30-day stockout forecast that combines Etsy and Shopify sales per product.
when_to_use: Use when editing forecast code, stock risk queries, the stockout backtest, or the Stock risk dashboard page.
disable-model-invocation: false
---

# Stockout forecast

The forecast answers one question: for each product, will on-hand stock run out within 30 days, and when should the seller reorder?

## Baseline method

Use a simple velocity baseline. Do not add ML until the backtest shows it beats the baseline.

1. Build a daily units series per product, combining Etsy and Shopify sales. Map both platforms to `products` through `product_map`.
2. Exclude days when the product was out of stock, using `inventory_snapshots`. A day with zero stock is not a day of zero demand.
3. Compute:
   - `velocity_7` = average daily units over the last 7 in-stock days.
   - `velocity_28` = average daily units over the last 28 in-stock days.
   - `velocity` = `0.5 * velocity_7 + 0.5 * velocity_28`. The 0.5 weights are the default and live in one constant. Change them only after the backtest.
4. Use integer units. Keep velocity as a decimal value for the calculation, and never use it for money.

## Output fields

- `days_of_cover = on_hand / velocity`. If `velocity` is zero, set it to null and flag as no demand.
- `stockout_date = as_of_date + floor(days_of_cover)`.
- `reorder_by = stockout_date - lead_time_days - safety_days`.
- `flag_30d = stockout_date <= as_of_date + 30`.
- `confidence` as described below.

If `reorder_by` is already in the past, the product needs an order now. Show it at the top of the Stock risk page.

## Confidence rules

Confidence depends on data volume. Use these starting thresholds and tune them after the backtest:

| Level | Days of in-stock history | Units sold in last 28 days |
|---|---|---|
| high | 56 or more | 50 or more |
| med | 14 to 55 | 10 to 49 |
| low | under 14 | under 10 |

The lowest of the two levels is the reported confidence. Show the reason next to the badge, for example "low: 9 days of history".

## Edge cases

- Zero sales: velocity is zero. Set `days_of_cover` to null. Do not report a stockout date. Show as "no demand" with the last sale date.
- New products with under 14 days of history: confidence is low. Do not use a 28-day window that includes days before the listing existed.
- Stockout already occurred: if on-hand is zero, `stockout_date` is the as-of date. Do not divide by velocity to estimate a past stockout. Mark the product out of stock.
- Out-of-stock periods depress velocity. This is why they are excluded in step 2. If the exclusion removes all data, treat as no history.
- Seasonal spikes: flag them, do not model them. If a day's sales exceed 3 times the 28-day median, mark it as a spike in the output. Do not remove the spike from the velocity in v1, but show the spike flag.
- Bundles and variants: a bundle consumes component stock. Decrement components by bundle quantity before forecasting components. If a component mapping is missing, list it on the Data health page and skip the component.

## Backtest procedure

Run the backtest with `uv run python scripts/backtest.py` once the script exists. It must:

1. Hold out the last 30 days of fixture data.
2. Run the baseline using only data before the hold-out start.
3. For each product, compare the predicted `stockout_date` with the actual first day of zero on-hand in the hold-out period.
4. Print:
   - MAE of predicted stockout days versus actual, over products where both exist.
   - Precision and recall of `flag_30d` against actual stockouts within the hold-out window.
5. Use fixtures only. No network calls.

Record the numbers in the commit message or the PLAN.md phase notes when the baseline changes.

## Rule for ML

Only consider an ML model if it beats the baseline on MAE and on recall in the same backtest, on the same hold-out data. A model that wins on one metric and loses on the other does not qualify. Keep the baseline as the fallback.

## Checks before finishing

- `uv run pytest -q` includes hand-computed stockout fixtures.
- The Stock risk page sorts by `stockout_date`, then `reorder_by`.
- Confidence and reason appear on every row.
- No float is used for money anywhere in this path.

---
name: verify
description: Run after any code change to prove that lint, format, types, tests and the smoke run all pass for the app.
when_to_use: Use before declaring a task done, before committing, or after editing Python, SQL or fixtures.
disable-model-invocation: false
---

# Verify

Run these steps in order. Stop at the first failure, fix the root cause, then rerun from the failing step.

## Steps

1. Lint: `uv run ruff check .`
2. Format check: `uv run ruff format --check .`
3. Types: `uv run mypy src`
4. Tests: `uv run pytest -q -x`
5. Smoke run: `uv run python scripts/smoke.py`

The smoke script ingests `fixtures/` into a temporary DuckDB file and prints profit and stockout summaries. It never touches `data/shop.duckdb`.

## Phase 1 exception

If `scripts/smoke.py` does not exist yet, skip step 5 and say so in the report. Do not create the script from this skill.

## Report format

Print one line per step:

```
ruff check        PASS
ruff format       PASS
mypy              FAIL
pytest            not run
smoke             not run
```

For each FAIL, include the first failing output, trimmed to 30 lines. Put the most relevant lines first, usually the error message and the file and line.

## Rules

- Fix the root cause. Do not add `# type: ignore`, `# noqa`, `skip` or `xfail` to get a green run unless the user approves it.
- Never loosen or delete a test to pass.
- Golden-number test failures: recompute the expected value by hand from the fixture before changing it. If the hand calculation disagrees with the code, the code is wrong. If the expected value should change for a real reason, ask the user first.
- Do not run `ruff format` silently to hide a format failure from code you did not intend to change. Run it, then show the diff in the report.
- Do not make network calls. If a step needs one, it is a bug in the test.
- If a step passes only after a retry with no code change, report it as FLAKY and investigate before saying done.

## When to run

- After every code change, even small ones.
- Before saying a task is done.
- Before any commit. Commits need the user's approval, so show the report first.

# Install the app

For a daily Claude Code user. Allow about 30 minutes.

## Prerequisites

- Claude Code 2.1.296 or newer (`claude --version`)
- Python 3.12
- uv
- git

## Steps

1. Create the repo folder and copy in the files. Put CLAUDE.md, SPEC.md, PLAN.md, REVIEW.md and INSTALL.md in the repo root, and the `.claude/` folder beside them.
2. Run `git init`, then make the first commit.
3. Set up the Python project:
   ```
   uv init
   uv add duckdb pandas streamlit
   uv add --dev pytest ruff mypy
   ```
4. Create `.env.example` with placeholders:
   ```
   ETSY_API_KEY=YOUR_API_KEY
   SHOPIFY_ACCESS_TOKEN=YOUR_ACCESS_TOKEN
   ```
   Add `.env`, `data/` and `*.duckdb` to `.gitignore`.
5. Export sample data and anonymize it into `fixtures/`.
   - Etsy: orders, listings, payment ledger.
   - Shopify: orders, products, inventory levels.
   - Remove customer names, addresses and emails before committing.
6. Start Claude Code in the repo with `claude`.
7. Install the plugins:
   ```
   /plugin install code-review@claude-plugins-official
   /plugin install feature-dev@claude-plugins-official
   /plugin install commit-commands@claude-plugins-official
   ```
8. Paste the Phase 1 `/goal` prompt from PLAN.md to start.
9. Run the phases in order. Use `/clear` before each new phase and `/compact` at natural breaks. Check spend with `/usage`.

## Notes

- CLAUDE.md imports REVIEW.md with `@REVIEW.md`. `/code-review` reads CLAUDE.md but not REVIEW.md, so the import is what applies the review rules.
- Never commit `.env`, `data/` or real exports.

# the app build plan

Five phases. Each ends with a measurable state and a `/goal` prompt you can paste into Claude Code. Run `/clear` before each phase and `/compact` at natural breaks. Check spend with `/usage`.

## Phase 1: Foundation

- End state: uv project, DuckDB schema, fixtures, and a working verify skill.
- Check: `uv run pytest -q`, ruff and mypy pass, and schema tests exist.
- Constraints: no floats for money, no network, no schema break without a migration note.
- Turn bound: 25.

Prompt:

```
/goal Phase 1 Foundation for the app (Python 3.12, DuckDB, Streamlit). Read CLAUDE.md, SPEC.md and PLAN.md first. Explore, then propose a short plan before editing. End state: (1) uv project with src/the app/ package and tests/ directory; (2) sql/schema.sql defining raw_etsy_orders, raw_shopify_orders, orders, order_lines, products, product_map, inventory_snapshots, fee_rules, with money as integer cents plus currency; (3) src/the app/db.py creates the DuckDB file from schema.sql and is idempotent; (4) fixtures/ holds about 20 anonymized orders per platform, including a refund, a discount and a multi-line order; (5) tests/test_schema.py asserts every table and its columns exist and that creating twice is safe; (6) the verify skill runs, with scripts/smoke.py stubbed to exit 0 and print the table list. Check: `uv run ruff check .`, `uv run ruff format --check .`, `uv run mypy src` and `uv run pytest -q` all pass; paste their output in the conversation. Constraints: no floats for money; no network calls; no real customer data; add no dependencies beyond duckdb, pandas, streamlit, pytest, ruff, mypy; leave .claude/settings.json alone unless fixing a clear error; do not commit. Stop after at most 25 turns and list what passed and what is left.
```

## Phase 2: Ingest

- End state: Etsy and Shopify parsers load raw tables, then unified tables, with idempotent upserts, a product_map, and an unmapped SKU report.
- Check: ingest run twice on fixtures gives the same row counts.
- Constraints: no live API, cents not floats, schema changes recorded in SPEC.md.
- Turn bound: 30.

Prompt:

```
/goal Phase 2 Ingest for the app. Read CLAUDE.md, SPEC.md and REVIEW.md first. End state: (1) Etsy and Shopify export parsers under src/the app/ingest.py load fixtures/ into raw_etsy_* and raw_shopify_* tables, then normalize into orders, order_lines and product_map; (2) products are loaded from the manual costs CSV; (3) upserts key on (platform, order_id, line_id) so reruns are idempotent; (4) timestamps are stored as UTC; (5) unmapped SKUs are written to a report and counted; (6) each run prints rows read, loaded and rejected, and the counts reconcile; (7) `uv run python -m the app.ingest --source fixtures` works. Check: run the ingest command twice and show that row counts are identical both times; `uv run pytest -q`, ruff and mypy pass. Constraints: no live API calls; cents not floats; no silent row drops; any schema change gets a migration note in SPEC.md. Stop after at most 30 turns and list what passed and what is left.
```

## Phase 3: Profit engine

- End state: fee rules, allocation of shipping and fees, refunds, unit costs, and a profit-per-product SQL view with golden-number tests.
- Check: tests match hand-computed totals to the cent.
- Constraints: rounding once at allocation, no SELECT * in views, no live API.
- Turn bound: 30.

Prompt:

```
/goal Phase 3 Profit engine for the app. Read CLAUDE.md, SPEC.md (Profit definition) and REVIEW.md first. End state: (1) fee_rules seeded with Etsy fees (listing, transaction, payment processing, offsite ads) and Shopify fees (plan, payment processing, app fees), rates stored as configurable rows; (2) pure functions in src/the app/fees.py for fee and profit math; (3) order-level fees and shipping allocated to lines by revenue share, with remainder cents assigned deterministically to the largest-revenue line, ties by lower line_id; (4) refunds and partial refunds reduce revenue, and fees follow the refundable flag in fee_rules; (5) a profit-by-product view in sql/ with explicit column lists; (6) tests/test_fees.py has one test per fee type; (7) tests/test_profit.py has golden-number tests with totals hand-computed on the fixtures. Check: `uv run pytest -q` passes with totals matching hand-computed values to the cent; ruff and mypy pass. Constraints: no floats anywhere in money paths; no SELECT * in views; no live API calls. If a golden value must change, recompute it by hand first and ask me. Stop after at most 30 turns and list what passed and what is left.
```

## Phase 4: Stockout forecast

- End state: velocity baseline, lead time, safety stock, and a backtest on fixtures with a simple error metric.
- Check: the backtest script prints MAE and tests pass.
- Constraints: no ML unless it beats the baseline in the backtest, deterministic, no network.
- Turn bound: 30.

Prompt:

```
/goal Phase 4 Stockout forecast for the app. Read CLAUDE.md, SPEC.md (Stockout prediction) and .claude/skills/stockout-forecast/SKILL.md first. End state: (1) src/the app/forecast.py computes per-product velocity as a blend of 7-day and 28-day average daily units, excluding out-of-stock days and combining both platforms; (2) it outputs days_of_cover, stockout_date, reorder_by and confidence low/med/high; (3) zero-sales items, items with under 14 days of history, and already-stocked-out items are handled explicitly; (4) scripts/backtest.py holds out the last 30 days of fixtures and prints MAE plus stockout precision and recall; (5) tests cover each edge case with hand-computed expected values. Check: `uv run python scripts/backtest.py` prints MAE; `uv run pytest -q`, ruff and mypy pass. Constraints: no ML unless it beats the baseline in the backtest; deterministic output; no network calls. Stop after at most 30 turns and list what passed and what is left.
```

## Phase 5: Dashboard and polish

- End state: Streamlit pages, data health page, caching, and a smoke run script.
- Check: `uv run python scripts/smoke.py` exits 0 and the app boots headless.
- Constraints: no live API, no real data in logs, no schema break without a note.
- Turn bound: 30.

Prompt:

```
/goal Phase 5 Dashboard and polish for the app. Read CLAUDE.md, SPEC.md (Dashboard pages and Acceptance criteria) first. End state: (1) Streamlit app in src/the app/app.py with Overview, Products, Stock risk and Data health pages as specified in SPEC.md; (2) query functions wrapped in st.cache_data; (3) scripts/smoke.py ingests fixtures into a temp DuckDB and prints profit and stockout summaries, exiting 0; (4) a script generates 50,000 synthetic order lines in a temp DB and times the Overview load, which must finish under 3 seconds. Check: `uv run python scripts/smoke.py` exits 0; `uv run streamlit run src/the app/app.py --server.headless true` boots without errors, then stop it; the verify skill passes end to end. Constraints: no live API calls; no real customer data in fixtures, logs or screenshots; no schema break without a note in SPEC.md. Stop after at most 30 turns and list what passed and what is left.
```

## Notes

- Phases build on each other. Do not start Phase 3 until Phase 2's check passes.
- If a `/goal` stops at its turn bound, read the report, fix the gap, and rerun that phase's prompt.

# Review rules

Imported by CLAUDE.md, so /code-review sees these rules.

## Money
- Any float arithmetic on currency is a blocker.
- Store money as integer cents plus a currency code.
- Round once, at allocation. Assign remainder cents deterministically.

## Fees
- Each platform fee type has a test.
- Check refunds, partial refunds, discounts and multi-currency.

## Ingest
- Ingest is idempotent: upsert on (platform, order_id, line_id).
- Timestamps are UTC.
- No silent row drops. Log counts and reconcile them.
- Surface unmapped SKUs.

## SQL
- No SELECT * in views. Use explicit column lists.
- Joins must not fan out and duplicate revenue. Check row counts in tests.

## Forecast
- Handle zero-sales items, new items with under 14 days of history, stockouts that already occurred, and out-of-stock periods that depress velocity.

## Security
- No secrets in code, fixtures or logs.
- No real customer data (names, addresses, emails) in fixtures or logs.

## Tests
- Golden numbers are hand-verified.
- No network calls. Tests are deterministic.

## Style
- ruff and mypy clean.
- Small functions, no dead code.

# the app spec

## Users and goal

- One solo seller who sells on Etsy and Shopify.
- Goal: know which products make money after every fee and cost, and which products will sell out in the next 30 days.

## Scope for v1

- Inputs: CSV or JSON exports from Etsy (orders, listings, payment ledger) and Shopify (orders, products, inventory levels).
- Inputs: manual costs CSV with columns `product_id, unit_cost, supplier_lead_time_days, reorder_qty`.
- Live API sync is v2. Development and tests use `fixtures/` only.
- Ingest is idempotent: rerunning the same export gives the same row counts.

## Unified schema

- Money is INTEGER cents plus a currency code (CHAR(3)). No floats for money.
- Timestamps are UTC.
- No buyer names, addresses or emails are stored.

### orders

- platform VARCHAR (`etsy` or `shopify`)
- order_id VARCHAR
- ordered_at_utc TIMESTAMP
- buyer_country CHAR(2), nullable
- currency CHAR(3)
- shipping_charged_cents BIGINT
- shipping_label_cents BIGINT
- platform_fee_cents BIGINT
- payment_fee_cents BIGINT
- fee_source VARCHAR (`ledger` or `computed`)
- Primary key: (platform, order_id)

### order_lines

- platform VARCHAR, order_id VARCHAR, line_id VARCHAR
- external_listing_id VARCHAR (Etsy listing or Shopify product ID)
- external_sku VARCHAR, nullable
- product_id VARCHAR, nullable (NULL means unmapped)
- quantity INTEGER
- unit_price_cents BIGINT
- line_discount_cents BIGINT
- line_refund_cents BIGINT
- Primary key: (platform, order_id, line_id)

### products

- product_id VARCHAR, primary key
- title VARCHAR
- unit_cost_cents BIGINT, nullable (NULL shows in Data health)
- currency CHAR(3)
- supplier_lead_time_days INTEGER
- reorder_qty INTEGER
- safety_days INTEGER, default 7
- is_bundle BOOLEAN, default false

### product_map

- platform VARCHAR, external_id VARCHAR (listing ID or SKU)
- product_id VARCHAR
- Primary key: (platform, external_id)
- Etsy and Shopify IDs differ, so this table is the only bridge between them.

### inventory_snapshots

- product_id VARCHAR, platform VARCHAR, snapshot_date DATE, on_hand INTEGER
- Primary key: (product_id, platform, snapshot_date)

### fee_rules

- platform VARCHAR, fee_type VARCHAR, country CHAR(2), nullable
- effective_from DATE
- rate_bps INTEGER, nullable
- fixed_cents BIGINT, nullable
- refundable BOOLEAN
- Rates are seed rows, never hardcoded in code.

### raw staging tables

- raw_etsy_* and raw_shopify_*: source_file, row_number, loaded_at_utc, payload JSON.
- Staging only. The dashboard never reads them.

## Profit definition

Per order line:

- gross = unit_price_cents x quantity
- revenue = gross minus line discounts minus line refunds
- profit = revenue minus allocated platform fees minus allocated payment fees minus allocated shipping label cost minus unit_cost_cents x quantity

Allocation:

- Order-level fees and shipping are split across lines by revenue share.
- Remainder cents go to the line with the largest revenue; ties go to the lower line_id.
- Rounding happens once, at allocation.

Product totals sum line results. Margin = profit / revenue, null when revenue is zero.

## Stockout prediction definition

- Velocity: blend of 7-day and 28-day average daily units, excluding days out of stock, combined across both platforms per product.
- days_of_cover = on_hand / velocity.
- stockout_date = today + days_of_cover.
- Flag the product if stockout_date falls within 30 days.
- reorder_by = stockout_date minus supplier_lead_time_days minus safety_days.
- Confidence:
  - low: under 14 days of sales history
  - high: 60 or more days of history and 20 or more units sold in the window
  - med: everything else

## Dashboard pages

- Overview: combined revenue, profit and margin; split by platform and by week.
- Products: sortable profit table with margin, units and fee breakdown.
- Stock risk: products sorted by stockout date, with reorder-by date and confidence.
- Data health: unmapped SKUs, missing costs, row count reconciliation.

## Non-goals

- Multi-user auth.
- Accounting or tax.
- Other marketplaces.
- ML beyond the baseline in v1.

## Acceptance criteria

- The fixture dataset produces profit figures asserted to the cent in tests.
- The stockout list matches hand-computed fixture results.
- The dashboard loads in under 3 seconds on 50,000 order lines.
- Ingest reconciles: rows read = rows loaded + rows rejected, with rejects logged.
- Rerunning ingest gives identical row counts.

## Open questions

- Fee tiers per country.
- Currency conversion: store original currency only, or convert, and at what rate?
- Bundles and variants: how do they draw down component stock?
- Does shipping charged count as revenue?
- Do refunded units return to cost basis?