# Etsy Seller OS (Premium Edition)

## 1. Executive Dashboard
A command center for your business. This page aggregates data from all underlying databases to provide real-time financial health, operational status, and inventory alerts.

**Visual Setup:**
*   **Cover Image:** Clean, abstract geometric pattern (e.g., "Bauhaus" style).
*   **Icon:** 📊 (Bar Chart).

**Dashboard Views (Linked Database Blocks):**
1.  **Financial Snapshot (Table View):** A filtered view of the `Orders` database showing only "This Month". Columns: `Date`, `Order ID`, `Net Profit`, `Status`.
2.  **Low Stock Alert (Board View):** A filtered view of the `Materials` database where `Current Stock` is less than `Reorder Level`. Grouped by `Category`.
3.  **Pending Fulfillment (List View):** A filtered view of `Orders` where `Status` is "Processing". Sorted by `Date` (Oldest first).
4.  **Monthly P&L (Table View):** A view of the `Expenses` database for the current month.

**Key Metrics (Formulas calculated in database views):**
*   **Total Revenue:** Sum of `Total Revenue` in `Orders`.
*   **True Net Profit:** Sum of `Net Profit` in `Orders` minus Sum of `Amount` in `Expenses`.
*   **Profit Margin:** `(True Net Profit / Total Revenue) * 100`.

---

## 2. Customer Relationship Management (CRM)
Track buyer behavior to increase retention and Lifetime Value (LTV).

**Database Name:** `Customers DB`
**Icon:** 👥 (People)

**Properties Setup:**
*   **Customer Name** (Title)
*   **Email** (Email)
*   **Country** (Select)
*   **Total Orders** (Rollup): Relation to `Orders DB` -> Rollup `Order ID` (Count).
*   **Total Spent** (Rollup): Relation to `Orders DB` -> Rollup `Total Revenue` (Sum).
*   **LTV Status** (Formula): Categorize customer value.
    ```javascript
    if(prop("Total Spent") > 500, "VIP", if(prop("Total Spent") > 100, "Regular", "New"))
    ```
*   **Last Purchase Date** (Rollup): Relation to `Orders DB` -> Rollup `Date` (Max).

**Workflow:**
When a new order arrives, check if the email exists. If not, create a new Customer entry and link it to the Order.

---

## 3. Product Catalog (Listings)
Define your finished goods with built-in fee estimation and COGS logic.

**Database Name:** `Products DB`
**Icon:** 🛍️ (Shopping Bag)

**Properties Setup:**
*   **Product Name** (Title)
*   **Etsy Listing ID** (Text)
*   **Sale Price** (Number): Currency format ($).
*   **Status** (Select): Active, Draft, Discontinued, Out of Stock.
*   **Materials Used** (Relation): Link to `Materials DB`.
*   **Qty per Unit** (Number): *Note: This is handled inside the Relation or as a separate lookup.*
*   **COGS per Unit** (Formula): Calculates material cost.
    ```javascript
    prop("Materials Used").map(current.prop("Cost per Unit")).sum()
    ```
    *(Note: If using a Rollup, ensure the Rollup property sums the "Cost per Unit" from the related materials).*
*   **Est. Etsy Fees** (Formula): 6.5% Transaction fee + Listing fee ($0.20).
    ```javascript
    (prop("Sale Price") * 0.065) + 0.20
    ```
*   **Gross Margin** (Formula): `prop("Sale Price") - prop("COGS per Unit") - prop("Est. Etsy Fees")`.

---

## 4. Materials & Inventory (Automated)
The foundation of your stock management. This database connects to the Stock Ledger to automate stock levels.

**Database Name:** `Materials DB`
**Icon:** 🧶 (Yarn)

**Properties Setup:**
*   **Item Name** (Title)
*   **SKU** (Text)
*   **Category** (Select): Raw Material, Packaging, Branding.
*   **Cost per Unit** (Number): Cost for a single unit (e.g., per jar, per label).
*   **Supplier** (Relation): Link to `Suppliers DB`.
*   **Reorder Level** (Number): Minimum stock before alert.
*   **Current Stock** (Rollup): **CRITICAL FOR AUTOMATION.**
    *   *Source:* Relation to `Stock Ledger DB`.
    *   *Property:* `Qty Change`.
    *   *Calculation:* Sum.
*   **Stock Status** (Formula): Visual indicator.
    ```javascript
    if(prop("Current Stock") <= prop("Reorder Level"), "🔴 Order Now", "🟢 In Stock")
    ```

**Logic:**
You never manually edit `Current Stock`. You add entries to the `Stock Ledger DB` (Restocks increase stock, Orders decrease stock), and this Rollup updates automatically.

---

## 5. Stock Ledger (Inventory Engine)
This database acts as the engine for inventory deduction. It records every movement of stock in and out.

**Database Name:** `Stock Ledger DB`
**Icon:** 📒 (Ledger)

**Properties Setup:**
*   **Transaction ID** (Title): Auto-generated or timestamp.
*   **Date** (Date)
*   **Material** (Relation): Link to `Materials DB`.
*   **Transaction Type** (Select): Restock, Sale, Waste/Adjustment.
*   **Qty Change** (Number): **Positive for Restock, Negative for Sale.**
    *   *Example:* If you sell 1 jar, enter `-1`. If you buy 100 jars, enter `100`.
*   **Reference Order** (Relation): Optional link to `Orders DB` for traceability.

**Workflow:**
When you ship an order, open this database, create a new entry, select the Material used, set Type to "Sale", and enter the quantity as a negative number (e.g., `-2`). The `Materials DB` will instantly update.

---

## 6. Order Management (Financial Hub)
Central hub for sales, fees, and profit calculation.

**Database Name:** `Orders DB`
**Icon:** 📦 (Package)

**Properties Setup:**
*   **Order ID** (Title): Etsy Order #.
*   **Date** (Date)
*   **Customer** (Relation): Link to `Customers DB`.
*   **Product Sold** (Relation): Link to `Products DB`.
*   **Quantity** (Number)
*   **Status** (Select): New, Processing, Shipped, Delivered, Returned.
*   **Shipping Charged** (Number): What the customer paid.
*   **Actual Shipping Cost** (Number): Postage cost.
*   **Total Revenue** (Formula): `prop("Product Sold").Sale Price * prop("Quantity") + prop("Shipping Charged")`.
*   **Total COGS** (Rollup): Pull `COGS per Unit` from `Product Sold` and multiply by `Quantity` (or calculate in formula).
*   **Listing Fee** (Number): Usually $0.20.
*   **Transaction Fee** (Number): 6.5% of (Sale Price + Shipping Charged).
*   **Payment Processing** (Number): 3% + $0.25 (approximate).
*   **Offsite Ads Fee** (Number): 12-15% (if applicable).
*   **Total Fees** (Formula): `prop("Listing Fee") + prop("Transaction Fee") + prop("Payment Processing") + prop("Offsite Ads Fee")`.
*   **Net Profit** (Formula): `prop("Total Revenue") - prop("Total COGS") - prop("Actual Shipping Cost") - prop("Total Fees")`.

---

## 7. Expenses & Overhead
Track business costs not tied to specific products (software, ads, electricity).

**Database Name:** `Expenses DB`
**Icon:** 💸 (Money with Wings)

**Properties Setup:**
*   **Expense Name** (Title)
*   **Date** (Date)
*   **Category** (Select): Software, Marketing, Tools, Office, Education.
*   **Amount** (Number): Currency.
*   **Payment Method** (Select): Credit Card, PayPal, Bank Transfer.
*   **Receipt Link** (URL): Link to Google Drive/Dropbox scan.
*   **Recurring?** (Checkbox): For monthly subscriptions.

---

## 8. Supplier Directory
Manage vendor relationships and lead times.

**Database Name:** `Suppliers DB`
**Icon:** 🏭 (Factory)

**Properties Setup:**
*   **Company Name** (Title)
*   **Website** (URL)
*   **Contact Person** (Text)
*   **Email** (Email)
*   **Lead Time (Days)** (Number)
*   **Min Order Qty** (Text)
*   **Notes** (Text)

---

## System Setup Guide

Follow this sequence to build the relational logic correctly.

1.  **Create Databases:** Create the 8 databases listed above in this order: `Customers`, `Suppliers`, `Materials`, `Products`, `Stock Ledger`, `Orders`, `Expenses`.
2.  **Connect Relations:**
    *   **Products:** Link "Materials Used" to `Materials DB`.
    *   **Materials:** Link "Supplier" to `Suppliers DB`. Link "Stock Ledger" (Relation) to `Stock Ledger DB`.
    *   **Stock Ledger:** Link "Material" to `Materials DB`. Link "Reference Order" to `Orders DB`.
    *   **Orders:** Link "Customer" to `Customers DB`. Link "Product Sold" to `Products DB`.
3.  **Configure Inventory Logic:**
    *   In `Materials DB`, ensure `Current Stock` is a Rollup looking at `Stock Ledger DB` -> `Qty Change` (Sum).
    *   Test it: Create a Material (e.g., "Jar"). In `Stock Ledger`, add a "Restock" of +10. Check `Materials` — it should say 10. Add a "Sale" of -2. It should say 8.
4.  **Configure Financials:**
    *   In `Orders DB`, ensure the `Net Profit` formula accounts for all specific Etsy fee properties.
5.  **Build Dashboard:**
    *   Create a new page. Add "Linked View" blocks for `Orders` and `Materials`. Apply the filters mentioned in Section 1 (e.g., Low Stock = `Current Stock` < `Reorder Level`).