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