link,[object Object]
ACQM Docs

Appearance

Tables – Domain Reference ​

This document centralizes detailed table definitions, indexes, and RLS notes, organized by domain. For a high-level map and quick links, see schema.md.

1) Authentication & User Management ​

profiles ​

Core user profiles extending Supabase auth.users.

Columns:

  • id uuid PK (references auth.users.id)
  • full_name text
  • email text
  • avatar_url text
  • role app_role DEFAULT 'buyer' — one of: admin, seller, buyer
  • subscription_level text DEFAULT 'free' — free, starter, pro
  • subscription_status subscription_status DEFAULT 'inactive'
  • language_preference text DEFAULT 'ro'
  • seller_verified boolean DEFAULT false
  • seller_since timestamptz
  • seller_rating numeric
  • total_sales integer DEFAULT 0
  • total_sales_value numeric DEFAULT 0
  • kyc_status kyc_status DEFAULT 'not_started'
  • payouts_enabled boolean DEFAULT false
  • charges_enabled boolean DEFAULT false
  • details_submitted boolean DEFAULT false
  • stripe_customer_id text
  • stripe_connect_id text
  • stripe_connect_status text DEFAULT 'not_started'
  • bio text, bio_ro text, bio_en text
  • company_name text
  • website text
  • created_at timestamptz DEFAULT now()
  • updated_at timestamptz DEFAULT now()

Indexes:

  • PK(id), idx(role), idx(seller_verified), idx(subscription_level)

RLS:

  • Users can SELECT/UPDATE own row; admins full access; service role full access

2) Marketplace Core ​

listings ​

Business listings with comprehensive multilingual content and structured description.

Columns (selection):

  • id uuid PK DEFAULT gen_random_uuid()
  • title text NOT NULL; title_ro text; title_en text
  • description text; description_ro text; description_en text
  • price numeric(10,2) NOT NULL; original_price numeric
  • category text NOT NULL
  • status listing_status DEFAULT 'draft'
  • verification_status text DEFAULT 'pending'
  • rejection_reason text
  • seller_id uuid NOT NULL; buyer_id uuid
  • monthly_revenue numeric(10,2); monthly_profit numeric(10,2); monthly_traffic integer
  • number_of_customers integer; established_date date
  • technologies text[]; included_assets text[]
  • featured boolean DEFAULT false; allow_negotiation boolean DEFAULT false
  • images jsonb DEFAULT '[]'; main_image text
  • screenshot_url text; traffic_metrics_screenshot text; revenue_metrics_screenshot text
  • website_url text
  • views_count integer DEFAULT 0
  • is_sponsored boolean DEFAULT false
  • sponsored_start_date timestamptz; sponsored_end_date timestamptz; sponsored_scheduled_start timestamptz
  • sponsored_views_count integer DEFAULT 0; sponsored_clicks_count integer DEFAULT 0; sponsored_offers_count integer DEFAULT 0
  • admin_comment text
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

Structured description fields (RO/EN variants present for each):

  • description_introduction_startup_description, ..._business_model, ..._key_strengths, ..._team_founder
  • description_content_included_assets, ..._operations_time, ..._technology_stack, ..._financial_performance, ..._growth_opportunities
  • description_conclusion_selling_reason, ..._buyer_benefits, ..._risks_challenges, ..._ideal_buyer
  • business_model, price_reasoning, reason_for_selling, target_audience, competitors, growth_opportunities, terms_conditions

Indexes:

  • PK(id)
  • Composite on (status, verification_status)
  • idx(seller_id), idx(category), idx(featured), idx(created_at DESC)
  • Full-text on title/description fields (where supported)

RLS:

  • SELECT public for active + approved listings; owners/admins broader
  • INSERT authenticated; UPDATE owner/admin; DELETE admin

listing_categories ​

Simple taxonomy for listings.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • name text NOT NULL
  • slug text NOT NULL
  • created_at timestamptz DEFAULT now()

RLS:

  • SELECT public; write operations admin only

listing_views ​

Per-listing view events or counters.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • listing_id uuid NOT NULL REFERENCES listings(id)
  • viewer_id uuid NULL — null for anonymous
  • viewed_at timestamptz DEFAULT now()
  • count integer DEFAULT 1

Indexes: idx(listing_id), idx(viewer_id), idx(viewed_at)

RLS: SELECT owner/admin; INSERT/UPDATE service role or trusted triggers

listing_questions ​

Buyer Q&A on listings.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • listing_id uuid NOT NULL REFERENCES listings(id)
  • user_id uuid NOT NULL REFERENCES profiles(id)
  • question text NOT NULL; answer text
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

RLS:

  • SELECT public for active listings; INSERT authenticated; UPDATE (answer) by seller/admin

3) Offers & Transactions ​

offers ​

Buyer offers to sellers.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • listing_id uuid NOT NULL
  • buyer_id uuid NOT NULL
  • seller_id uuid NOT NULL
  • amount numeric(10,2) NOT NULL
  • status offer_status DEFAULT 'pending'
  • message text; response_message text
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

Status flow: pending → accepted | rejected | withdrawn

Indexes: idx(listing_id), idx(buyer_id), idx(seller_id), idx(status, created_at)

RLS: SELECT buyer/seller/admin; INSERT buyer (not own listing); UPDATE seller (status/response)

transactions ​

Deal lifecycle with escrow support.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • listing_id uuid NOT NULL; buyer_id uuid NOT NULL; seller_id uuid NOT NULL
  • amount numeric(10,2) NOT NULL — net to seller
  • amount_gross numeric; commission_amount numeric; commission_rate numeric
  • status transaction_status DEFAULT 'pending'
  • stripe_payment_intent_id text; stripe_session_id text
  • payment_provider payment_provider DEFAULT 'stripe'
  • payment_method_id text
  • refund_amount numeric; refund_reason text
  • escrow_released_at timestamptz
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

Status flow (simplified): pending → offer_pending → offer_accepted → payment_required → funded → dd_in_progress → handover_in_progress → buyer_accepts → payout_released → completed Alt: payment_failed | disputed → resolved/refunded | cancelled

Commission: computed via calculate_commission(amount, seller_id); tiered by seller subscription

Indexes: idx(listing_id), idx(buyer_id), idx(seller_id), idx(status), idx(created_at DESC)

RLS: SELECT deal parties/admin; INSERT system; UPDATE restricted to parties/admin by status

4) Communication System ​

conversations ​

Message threads.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • listing_id uuid NOT NULL
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

conversation_participants ​

Participants per conversation.

Columns:

  • conversation_id uuid NOT NULL REFERENCES conversations(id)
  • user_id uuid NOT NULL REFERENCES profiles(id)
  • joined_at timestamptz DEFAULT now()
  • last_read_message_id uuid

Primary key: (conversation_id, user_id)

messages ​

Conversation messages.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • conversation_id uuid NOT NULL
  • sender_id uuid NOT NULL
  • content text NOT NULL
  • read boolean DEFAULT false
  • created_at timestamptz DEFAULT now()

Subscription limits (business rules): Free: 0/day; Starter: 5/day; Pro: unlimited

5) Subscriptions & Billing ​

subscriptions ​

User subscription state and periods.

Columns (selection):

  • id uuid PK DEFAULT gen_random_uuid(); user_id uuid NOT NULL
  • stripe_subscription_id text; stripe_customer_id text
  • subscription_level text NOT NULL DEFAULT 'free'
  • subscription_status text NOT NULL DEFAULT 'inactive'
  • price_amount integer DEFAULT 0; currency text DEFAULT 'usd'
  • current_period_start timestamptz; current_period_end timestamptz
  • trial_start timestamptz; trial_end timestamptz
  • cancel_at_period_end boolean DEFAULT false; canceled_at timestamptz
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

subscription_features ​

Feature definitions and limits per plan.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • feature_key text UNIQUE
  • feature_name text; feature_name_ro text; feature_name_en text
  • feature_description text; ..._ro text; ..._en text
  • feature_category text
  • free_enabled boolean DEFAULT false; starter_enabled boolean DEFAULT false; pro_enabled boolean DEFAULT false
  • free_limit integer DEFAULT 0; starter_limit integer DEFAULT 0; pro_limit integer DEFAULT 0
  • is_numeric boolean DEFAULT false
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

subscription_feature_usage ​

Tracks per-user usage.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • user_id uuid NOT NULL; feature_key text NOT NULL
  • usage_count integer DEFAULT 0; last_reset_at timestamptz DEFAULT now()
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

Unique: (user_id, feature_key)

billing_plans ​

Stripe-linked plan catalog.

Columns:

  • price_id text PK — Stripe Price ID
  • level text NOT NULL — free/starter/pro
  • name text NOT NULL
  • interval text NOT NULL
  • role text NOT NULL DEFAULT 'buyer'
  • currency text DEFAULT 'usd'

RLS: SELECT public; write admin only

6) Administration & Moderation ​

admin_listing_comments ​

Admin feedback on listings.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • listing_id uuid NOT NULL; admin_id uuid NOT NULL
  • field_key text NOT NULL; comment text NOT NULL
  • is_resolved boolean NOT NULL DEFAULT false
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

Related functions: get_admin_listing_comments, get_unresolved_comments_count, send_admin_comments_to_user

admin_messages ​

Direct messages from admins to users.

Columns (selection):

  • id uuid PK DEFAULT gen_random_uuid()
  • listing_id uuid; admin_id uuid NOT NULL; recipient_id uuid NOT NULL
  • subject text DEFAULT 'Mesaj admin privind listarea'
  • message text NOT NULL
  • sent_at timestamptz DEFAULT now(); read_at timestamptz
  • email_sent boolean DEFAULT false; notification_sent boolean DEFAULT false
  • page_source text DEFAULT 'admin_listings'
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

admin_contact_messages ​

Contact form submissions to admins.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • sender_id uuid NOT NULL
  • subject text NOT NULL; message text NOT NULL
  • status text NOT NULL DEFAULT 'new'
  • admin_response text; responded_by uuid
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

reports ​

User-generated content reports.

Columns (selection):

  • id uuid PK DEFAULT gen_random_uuid()
  • reporter_id uuid NOT NULL; reported_user_id uuid; listing_id uuid
  • reason text NOT NULL; description text
  • status report_status DEFAULT 'pending'
  • admin_notes text; resolved_by uuid; resolved_at timestamptz
  • evidence_urls text[]; priority text DEFAULT 'medium'; category text
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

email_logs ​

Outbound email log for operational observability.

Columns (selection):

  • id uuid PK DEFAULT gen_random_uuid()
  • recipient_email text NOT NULL; recipient_id uuid
  • email_type text NOT NULL; subject text NOT NULL; template_name text
  • send_status text DEFAULT 'pending' CHECK (send_status IN ('pending','sent','failed'))
  • resend_email_id text; error_message text; metadata jsonb
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

7) System Configuration ​

blurred_fields ​

Subscription-based field visibility control.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • field_key text NOT NULL; field_name text NOT NULL; field_description text
  • is_blurred_for_unauthenticated boolean DEFAULT false
  • is_blurred_for_free boolean DEFAULT false
  • is_blurred_for_starter boolean DEFAULT false
  • is_blurred_for_pro boolean DEFAULT false
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

Helper:

  • can_view_field(listing_id, field_key, user_id)

translations ​

Key/value multilingual content.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • key text UNIQUE NOT NULL
  • value_ro text NOT NULL; value_en text NOT NULL
  • category text DEFAULT 'general'
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

tech_stack ​

Available technologies.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • name text NOT NULL; slug text NOT NULL
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

8) Analytics & Tracking ​

listing_views_summary (VIEW) ​

Aggregated view analytics per listing for dashboards.

price_alerts ​

User-configurable price monitoring.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • user_id uuid NOT NULL; listing_id uuid NOT NULL
  • target_price numeric(12,2) NOT NULL; alert_type text DEFAULT 'drop'
  • active boolean DEFAULT true; notified boolean DEFAULT false; send_notifications boolean DEFAULT true
  • last_seen_price numeric; last_notified_price numeric; last_notified_at timestamptz
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

Constraints: CHECK (alert_type IN ('drop','increase')), CHECK (target_price > 0)

favorites ​

User saved listings.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • user_id uuid NOT NULL; listing_id uuid NOT NULL
  • created_at timestamptz DEFAULT now()

Unique: (user_id, listing_id)

saved_searches ​

Saved search criteria per user.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • user_id uuid NOT NULL
  • name text NOT NULL; criteria jsonb NOT NULL
  • created_at timestamptz DEFAULT now()

9) Content Management ​

blog_posts ​

Blog/news content with RO/EN fields.

Columns (selection):

  • id uuid PK DEFAULT gen_random_uuid()
  • title text; title_ro text; title_en text; slug text NOT NULL
  • content text; content_ro text; content_en text
  • excerpt text; excerpt_ro text; excerpt_en text
  • featured_image text; author_id uuid
  • published boolean DEFAULT false; published_at timestamptz
  • meta_description text
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

simple_pages ​

Static page content in Markdown with RO/EN variants.

Columns:

  • id uuid PK DEFAULT gen_random_uuid()
  • slug text NOT NULL
  • title text NOT NULL; title_ro text; title_en text
  • content_markdown text; content_markdown_ro text; content_markdown_en text
  • published boolean DEFAULT false
  • created_by uuid
  • created_at timestamptz DEFAULT now(); updated_at timestamptz DEFAULT now()

Related:

  • See ../architecture/blur-policy.md for visibility model and ../setup/supabase.md for DB setup.