OmarketLab

Tables (10)

analysis_run

  • run_id — VARCHAR(50) — Primary key — NOT NULL
  • status — VARCHAR(20)
  • data_source — VARCHAR(50)
  • base_date — DATE
  • segment_rule — VARCHAR(30)
  • score_policy_version — VARCHAR(100) — Foreign key → rfm_score_policy.policy_version
  • segment_policy_version — VARCHAR(100) — Foreign key → segment_rule_policy.policy_version
  • customer_count — INTEGER
  • success_count — INTEGER
  • error_count — INTEGER
  • raw_row_count — INTEGER
  • excluded_row_count — INTEGER
  • valid_row_count — INTEGER
  • preprocess_stats — JSONB
  • started_at — TIMESTAMP
  • finished_at — TIMESTAMP
  • error_message — TEXT
  • created_by — BIGINT — Foreign key → app_user.user_id
  • created_at — TIMESTAMP

app_user

  • user_id — BIGSERIAL — Primary key — NOT NULL
  • username — VARCHAR(100) — NOT NULL
  • password_hash — TEXT
  • role — VARCHAR(20)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

customer_result

  • customer_result_id — BIGSERIAL — Primary key — NOT NULL
  • run_id — VARCHAR(50) — Foreign key → analysis_run.run_id
  • customer_id — VARCHAR(50)
  • segment — VARCHAR(20)
  • recency — INTEGER
  • frequency — INTEGER
  • monetary — NUMERIC
  • r_score — INTEGER
  • f_score — INTEGER
  • m_score — INTEGER
  • rfm_code — VARCHAR(10)
  • first_purchase_at — DATE
  • last_purchase_at — DATE
  • purchase_period_days — INTEGER
  • avg_purchase_interval_days — NUMERIC
  • classification_reason — JSONB
  • classification_rule_code — VARCHAR(100)
  • created_at — TIMESTAMP

evidence_transaction

  • evidence_transaction_id — BIGSERIAL — Primary key — NOT NULL
  • customer_result_id — BIGINT — Foreign key → customer_result.customer_result_id
  • invoice_no — VARCHAR(50)
  • invoice_date — DATE
  • product_id — VARCHAR(50)
  • stock_code — VARCHAR(50)
  • product_name — TEXT
  • quantity — INTEGER
  • unit_price — NUMERIC
  • amount — NUMERIC
  • evidence_type — VARCHAR(20)
  • source_row_hash — VARCHAR(100)
  • created_at — TIMESTAMP

recommendation

  • recommendation_id — BIGSERIAL — Primary key — NOT NULL
  • customer_result_id — BIGINT — Foreign key → customer_result.customer_result_id
  • product_id — VARCHAR(50)
  • product_name — TEXT
  • rank — INTEGER
  • match_score — NUMERIC
  • reason — TEXT
  • recommendation_method — VARCHAR(40)
  • already_purchased — BOOLEAN
  • created_at — TIMESTAMP

recommendation_evidence

  • recommendation_id — BIGINT — Foreign key → recommendation.recommendation_id — NOT NULL
  • evidence_transaction_id — BIGINT — Foreign key → evidence_transaction.evidence_transaction_id — NOT NULL
  • relevance_reason — TEXT
  • relevance_score — NUMERIC
  • created_at — TIMESTAMP

rfm_score_policy

  • rfm_score_policy_id — BIGSERIAL — Primary key — NOT NULL
  • policy_version — VARCHAR(100)
  • status — VARCHAR(20)
  • bins — INTEGER
  • basis_method — VARCHAR(30)
  • data_source — VARCHAR(50)
  • basis_base_date — DATE
  • r_policy — JSONB
  • f_policy — JSONB
  • m_policy — JSONB
  • recommend_reason — TEXT
  • decided_by — BIGINT — Foreign key → app_user.user_id
  • created_at — TIMESTAMP
  • decided_at — TIMESTAMP

segment_monthly_trend

  • segment_monthly_trend_id — BIGSERIAL — Primary key — NOT NULL
  • run_id — VARCHAR(50) — Foreign key → analysis_run.run_id
  • segment — VARCHAR(20)
  • month — CHAR(7)
  • total_amount — NUMERIC
  • transaction_count — INTEGER
  • customer_count — INTEGER
  • created_at — TIMESTAMP

segment_rule_policy

  • segment_rule_policy_id — BIGSERIAL — Primary key — NOT NULL
  • policy_version — VARCHAR(100)
  • status — VARCHAR(20)
  • score_policy_version — VARCHAR(100) — Foreign key → rfm_score_policy.policy_version
  • priority — JSONB
  • rules — JSONB
  • recommend_reason — TEXT
  • decided_by — BIGINT — Foreign key → app_user.user_id
  • created_at — TIMESTAMP
  • decided_at — TIMESTAMP

segment_summary

  • segment_summary_id — BIGSERIAL — Primary key — NOT NULL
  • run_id — VARCHAR(50) — Foreign key → analysis_run.run_id
  • segment — VARCHAR(20)
  • customer_count — INTEGER
  • total_monetary — NUMERIC
  • avg_monetary — NUMERIC
  • avg_recency — NUMERIC
  • avg_frequency — NUMERIC
  • avg_purchase_interval_days — NUMERIC
  • created_at — TIMESTAMP