Data model

ContentsVision stores everything in a single SQLite database (storage/inventory.db) through SQLAlchemy 2's async ORM. The models live in backend/app/models/. This page is the reference for every table and relationship.

Entity relationships

Foreign keys vs implicit links

The parent-child links inside a claim (Room.claim_id, ProcessingJob.room_id, InventoryItem.job_id, ItemPhoto.item_id, StagedPhoto.job_id) are real SQLAlchemy ForeignKey columns. The ownership links team_id (on users and claims) and Claim.created_by_user_id are plain indexed strings, not database-level foreign keys. Team scoping is enforced in the application layer, not by the database.

Tables

teams

The organization. Auto-created on first login from the SSO token.

Column	Type	Notes
id	string (UUID)	Primary key.
created_at	datetime
external_id	int, nullable	The AdjustSquare team id.
name	string	Team display name.
team_token	string, indexed	The team's Adjust Square API token, used as the bearer token when submitting claims.
credit_rate	float, nullable	Dollars of revenue per credit, for the media-processing Slack notification. NULL (the default) means use the global DEFAULT_TEAM_CREDIT_RATE.

users

A person. Auto-provisioned on first login.

Column	Type	Notes
id	string (UUID)	Primary key.
created_at	datetime
external_id	int, nullable, unique, indexed	The AdjustSquare user id; becomes the adjuster id on submission.
email	string, unique, indexed
name	string
team_id	string, indexed	The owning team's id.
is_active	bool
last_seen_at	datetime, nullable, indexed	When the user last made an authenticated request. Stamped by get_current_user (at most once a minute per user); powers the admin "Active Users (live)" stat.
timezone	string	IANA zone picked on Settings > Profile ("" = browser local). Display preference only; storage stays UTC.

component_visibility

Admin-controlled show/hide overrides for UI components (currently training). One row per override; resolution is team override > global override (team_id="") > the registry default in services/component_visibility.py (training defaults to HIDDEN). See GET /api/admin/components and the admin Teams tab.

Column	Type	Notes
id	string (UUID)	Primary key.
updated_at	datetime
component	string, indexed	Key from the component registry, e.g. training.
team_id	string, indexed	"" = the global row; otherwise the team this override applies to.
visible	bool

claims

The top-level unit of work.

Column	Type	Notes
id	string (UUID)	Primary key.
created_at / updated_at	datetime	created_at indexed.
claim_number	string, indexed
date_of_loss	date
zip_code	string
policy_limit / policy_deductible	float
insured_name / adjuster_name	string
loss_type / coverage_type / claim_type	string	Human-readable; mapped to Adjust Square codes on submit.
team_id	string, indexed	Owning team.
is_training	bool, indexed	Training sandbox flag: uploads complete instantly with fixture items (no AI, no credits) and submission is a dry run. One per user, auto-created by the Training hub. See training.
created_by_user_id	string, indexed	The users.id of the member who created the claim. That user's external_id is the Adjust Square user id.
adjust_square_claim_key	string	Set after the first submission.
adjust_square_claim_id	int	The integer claim id on Adjust Square.
submitted_at	datetime, nullable
is_archived	bool, indexed	Soft-hide flag. Archived claims drop out of the default GET /api/claims list. Nothing is ever deleted by hand; archiving is the only removal path. A claim that has been submitted (submitted_at set) is locked and cannot be archived.
archived_at	datetime, nullable	When the claim was archived (null when active). The daily purge permanently deletes anything archived more than 30 days ago; restoring clears this.

rooms

A labeled bucket inside a claim.

Column	Type	Notes
id	string (UUID)	Primary key.
claim_id	string, FK to claims.id, indexed
created_at / updated_at	datetime
name	string	e.g. "Master Bedroom".
is_archived	bool, indexed	Soft-hide flag. Archived rooms drop out of the default room list and their items stop counting toward the claim. A room that contains any submitted item is locked and cannot be archived.
archived_at	datetime, nullable	When the room was archived (null when active). Drives the 30-day purge.

processing_jobs

One upload and its processing run.

Column	Type	Notes
id	string (UUID)	Primary key.
room_id	string, FK to rooms.id, indexed
source_type	string	video, photo, or audio.
filename / file_path	string	The original file (or photos dir).
file_size_bytes	int
duration_seconds	float	Drives video/audio billing.
loss_type / claim_number	string	Copied from the claim at creation.
status	string	pending, awaiting_grouping, extracting, transcribing, segmenting, analyzing, synthesizing, complete, error.
progress_pct	int	0-100, streamed to the UI.
current_stage	string	Human-readable current step.
error_message	text	Set on failure.
total_frames / rooms_detected / items_found	int	Processing metadata.
transcript / transcript_segments / transcript_language	text / JSON / string	Transcription output.
room_segments	JSON	Time-chunk ranges.
completed_at	datetime, nullable
billed_cost_usd	float	Credits charged (name kept for backward compatibility).
billed_minutes	float	Whole minutes billed (video/audio).
photo_request_count	int	AI calls made (photo jobs).
billing_charged	bool	Whether credits were spent up front against the external billing service (only when ENABLE_BILLING). false otherwise.
billing_txn_id	string	The billing service's transaction id for the up-front spend, when one was made.
billing_amount	float	Credits spent up front, so a refund can return the exact amount.
billing_refunded	bool	true once the up-front charge was refunded because processing failed.
recovery_attempts	int	How many times the self-healing sweep requeued this job after an interruption (restart, crash, hang). One retry is allowed; a second interruption fails and refunds the job. See troubleshooting.
ai_cost_usd	float	Our internal OpenRouter spend in dollars (token counts priced at the model's per-million rates), copied from the in-memory observability counters when the job finishes. Separate from customer credit billing. Jobs predating this stay at 0.
ai_input_tokens / ai_output_tokens / ai_api_calls	int	The token counts and AI call count behind ai_cost_usd.

job_log_archives

The durable copy of a job's pipeline log lines. While a job runs its logs live in the in-memory ring buffer (see observability); when the job reaches complete or error the buffer is copied here so the detail survives restarts and stays readable on the Logs page. One row per job, upserted.

Column	Type	Notes
job_id	string, FK to processing_jobs.id	Primary key.
created_at	datetime
logs	JSON	The full list of structured log entries (ts, level, msg, stage, elapsed).

submissions

One "Send for Estimation" event for a claim (a claim can be submitted several times; later submissions add the new items to the same Adjust Square claim).

Column	Type	Notes
id	string (UUID)	Primary key; shown as the submission id in the UI.
claim_id	string, indexed
created_at	datetime
items_count / photos_count	int	What the submission carried.
adjust_square_claim_key	string	The portal claim it landed on.
is_resubmit	bool	False for the claim-creating first submission.
is_dry_run	bool	Training claims: recorded, sent nowhere.
status	string	processing while background item/photo uploads run, then complete.
processing_seconds	float	End to end: request start to background finish.

api_request_logs

One outbound AI API call (OpenRouter), for the admin "API Requests" inspector on the Logs page. The request JSON is stored with base64 images redacted to size placeholders; the response is stored in full.

Column	Type	Notes
id	string (UUID)	Primary key.
created_at	datetime, indexed
job_id	string, indexed	Derived from the per-job pipeline logger.
service / method / url / model	string	openrouter, POST, the endpoint, and the model id.
status_code / duration_ms / attempts	int / float / int	Final outcome and total time across retries.
input_tokens / output_tokens / cost_usd	int / int / float	From the response usage block (OpenRouter reports real cost).
request_json / response_json	JSON	Redacted request; full response.
error	text	Set when the call ultimately failed.

inventory_items

A single line of the inventory.

Column	Type	Notes
id	string (UUID)	Primary key.
job_id	string, FK to processing_jobs.id, indexed
source_type	string	Copied from the job.
item_name	string	Searchable retail-style name.
quantity	int	Identical items are grouped.
room	string	Room name (denormalized for convenience).
condition	string	New / Above Average / Average / Below Average.
age_years / age_months	int	Set by the AI only from a spoken age (video narration, audio). Always 0 from photos, and never estimated from appearance.
notes	text
brand / model_number / serial_number / sku	string
damage_type	string	smoke / water / heat / impact / mold.
estimated_value	float
confidence	float	0.0-1.0.
audio_evidence / visual_evidence	text	What was said / what was seen.
audio_start_seconds / audio_end_seconds	float	The recording slice (audio items).
photo_path / crop_photo_path / photo_context_path / source_photo_path	string	Legacy photo columns, kept as a safety net.
photo_frame_time	float	Timestamp in the video.
photo_frames	text (JSON)	All analyzed frame URLs.
reviewed / flagged / approved	bool	Human review state.
is_archived	bool, indexed	Soft-hide flag. Archived items drop out of the inventory lists, counts, exports, and Adjust Square submission, but keep their item_number (no renumbering on archive) so unarchiving restores the exact slot. Submitted items cannot be archived. Archiving is the only way to remove an item; there is no manual delete.
archived_at	datetime, nullable	When the item was archived (null when active). Drives the 30-day purge.
sort_order	int
item_number	int, indexed	Unique per claim.
submitted	bool, indexed	Sent to Adjust Square.
adjust_square_item_id	int	Remote item id after submission.
photos	relationship	The item_photos rows (source of truth for images).

item_photos

Photos attached to a finished item. The source of truth for item imagery (the legacy photo_path columns on the item are kept for compatibility).

Column	Type	Notes
id	string (UUID)	Primary key.
item_id	string, FK to inventory_items.id, CASCADE delete, indexed
path / crop_path / context_path	string
is_primary	bool	The primary thumbnail.
photo_order	int	"order" is reserved in SQL, hence the name.

staged_photos

Raw uploaded photos waiting to be grouped before AI runs.

Column	Type	Notes
id	string (UUID)	Primary key.
job_id	string, FK to processing_jobs.id, CASCADE delete, indexed
filename	string	File on disk under <storage>/<job_id>/photos/ (a synthetic orig_####.jpg sequence).
original_filename	string	The photo's real upload name (e.g. IMG_1234.jpg). The on-disk filename is synthetic, so this is the only record of the real name; grouping's "Sort by name" re-orders on it. Blank for manual segments and for rows created before the column existed.
captured_at	string	When the photo was taken (YYYY-MM-DDTHH:MM:SS), read from EXIF in the browser at upload (the re-encode strips EXIF, so it must be captured before then); falls back to the file's modified time. Powers grouping's "Sort by date". Blank when unknown and for manual segments.
group_index	int, indexed	Photos sharing a group become one item. Manual segments use a high range (>= 1_000_000) so they never collide with photo groups.
photo_order	int
deleted	bool, indexed	Soft-delete; file removed on Process.
is_segment	bool	True for a crop the user drew from a parent photo during grouping (a known item the AI never re-splits).
quantity	int	User-set quantity for a described or segment item (e.g. 4 for four boxed rolls of tape). 1 by default.
manual_name	string	User-typed name for the item (a whole photo / merged group, or a segment). When set, the AI is skipped for it (no request, no credits). Blank means the AI names it.
source_filename	string	The whole photo a segment was cropped from, so "clear segments" can restore it.
bbox	string	JSON [x, y, w, h] (normalized) of the box on the source photo, kept for re-editing.

activity_log

One row per meaningful action, powering the Settings activity log and its Restore button. Covered in observability.

Column	Type	Notes
id	string (UUID)	Primary key.
created_at	datetime, indexed
user_id / user_name	string	Who did it.
action_type	string	Machine code the restore logic dispatches on.
action_label / target	string	Human-readable text.
entity_type / entity_id	string	What was acted on.
snapshot	JSON, nullable	Everything needed to undo the action.
restorable / restored / restored_at	bool / bool / datetime	Restore state.

training_progress

One row per user per training mission; the source of truth for points and badges (mission complete = badge earned). See training.

Column	Type	Notes
id	string (UUID)	Primary key.
created_at / updated_at	datetime
user_id / team_id	string, indexed	Who is training, and their team (for the leaderboard).
mission_key	string, indexed	Curriculum mission key, e.g. messy-claim.
status	string	in_progress or complete.
quiz_score / quiz_total / quiz_attempts	int	Best quiz result and attempt count.
checks_passed	JSON	Check keys that passed on the latest grading run.
check_attempts	int
points	int	Banked once at completion: mission base + quiz bonus.
completed_at	datetime, nullable

training_certificates

Issued certifications, publicly verifiable by code. See training.

Column	Type	Notes
id	string (UUID)	Primary key.
code	string, unique, indexed	Shareable verification code, e.g. CV-3F9A-C24D-71BE.
user_id / team_id	string, indexed
holder_name / team_name	string	Snapshotted at issue time so the public page stays stable.
level	string	Certified Contents Specialist.
score_pct / points	int	Overall quiz accuracy and total points at issue time.
issued_at	datetime
revoked	bool	Invalidates the certificate while keeping the code resolvable.

How the schema is created and migrated

There is no migration tool (no Alembic). On startup, core/database.py's init_db():

1. Calls Base.metadata.create_all to create any missing tables.
2. Runs a list of idempotent ALTER TABLE ... ADD COLUMN statements, each wrapped in a try/except so an already-existing column is silently skipped. This is how new columns are added to existing databases.
3. Runs a few one-time backfills (also guarded): assigning sequential item_numbers to old items per claim, repairing crop_photo_path for old photo items, and seeding item_photos rows for items that predate the multi-photo model.

Adding a column

To add a column: add it to the model and append an ALTER TABLE ... ADD COLUMN line in init_db(). create_all only creates whole new tables; it never alters an existing one, so without the ALTER line the column exists in the model but not in an already-deployed database.