Database ER Diagram

Database ER Diagram

Entity-relationship overview of the core VaultLister 3.0 schema. All ID columns are TEXT (UUID). The database is SQLite 3 in WAL mode with FTS5 for full-text search on inventory.

Legend

PK Primary key
FK Foreign key
1 : N One-to-many relationship

Relationship map

users
  │
  ├─[1:N]──────────────► inventory
  │                           │
  │                           └─[1:N]────────► listings
  │                                                │
  ├─[1:N]──────────────────────────────────────────┘
  │
  ├─[1:N]──────────────► sales
  │
  ├─[1:N]──────────────► offers
  │
  ├─[1:N]──────────────► shops
  │
  └─[1:N]──────────────► sessions

Relationships

Parent entity Cardinality Child entity Description
users 1 : N inventory Each user owns zero or more InventoryItems. Deleting a user cascades to their inventory.
users 1 : N listings Each user owns zero or more Listings across all platforms. A Listing also belongs to one InventoryItem.
inventory 1 : N listings One InventoryItem can have one Listing per platform (up to 9). Status syncs back when a sale occurs.
users 1 : N sales Each Sale belongs to one user and is linked to the originating Listing and InventoryItem.
users 1 : N offers Each Offer belongs to one user. Offers are associated with a Listing and a platform buyer ID.
users 1 : N shops Each user can have one shop profile per platform (e.g., Poshmark closet, eBay store).
users 1 : N sessions Auth sessions for JWT refresh token tracking. Supports multiple devices (Remember Me).

Core entities

users

1 row per account
  • PK id TEXT
  • email TEXT UNIQUE
  • password_hash TEXT
  • totp_secret TEXT
  • plan TEXT
  • created_at TEXT

inventory

1 row per item
  • PK id TEXT
  • FK user_id TEXT
  • title TEXT
  • description TEXT
  • cost_price REAL
  • list_price REAL
  • status TEXT
  • tags TEXT (JSON)
  • created_at TEXT

listings

1 row per platform listing
  • PK id TEXT
  • FK user_id TEXT
  • FK inventory_id TEXT
  • platform TEXT
  • platform_listing_id TEXT
  • status TEXT
  • listed_at TEXT

sales

1 row per completed sale
  • PK id TEXT
  • FK user_id TEXT
  • FK listing_id TEXT
  • sale_price REAL
  • platform_fees REAL
  • net_profit REAL
  • sold_at TEXT

offers

1 row per offer
  • PK id TEXT
  • FK user_id TEXT
  • FK listing_id TEXT
  • offer_amount REAL
  • status TEXT
  • buyer_id TEXT
  • created_at TEXT

shops

1 row per platform connection
  • PK id TEXT
  • FK user_id TEXT
  • platform TEXT
  • shop_name TEXT
  • access_token_enc TEXT
  • refresh_token_enc TEXT
  • connected_at TEXT

sessions

1 row per device/refresh token
  • PK id TEXT
  • FK user_id TEXT
  • refresh_token_hash TEXT
  • ip_address TEXT
  • user_agent TEXT
  • expires_at TEXT
  • created_at TEXT