طراحی پایگاه‌داده پروژه (Database Schema)

این سند اسکیمای جدول‌های PostgreSQL پروژه را مطابق وضعیت فعلی کد مستند می‌کند: مدل Django در Application Registry و فایل‌های SQL زیرسیستم Storage Consumer (storage_consumer/persistence/sql/). ستون reviews.sentiment توسط زیرسیستم Sentiment نوشته می‌شود.

نمای کلی روابط

erDiagram APPS_REGISTRY_APPLICATION ||--o{ APP_STATS : "has many" APPS_REGISTRY_APPLICATION ||--o{ REVIEWS : "has many" APPS_REGISTRY_APPLICATION ||--o{ NETWORK_METRICS : "has many" APPS_REGISTRY_APPLICATION { bigint id PK string package_name UK boolean is_messaging_app boolean is_iranian_app boolean is_active } APP_STATS { bigint id PK bigint application_id FK timestamptz crawled_at } REVIEWS { bigint id PK bigint application_id FK string review_id UK string sentiment } NETWORK_METRICS { bigint id PK bigint application_id FK uuid analysis_id UK } DEAD_LETTER_EVENTS { bigint id PK string topic int partition bigint kafka_offset }

۱. جدول Applications

نام واقعی در Postgres: apps_registry_application (مدل Django Application در اپ apps_registry). مالک DDL: app_api از طریق مهاجرت‌های Django.

جدول مرکزی سیستم است که فهرست اپلیکیشن‌های تحت پایش را نگه می‌دارد. فقط وضعیت فعلی لیست را نشان می‌دهد و دادهٔ آماری/تاریخی ندارد؛ آن‌ها در app_stats، reviews و network_metrics می‌مانند تا هر کراول تاریخچه بسازد بدون دستکاری لیست.

دیاگرام (ER)

erDiagram APPS_REGISTRY_APPLICATION { bigint id PK string package_name UK "unique app identifier" string display_name "optional" string category "Play Store category" boolean is_messaging_app "flags network analysis need" boolean is_iranian_app "selects crawl region/locale" boolean is_active "soft delete flag" datetime created_at datetime updated_at }

مشخصات فیلدها

فیلد	نوع داده	ویژگی‌ها	توضیح و دلیل طراحی
`id`	BigAutoField / BIGINT	PK, Auto Increment	کلید داخلی برای FKهای جدول‌های Storage Consumer. Django پیش‌فرض BigAutoField است؛ به همین دلیل FKها در SQL از `BIGINT` استفاده می‌کنند.
`package_name`	CharField(255)	Unique, Not Null, Indexed	شناسهٔ یکتای اپ در Google Play. کلید ارجاع بین‌سیستمی (Crawler، Network Analyzer، Kafka key) — نه `id` داخلی.
`display_name`	CharField(255)	Nullable	نام نمایشی. Create می‌تواند فقط با package_name باشد. شکاف شناخته‌شده: پر شدن خودکار از Play Store توسط کراولر هنوز پیاده نشده؛ فعلاً همان مقدار ورودی API می‌ماند.
`category`	CharField(100)	Nullable	دستهٔ رسمی Play Store برای گزارش‌گیری. همان شکاف display_name برای تکمیل خودکار صدق می‌کند.
`is_messaging_app`	Boolean	Default: False	فلگ عملیاتی جدا از category: آیا اپ نیاز به جمع‌آوری pcap دارد؟ منطق شبکه با یک Boolean ساده کار می‌کند.
`is_iranian_app`	Boolean	Default: False	بازار هدف ایران. کراولر بر اساس این فلگ Region/Locale را انتخاب می‌کند (`ir/fa` در برابر `us/en`) چون نتایج Play Store (به‌خصوص ریویوها) به کشور/زبان درخواست وابسته‌اند.
`is_active`	Boolean	Default: True	Soft Delete. کراولر فقط `is_active=True` را می‌خواند؛ تاریخچهٔ اپ غیرفعال حفظ می‌شود.
`created_at`	DateTime	auto_now_add	زمان ثبت اولیه.
`updated_at`	DateTime	auto_now	زمان آخرین ویرایش رکورد لیست (نه آمار).

تصمیمات طراحی

جداسازی وضعیت از تاریخچه: هیچ فیلد آماری در این جدول نیست تا Update لیست، روند زمانی را پاک نکند.
Soft Delete: عملیات API «غیرفعال کردن» است، نه حذف فیزیکی.
package_name به‌عنوان کلید بین‌سیستمی: پایدار و معنادار برای producer/consumerهای مستقل.
is_iranian_app: بدون این فلگ، کراول پیش‌فرض US عمدتاً نقدهای غیرایرانی برای اپ‌های ایرانی برمی‌گرداند.

۲. جدول App Stats

مالک: Storage Consumer — فایل 0001_app_stats.sql. ماهیت Append-Only: هر چرخهٔ کراول یک ردیف جدید (نه Update).

دیاگرام (ER)

erDiagram APPS_REGISTRY_APPLICATION ||--o{ APP_STATS : "has many" APPS_REGISTRY_APPLICATION { bigint id PK string package_name UK } APP_STATS { bigint id PK bigint application_id FK bigint min_installs float score bigint ratings bigint reviews_count string version boolean ad_supported timestamptz app_updated_at "last update on Play Store" timestamptz crawled_at "our crawl timestamp" }

مشخصات فیلدها

فیلد	نوع داده	ویژگی‌ها	توضیح و دلیل طراحی
`id`	BIGSERIAL	PK	کلید داخلی؛ جدول با نرخ (تعداد اپ × هر ساعت) رشد می‌کند.
`application_id`	BIGINT FK	Not Null, ON DELETE CASCADE	ارجاع به `apps_registry_application(id)`. آمار بدون اپ مادر بی‌معنی است.
`min_installs`	BIGINT	Nullable	حداقل نصب اعلام‌شده. Nullable چون Play Store گاهی مقدار ندارد؛ نبود یک سنجه نباید کل رکورد را دور بیندازد.
`score`	DOUBLE PRECISION	Nullable	امتیاز کلی (مثلاً 4.3).
`ratings`	BIGINT	Nullable	تعداد کل رتبه‌بندی‌ها.
`reviews_count`	BIGINT	Nullable	تعداد کل نقدهای گزارش‌شده توسط Play Store (متفاوت از تعداد ردیف‌های جدول reviews ما).
`version`	VARCHAR(50)	Nullable	نسخهٔ فعلی اپ.
`ad_supported`	BOOLEAN	Nullable	پشتیبانی از تبلیغات.
`app_updated_at`	TIMESTAMPTZ	Nullable	آخرین به‌روزرسانی خودِ اپ در Play Store — جدا از زمان کراول ما.
`crawled_at`	TIMESTAMPTZ	Not Null	زمان کراول ما. همراه با `application_id` کلید idempotency است.

ایندکس‌ها و قیدها

UNIQUE (application_id, crawled_at) — کلید idempotency در برابر at-least-once بودن Kafka و retryهای producer. همان btree عملاً ایندکس مرکب گزارش‌گیری per-app را هم پوشش می‌دهد.
INDEX (crawled_at) — برای برش‌های زمانی cross-app در Metabase که ایندکس مرکب با leading column روی application_id سرویس نمی‌دهد.

تصمیمات طراحی

Append-Only: پیش‌نیاز تحلیل روند نصب/امتیاز در زمان.
جداسازی app_updated_at از crawled_at: «زمان تغییر اپ» در برابر «زمان مشاهدهٔ ما».
CASCADE: در عمل Soft Delete در سطح اپ است؛ CASCADE برای یکپارچگی در پاک‌سازی دستی/توسعه است.

۳. جدول Reviews

مالک ساختار: Storage Consumer — 0002_reviews.sql. نویسندگان: Storage Consumer برای همهٔ ستون‌ها به‌جز sentiment؛ زیرسیستم Sentiment فقط همان ستون را UPDATE می‌کند. ماهیت Upsert روی review_id.

دیاگرام (ER)

erDiagram APPS_REGISTRY_APPLICATION ||--o{ REVIEWS : "has many" APPS_REGISTRY_APPLICATION { bigint id PK string package_name UK } REVIEWS { bigint id PK bigint application_id FK string review_id UK string user_name int thumbs_up_count smallint score text content timestamptz at string sentiment "POSITIVE NEUTRAL NEGATIVE" timestamptz first_seen_at timestamptz last_synced_at }

مشخصات فیلدها

فیلد	نوع داده	ویژگی‌ها	توضیح و دلیل طراحی
`id`	BIGSERIAL	PK	کلید داخلی جدا از review_id معنادار.
`application_id`	BIGINT FK	Not Null, CASCADE	ارجاع به اپ.
`review_id`	VARCHAR(255)	UNIQUE, Not Null	شناسهٔ یکتای نقد در Play Store؛ ستون کلیدی Upsert.
`user_name`	VARCHAR(255)	Nullable	گاهی در Play Store مخفی است.
`thumbs_up_count`	INTEGER	Default 0	در طول زمان عوض می‌شود؛ دلیل اصلی نیاز به Upsert.
`score`	SMALLINT	Nullable	امتیاز ۱–۵. Nullable چون scraper گاهی score ندارد و نبود سنجه نباید متن نقد را دور بیندازد.
`content`	TEXT	Nullable	متن نقد؛ طول نامحدود.
`at`	TIMESTAMPTZ	Not Null	زمان ثبت نقد در Play Store؛ مبنای روند زمانی در Metabase.
`sentiment`	VARCHAR(20)	Nullable, CHECK ∈ {POSITIVE, NEUTRAL, NEGATIVE}	نتیجهٔ Job تحلیل احساس. Storage Consumer در upsert هرگز این ستون را لمس نمی‌کند تا re-sync کراولر تحلیل را پاک نکند. نوشته می‌شود توسط Sentiment.
`first_seen_at`	TIMESTAMPTZ	Not Null, DEFAULT now()	اولین دیده‌شدن؛ در DO UPDATE لیست نیست و پایدار می‌ماند.
`last_synced_at`	TIMESTAMPTZ	Not Null، از `crawled_at` پیام	زمان همگام‌سازی از Play Store (مهر پیام)، نه auto_now زمان نوشتن ردیف. پیش‌نیاز نگهبان یکنوا.

ایندکس‌ها

UNIQUE (review_id)
INDEX (application_id, at) — کوئری «روند امتیاز یک اپ»
INDEX (at) — برش زمانی cross-app

تصمیمات طراحی

Upsert نه Append-Only: هدف آخرین نسخهٔ هر نقد است، نه تاریخچهٔ ویرایش‌ها.
نگهبان یکنوا: ON CONFLICT (review_id) DO UPDATE … WHERE EXCLUDED.last_synced_at > reviews.last_synced_at — replay کهنه و ترتیب به‌هم‌خورده بین پارتیشن‌ها نمی‌توانند دادهٔ تازه‌تر را بازنویسی کنند.
sentiment زودهنگام در DDL: ستون از مهاجرت reviews ساخته شد تا بعداً ALTER روی جدول بزرگ لازم نباشد؛ مالک نوشتن جداست.

۴. جدول Network Metrics

مالک: Storage Consumer — 0003_network_metrics.sql. Append-Only با کلید idempotency روی analysis_id. ورودی از تحلیل دستی/آفلاین pcap (معمولاً اپ‌های messaging).

دیاگرام (ER)

erDiagram APPS_REGISTRY_APPLICATION ||--o{ NETWORK_METRICS : "has many" APPS_REGISTRY_APPLICATION { bigint id PK string package_name UK } NETWORK_METRICS { bigint id PK bigint application_id FK uuid analysis_id UK string scenario "UPLOAD or DOWNLOAD" float rtt_handshake int retransmission_count int out_of_order_count int spurious_retransmission_count int zero_window_count int tcp_reset_count bigint bytes_transferred_total bigint bytes_payload_total float overhead_ratio string source_pcap_filename timestamptz analyzed_at }

مشخصات فیلدها

فیلد	نوع داده	ویژگی‌ها	توضیح و دلیل طراحی
`id`	BIGSERIAL	PK	کلید داخلی.
`application_id`	BIGINT FK	Not Null, CASCADE	معمولاً اپ‌های `is_messaging_app=True`.
`analysis_id`	UUID	UNIQUE, Not Null	یک UUID به‌ازای هر فایل تحلیل‌شده (تولید Network Analyzer). کلید idempotency: `ON CONFLICT DO NOTHING` برای redelivery؛ اجرای واقعی مجدد تست UUID تازه می‌سازد و ردیف جدید می‌دهد.
`scenario`	VARCHAR(20)	Not Null, CHECK ∈ {UPLOAD, DOWNLOAD}	سناریوی ضبط.
`rtt_handshake`	DOUBLE PRECISION	Nullable	میانگین زمان SYN→SYN-ACK (ms). ممکن است handshake تازه در pcap نباشد.
`retransmission_count`	INTEGER	Default 0	بازفرستادن واقعی داده (بدون spurious).
`out_of_order_count`	INTEGER	Default 0	سگمنت‌های out-of-order که حفرهٔ sequence ساخته‌اند.
`spurious_retransmission_count`	INTEGER	Default 0	بازفرستادن وقتی peer قبلاً ACK کرده؛ جدا از retransmission_count.
`zero_window_count`	INTEGER	Default 0	رخدادهای TCP Zero Window.
`tcp_reset_count`	INTEGER	Default 0	بسته‌های RST.
`bytes_transferred_total`	BIGINT	Not Null	حجم کل بسته‌ها.
`bytes_payload_total`	BIGINT	Not Null	حجم خالص payload بدون هدر.
`overhead_ratio`	DOUBLE PRECISION	Not Null	نسبت سربار هدر؛ ذخیرهٔ جدا برای سادگی Metabase.
`source_pcap_filename`	VARCHAR(255)	Nullable	Traceability به فایل خام.
`analyzed_at`	TIMESTAMPTZ	Not Null، از پیام Analyzer	زمان اجرای تحلیل — نه زمان خواندن پیام توسط consumer و نه auto_now_add.

ایندکس‌ها

UNIQUE (analysis_id)
INDEX (application_id, analyzed_at) — FK ایندکس خودکار در Postgres ندارد؛ این ایندکس صریح هم FK را پوشش می‌دهد هم کوئری per-app زمانی را.

تصمیمات طراحی

تکرار تست ≠ redelivery: بدون Unique روی analysis_id، تحویل مجدد Kafka ردیف تکراری می‌ساخت. با UUID، تست واقعیِ مجدد مجاز می‌ماند.
بدون محدودیت روی (application_id, scenario): تکرار آزمایش در شرایط مختلف باید مجاز باشد.

۵. جدول Dead Letter Events

مالک: Storage Consumer — 0004_dead_letter_events.sql. در طرح اولیه نبود؛ همراه با consumer اضافه شد.

پیام‌هایی را نگه می‌دارد که consumer نتوانسته و هرگز نخواهد توانست ذخیره کند (JSON نامعتبر، فیلد هویتی غایب، نوع نادرست، timestamp بد، scenario ناشناخته، package_name ثبت‌نشده، …). بدون DLQ، retry بی‌پایان یک پیام معیوب پارتیشن را قفل می‌کند؛ دور انداختن خاموش هم داده را بی‌صدا از دست می‌دهد. راه سوم: ثبت اینجا، جلو رفتن offset، بررسی بعدی توسط اپراتور.

فیلد	نوع داده	ویژگی‌ها	توضیح
`id`	BIGSERIAL	PK	کلید داخلی.
`topic`	VARCHAR(255)	Not Null، عضو Unique	تاپیک مبدأ.
`partition`	INTEGER	Not Null، عضو Unique (ستون quoted در SQL)	شمارهٔ پارتیشن.
`kafka_offset`	BIGINT	Not Null، عضو Unique	پیشوند `kafka_` چون `offset` در Postgres رزرو است.
`kafka_key`	TEXT	Nullable	معمولاً package_name.
`raw_payload`	BYTEA	Nullable	بدنهٔ خام؛ BYTEA چون ممکن است UTF-8 معتبر نباشد.
`error_reason`	TEXT	Not Null	دلیل خوانا برای اپراتور.
`occurred_at`	TIMESTAMPTZ	Default now(), INDEX DESC	کوئری عملیاتی: «اخیراً چه رد شده؟»

تصمیمات طراحی

در Postgres نه Kafka DLQ: همان تراکنش بچ که offset را جلو می‌برد؛ و قابل کوئری از Metabase/psql.
UNIQUE (topic, partition, kafka_offset): هویت رکورد Kafka؛ reset offset ردیف تکراری نمی‌سازد.
بدون FK به applications: شایع‌ترین علت DLQ خودِ ناشناخته بودن package است.
خطای موقت ≠ دائمی: قطع DB / deadlock هرگز اینجا نمی‌روند؛ با backoff retry می‌شوند.

۶. مالکیت اسکیما و مهاجرت‌ها

app_api (Django): تنها مالک apps_registry_application.
storage_consumer: مالک app_stats، reviews، network_metrics، dead_letter_events از طریق SQL شماره‌دار در persistence/sql/؛ وضعیت اعمال در جدول storage_consumer_migrations. جدول اپ را فقط می‌خواند (resolve کردن package_name → id).
sentiment: مدل/مهاجرت جدا برای reviews ندارد؛ فقط UPDATE ستون sentiment را انجام می‌دهد.

هرگز نباید برای چهار جدول Storage Consumer مدل Django managed ساخته شود — دو ابزار روی یک DDL می‌جنگند. اگر روزی از API خوانده شدند، راه درست managed = False است.

شکاف‌های شناخته‌شده

display_name / category: تکمیل خودکار از کراول هنوز نیست؛ payload تاپیک app-stats این فیلدها را حمل نمی‌کند.
sentiment: تا وقتی Job Sentiment اجرا نشود NULL می‌ماند؛ upsert کراولر آن را پاک نمی‌کند.

۷. منابع حقیقت در مخزن

منبع	پوشش
`app_api/apps_registry/models.py` + migrations	جدول اپ
`storage_consumer/persistence/sql/0001_.sql` … `0004_.sql`	چهار جدول دادهٔ ماندگار + DLQ
`storage_consumer/persistence/migrator.py`	اعمال SQL و جدول `storage_consumer_migrations`
مستند Storage Consumer	تضمین‌های نوشتن، idempotency، DLQ
مستند Network Analyzer	تولید پیام network-metrics و تعریف متریک‌ها
مستند Sentiment	نوشتن و overwrite ستون sentiment