Database Schema

SQLite 3 · WAL mode · FTS5 · Foreign keys enforced — 38 tables total

Core Tables

users Registered user accounts and subscription details

Column	Type	Constraints
id	TEXT	PK
email	TEXT	UNIQUENOT NULL
password_hash	TEXT	NOT NULL
username	TEXT	UNIQUENOT NULL
full_name	TEXT
avatar_url	TEXT
subscription_tier	TEXT	DEFAULT 'free' CHECK (free \| starter \| pro \| business)
subscription_expires_at	DATETIME
timezone	TEXT	DEFAULT 'America/New_York'
locale	TEXT	DEFAULT 'en-US'
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP
updated_at	DATETIME	DEFAULT CURRENT_TIMESTAMP
last_login_at	DATETIME
is_active	INTEGER	DEFAULT 1
preferences	TEXT	DEFAULT '{}' JSON blob
onboarding_completed	INTEGER	DEFAULT 0

Indexes

idx_users_email ON users(email)

idx_users_username ON users(username)

sessions Refresh token sessions for JWT auth

Column	Type	Constraints
id	TEXT	PK
user_id	TEXT	FK → users.idNOT NULL ON DELETE CASCADE
refresh_token	TEXT	UNIQUENOT NULL
device_info	TEXT
ip_address	TEXT
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP
expires_at	DATETIME	NOT NULL
is_valid	INTEGER	DEFAULT 1

Indexes

idx_sessions_user_id ON sessions(user_id)

idx_sessions_refresh_token ON sessions(refresh_token)

inventory Master catalog of InventoryItems; source of truth for all Listings

Column	Type	Constraints
id	TEXT	PK
user_id	TEXT	FK → users.idNOT NULL ON DELETE CASCADE
sku	TEXT
title	TEXT	NOT NULL
description	TEXT
brand	TEXT
category	TEXT
subcategory	TEXT
size	TEXT
color	TEXT
condition	TEXT	CHECK (new \| like_new \| good \| fair \| poor)
cost_price	REAL	DEFAULT 0
list_price	REAL	NOT NULL
quantity	INTEGER	DEFAULT 1
low_stock_threshold	INTEGER	DEFAULT 5
weight	REAL
dimensions	TEXT
material	TEXT
tags	TEXT	DEFAULT '[]' JSON array
images	TEXT	DEFAULT '[]' JSON array of URLs
thumbnail_url	TEXT
status	TEXT	DEFAULT 'draft' CHECK (draft \| active \| sold \| archived \| deleted)
location	TEXT
notes	TEXT
blockchain_hash	TEXT
sustainability_score	REAL
ai_generated_data	TEXT	DEFAULT '{}' JSON blob
custom_fields	TEXT	DEFAULT '{}' JSON blob
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP
updated_at	DATETIME	DEFAULT CURRENT_TIMESTAMP

Indexes

idx_inventory_user_id ON inventory(user_id)

idx_inventory_status ON inventory(status)

idx_inventory_sku ON inventory(sku)

FTS5 Virtual Table

inventory_fts — content=inventory; indexes title, description, brand, tags. Kept in sync via INSERT/UPDATE/DELETE triggers.

listings Platform-specific Listings derived from an InventoryItem

Column	Type	Constraints
id	TEXT	PK
inventory_id	TEXT	FK → inventory.idNOT NULL ON DELETE CASCADE
user_id	TEXT	FK → users.idNOT NULL ON DELETE CASCADE
platform	TEXT	NOT NULL
platform_listing_id	TEXT
platform_url	TEXT
title	TEXT	NOT NULL
description	TEXT
price	REAL	NOT NULL
original_price	REAL
shipping_price	REAL	DEFAULT 0
category_path	TEXT
condition_tag	TEXT
status	TEXT	DEFAULT 'draft' CHECK (draft \| pending \| active \| sold \| ended \| error \| archived)
images	TEXT	DEFAULT '[]' JSON array
platform_specific_data	TEXT	DEFAULT '{}' JSON blob
views	INTEGER	DEFAULT 0
likes	INTEGER	DEFAULT 0
shares	INTEGER	DEFAULT 0
last_shared_at	DATETIME
last_bumped_at	DATETIME
listed_at	DATETIME
sold_at	DATETIME
error_message	TEXT
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP
updated_at	DATETIME	DEFAULT CURRENT_TIMESTAMP

Unique Constraint

UNIQUE(inventory_id, platform) — one listing per platform per item

Indexes

idx_listings_user_id, idx_listings_inventory_id, idx_listings_platform, idx_listings_status

sales Completed Sales and order tracking

Column	Type	Constraints
id	TEXT	PK
user_id	TEXT	FK → users.idNOT NULL ON DELETE CASCADE
listing_id	TEXT	FK → listings.id ON DELETE SET NULL
inventory_id	TEXT	FK → inventory.id ON DELETE SET NULL
platform	TEXT	NOT NULL
platform_order_id	TEXT
buyer_username	TEXT
buyer_address	TEXT
sale_price	REAL	NOT NULL
platform_fee	REAL	DEFAULT 0
shipping_cost	REAL	DEFAULT 0
tax_amount	REAL	DEFAULT 0
net_profit	REAL	Computed: sale_price − platform_fee − shipping_cost − cost_price
status	TEXT	DEFAULT 'pending' CHECK (pending \| confirmed \| shipped \| delivered \| cancelled \| returned)
tracking_number	TEXT
carrier	TEXT
shipped_at	DATETIME
delivered_at	DATETIME
notes	TEXT
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP
updated_at	DATETIME	DEFAULT CURRENT_TIMESTAMP

Indexes

idx_sales_user_id, idx_sales_platform, idx_sales_status

offers Buyer Offers on Listings with accept/decline/counter workflow

Column	Type	Constraints
id	TEXT	PK
user_id	TEXT	FK → users.idNOT NULL ON DELETE CASCADE
listing_id	TEXT	FK → listings.idNOT NULL ON DELETE CASCADE
platform	TEXT	NOT NULL
platform_offer_id	TEXT
buyer_username	TEXT
offer_amount	REAL	NOT NULL
counter_amount	REAL
status	TEXT	DEFAULT 'pending' CHECK (pending \| accepted \| declined \| countered \| expired \| cancelled)
auto_action	TEXT	Automation rule that responded
responded_at	DATETIME
expires_at	DATETIME
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP
updated_at	DATETIME	DEFAULT CURRENT_TIMESTAMP

Indexes

idx_offers_user_id, idx_offers_listing_id, idx_offers_status

shops Connected marketplace Platforms (OAuth credentials, sync state)

Column	Type	Constraints
id	TEXT	PK
user_id	TEXT	FK → users.idNOT NULL ON DELETE CASCADE
platform	TEXT	NOT NULL
platform_username	TEXT
platform_user_id	TEXT
credentials	TEXT	AES-256-CBC encrypted JSON
is_connected	INTEGER	DEFAULT 1
last_sync_at	DATETIME
sync_status	TEXT	DEFAULT 'idle'
settings	TEXT	DEFAULT '{}' JSON blob
stats	TEXT	DEFAULT '{}' JSON blob
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP
updated_at	DATETIME	DEFAULT CURRENT_TIMESTAMP

Unique Constraint

UNIQUE(user_id, platform) — one shop connection per platform per user

Indexes

idx_shops_user_id, idx_shops_platform

automation_rules Automation rules (scheduled Playwright bots: share, follow, offer, relist, price_drop)

Column	Type	Constraints
id	TEXT	PK
user_id	TEXT	FK → users.idNOT NULL ON DELETE CASCADE
name	TEXT	NOT NULL
type	TEXT	NOT NULL CHECK (share \| follow \| offer \| relist \| price_drop \| custom)
platform	TEXT
is_enabled	INTEGER	DEFAULT 1
schedule	TEXT	Cron expression or interval string
conditions	TEXT	DEFAULT '{}' JSON blob
actions	TEXT	DEFAULT '{}' JSON blob
last_run_at	DATETIME
next_run_at	DATETIME
run_count	INTEGER	DEFAULT 0
error_count	INTEGER	DEFAULT 0
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP
updated_at	DATETIME	DEFAULT CURRENT_TIMESTAMP

Indexes

idx_automation_rules_user_id ON automation_rules(user_id)

Related: automation_logs table records every execution result

Remaining Tables (30)

Full schema for these tables is in src/backend/db/schema.sql.

listing_templates

automation_logs

tasks

analytics_snapshots

sustainability_log

alerts

collaborations

sync_queue

notifications

security_logs

request_logs

error_logs

audit_logs

user_preferences

inventory_fts

price_history

image_assets

Indexes (core tables)

Index Name	Table	Columns
idx_users_email	users	(email)
idx_users_username	users	(username)
idx_sessions_user_id	sessions	(user_id)
idx_sessions_refresh_token	sessions	(refresh_token)
idx_shops_user_id	shops	(user_id)
idx_shops_platform	shops	(platform)
idx_inventory_user_id	inventory	(user_id)
idx_inventory_status	inventory	(status)
idx_inventory_sku	inventory	(sku)
idx_listings_user_id	listings	(user_id)
idx_listings_inventory_id	listings	(inventory_id)
idx_listings_platform	listings	(platform)
idx_listings_status	listings	(status)
idx_sales_user_id	sales	(user_id)
idx_sales_platform	sales	(platform)
idx_sales_status	sales	(status)
idx_offers_user_id	offers	(user_id)
idx_offers_listing_id	offers	(listing_id)
idx_offers_status	offers	(status)
idx_automation_rules_user_id	automation_rules	(user_id)

Full-Text Search

inventory_fts FTS5 virtual table backed by inventory

Detail	Value
Engine	FTS5 (SQLite built-in)
Content table	inventory
Content rowid	rowid
Indexed columns	id, title, description, brand, tags
Sync triggers	inventory_ai (INSERT), inventory_au (UPDATE), inventory_ad (DELETE)
Usage	SELECT * FROM inventory_fts WHERE inventory_fts MATCH 'query'

Back to VaultLister