Last Updated: 2026-03-29
Source of Truth:packages/core-database/src/schema.ts
This document defines the exact database columns for the LogicSpike platform, split by our Building (Global) vs Floor (Tenant) strategy.
🌍 Level 1: The Building (Global Data)
These tables live in the public schema (or shared space).
1. users (Identity)
Represents a human being. Simplified to just "Who are you?".
id(PK):text— Unique identifier.email(UK):text— Unique constraint.name:text— Display name.avatar_url:text— Profile picture.default_tenant_id:text(FK ->tenants.id) — Last active workspace (for UX convenience).is_verified:boolean— Email verification status.phone:text(Unique, Nullable) — Phone number.phone_verified:boolean— Phone verification status.is_two_factor_enabled:boolean(Default false).two_factor_method:text(email,phone,totp).two_factor_secret:text— Encrypted TOTP Secret.created_at:timestamp.
2. user_notification_preferences (Per-User Toggles)
One row per user — global notification preferences.
user_id(PK, FK ->users.id).security_alerts:boolean(Default true, always enforced server-side).workspace_invites:boolean(Default true).billing_updates:boolean(Default true).product_updates:boolean(Default false).updated_at:timestamp.
3. verifications (OTP Codes)
Short-lived codes for Email/Phone verification.
id:text(PK).identifier:text(Email or Phone).code:text(The 6-digit OTP).type:text(email,phone,login).expires_at:timestamp.attempts:integer(Security: max 3 tries).
4. user_auth_providers (Security)
Stores credentials. Separated to allow multiple login methods per user.
id(PK):text.user_id(FK ->users.id): Link to identity.provider:text—email,google.provider_user_id:text— "sub" from OAuth, or NULL for email.password_hash:text— scrypt hash (NULL for OAuth).created_at:timestamp.
5. tenants (The Floors)
Represents a Workspace/Organization.
id(PK):text— (e.g.,ls_xxx).name:text— Display name (e.g., "Acme Corp").slug:text(UK) — URL friendly identifier (e.g.,acme-corp).status:text—onboarding,active,suspended.plan_id:text(FK ->plans.id) — Current plan.logo_url:text— Workspace logo image URL.database_config:jsonb— (Hybrid Router){ type: "isolated", url: "..." }orNULL.created_at:timestamp.
6. memberships (The Keys)
Links User -> Tenant with a Role.
id(PK):text.user_id(FK ->users.id).tenant_id(FK ->tenants.id).role_id:text(FK ->roles.id) — The assigned role.is_owner:boolean— If true, bypasses role checks (Super Admin for this tenant).joined_at:timestamp.
7. roles (System + Custom Definitions)
Roles define what doors a key opens. Can be global (system) or per-tenant (custom).
id(PK):text(e.g.,role_owner,role_editor).tenant_id:text(FK ->tenants.id) —null= system role, UUID = custom tenant role.name:text.description:text.is_system:boolean— True for global roles, False for custom tenant roles.permissions:jsonb— Array of strings["blog:posts.update", "team:members.invite"].
8. invitations (The Lobby)
Pending access requests.
id(PK):text(Secure Token).tenant_id(FK ->tenants.id).email:text— Who is invited.role_id:text(FK ->roles.id) — What role they will get.invited_by:text(FK ->users.id).expires_at:timestamp.status:text—pending,accepted,revoked.
💰 Billing & Plans
9. plans (The Menu)
Predefined subscription tiers.
id(PK):text(free,starter,pro,business).name:text.price_monthly:integer— USD cents.price_monthly_inr:integer— INR paise (e.g., 99900 = ₹999).price_yearly_inr:integer— INR paise (nullable for free plan).yearly_discount_pct:integer(Default 0).trial_days:integer(Default 0).razorpay_plan_id_monthly:text— Razorpay Plan ID for monthly billing.razorpay_plan_id_yearly:text— Razorpay Plan ID for yearly billing.stripe_price_id_monthly:text— (Phase 8, nullable).stripe_price_id_yearly:text— (Phase 8, nullable).included_seats:integer(Default 0).extra_seat_cost_inr:integer— Paise per extra seat/month.is_per_seat:boolean(Default false).is_public:boolean— True for website pricing, False for custom/legacy.
10. plan_features (The Bundle Content)
Defines what a plan includes.
plan_id(PK, FK ->plans.id).service_code(PK, FK ->services.code).limits:jsonb— e.g.{ "posts": 100, "staff": 2 }.
11. subscriptions (The Contract)
Tracks the lifecycle of a plan subscription per tenant.
id(PK):text.tenant_id(FK ->tenants.id).plan_id(FK ->plans.id).status:text(active,trialing,past_due,canceled).billing_cycle:text(monthly,yearly).current_period_end:timestamp.has_used_trial:boolean(Default false).trial_end:timestamp.cancel_at_period_end:boolean(Default false).pending_plan_id:text(FK ->plans.id) — Scheduled downgrade.provider:text(razorpay,stripe).razorpay_customer_id:text.razorpay_subscription_id:text.stripe_customer_id:text— (Phase 8, nullable).stripe_subscription_id:text— (Phase 8, nullable).payment_method_id:text— (Stripe Phase 8, null for Razorpay).created_at:timestamp.updated_at:timestamp.
12. service_limits (Limit Definitions)
service_code(PK, FK ->services.code).limit_key(PK):text— e.g.,posts,storage_mb,seats.display_name:text.default_value:integer(Default 0).unit:text—MB,count,boolean.
13. plan_service_limits (Limits Per Plan)
plan_id(PK, FK ->plans.id).service_code(PK, FK ->services.code).limit_key(PK):text.value:integer—-1= unlimited,0= disabled.
14. tenant_coins (Coin Wallet)
tenant_id(PK, FK ->tenants.id).balance:integer(Default 0).updated_at:timestamp.
15. coin_transactions (Ledger)
id(PK):text.tenant_id(FK ->tenants.id).amount:integer— positive = credit, negative = debit.balance_after:integer.reason:text—plan_purchase,addon_renewal,manual_top_up.description:text.reference_id:text— Razorpay payment_id or order_id.created_at:timestamp.
16. coin_packs (Purchasable Coin Bundles)
id(PK):text(small,medium,large).name:text.price_paise:integer— INR paise.coins:integer.bonus_pct:integer(Default 0).razorpay_price_id:text.is_active:boolean.sort_order:integer.
17. addon_catalog (Available Add-ons)
id(PK):text.display_name:text.service_code(FK ->services.code).limit_key:text.coin_cost_per_unit:integer.unit_label:text—GB,seat.is_recurring:boolean.is_active:boolean.
18. tenant_addons (Purchased Add-ons)
id(PK):text.tenant_id(FK ->tenants.id).addon_type(FK ->addon_catalog.id).quantity:integer(Default 1).status:text(active,paused).next_renewal:timestamp.created_at:timestamp.
19. processed_payment_events (Webhook Idempotency)
provider_event_id(PK):text.provider:text(razorpay,stripe).event_type:text.processed_at:timestamp.
🔒 Level 2: The Floor (Tenant Business Data)
These tables contain YOUR USERS' data. Every single query here MUST have WHERE tenant_id = ?.
20. services (Global Catalog)
code(PK):text(blog,media,platform).name:text.
21. tenant_services (The Entitlements)
The Source of Truth for what this tenant can actually do.
tenant_id(PK, FK ->tenants.id).service_code(PK, FK ->services.code).enabled:boolean.limits:jsonb— Merged limits (Plan + Add-ons).source:text(plan,addon).updated_at:timestamp.
22. api_keys (Integrations)
id(PK):text— Prefixed ID (e.g.,key_xxx).tenant_id(FK ->tenants.id) — CRITICAL.name:text— User-given label.description:text— Optional note.key_hash:text— SHA-256 hash (raw key never stored).prefix:text—ls_(secret) orpk_(publishable).type:text(Defaultsecret) —secretorpublishable.permissions:jsonb— Array of PBAC strings (e.g.,["blog:posts.read", "media:files.write"]).allowed_domains:jsonb— Domain locking for publishable keys.status:text—active,revoked.expires_at:timestamp— Configurable: 30d, 90d, 1y, or never.last_used_at:timestamp.created_at:timestamp.created_by:text— User ID who created the key.
23. media_files (Storage)
id(PK):text.tenant_id(FK ->tenants.id) — Nullable for user-scoped uploads (avatar, etc.).service_code(FK ->services.code).file_name:text.file_size:integer— Bytes.mime_type:text.key:text— Storage path (R2/S3).status:text.is_public:boolean(Default true).uploaded_by:text.created_at:timestamp.
📡 Communication Service
These tables live in the communication Postgres schema for isolation.
24. communication.provider_configs
id(PK):text.tenant_id(FK ->tenants.id) — Nullable = System Default.type:text—twilio,smtp,resend.name:text.encrypted_config:text.mask:text— Display only.enabled:boolean.priority:integer.metadata:jsonb.created_at:timestamp.updated_at:timestamp.
25. communication.message_logs
id(PK):text.tenant_id(FK ->tenants.id).channel:text—email,sms.direction:text(Defaultoutbound).recipient:text.subject:text.content:jsonb—{ html, text, template... }.status:text—queued,sent, etc.provider_id(FK ->provider_configs.id).provider_message_id:text.cost:integer.idempotency_key:text.scheduled_at:timestamp.sent_at:timestamp.created_at:timestamp.metadata:jsonb.
26. communication.message_events
id(PK):text.message_id(FK ->message_logs.id).status:text.reason:text.source:text—internal,provider.raw_payload:jsonb.occurred_at:timestamp.
📜 Level 3: Platform Logging
These tables live in the dedicated logging database package (packages/log-db) to ensure high-volume log writes do not impact core transaction performance.
27. system_logs (Distributed Tracing & Events)
id(PK):uuid(Default random).service:varchar(50)— Origin service (e.g.,gateway,blog-service).environment:varchar(50)(Defaultproduction).level:varchar(20)—ERROR,INFO,WARN.message:text.trace_id:varchar(100)— For correlating requests across microservices.tenant_id:varchar(100)— User Context.user_id:varchar(100)— User Context.status_code:integer— HTTP Response Code.method:varchar(10)— HTTP Method.path:text— Target URL path.meta:jsonb— Deep data metrics{ stack, headers, body, duration }.created_at:timestamp.
📝 Naming Conventions
- IDs: Text/String (NanoID preferred over UUID for URL friendliness).
- Timestamps:
created_at,updated_at. - Foreign Keys:
underscore_case(user_id,tenant_id). - Boolean:
is_verified,is_active(Prefix withis_orhas_).