DB schema

[t3m8ch]

https://dbdiagram.io/d/623c921ebed6183873f26ce4

[t3m8ch]

[t3m8ch]

This scheme is logically divided into several contexts:

1. Users & authorization
2. Author & subscriptions
3. Posts
4. Payments

Users & authorization
This scheme involves working with sessions. There are three tables in this context:

1. user
2. user_session - created after entering code from email; when user exits, is_active becomes false, when created it becomes true; device and client fields store the user's device name and its client (in MVP - browser name) respectively
3. user_authentication_code - created when user sends email to the system during authorization

Author & subscriptions
This context consists of 4 tables:

1. author - this table is linked to the user's table by a many-to-one relationship, since the user may want to create multiple pages (you should probably rename this table to blog)
2. subscription_option
3. user_subscription - M2M for the user who subscribes and the author who is subscribed
4. user_tracking - is created when a user clicks the "Отслеживать" button (check design)

Posts
This context consists of 4 tables:

1. post - the content field contains text in Notion-like format
2. post_preview - settings of the preview which a user who has not opened the full post sees
3. post_like
4. post_access - post access settings; there are several types of access: open access (i.e., for everyone), subscribers only, subscribers and purchase, purchase only; for the second and third you must set the subscription_option_id field; for the third and fourth you must set the purchase_price field

Payments
The transaction table stores information about all payments. Each transaction is linked to a session to make it easier to detect fraudulent actions

[kirillzhosul]

#13 (comment)