Ruben Fischer
f14515e9cf
Features: - Add LinkedIn OAuth integration and auto-posting functionality - Add scheduler service for automated post publishing - Add metadata field to generated_posts for LinkedIn URLs - Add privacy policy page for LinkedIn API compliance - Add company management features and employee accounts - Add license key system for company registrations Fixes: - Fix timezone issues (use UTC consistently across app) - Fix datetime serialization errors in database operations - Fix scheduling timezone conversion (local time to UTC) - Fix import errors (get_database -> db) Infrastructure: - Update Docker setup to use port 8001 (avoid conflicts) - Add SSL support with nginx-proxy and Let's Encrypt - Add LinkedIn setup documentation - Add migration scripts for schema updates Services: - Add linkedin_service.py for LinkedIn API integration - Add scheduler_service.py for background job processing - Add storage_service.py for Supabase Storage - Add email_service.py improvements - Add encryption utilities for token storage Co-Authored-By: Claude Sonnet 4.5 <noreply@anthropic.com>
95 lines
3.3 KiB
SQL
95 lines
3.3 KiB
SQL
-- Migration: Add License Key System with Company Limits
|
|
-- This migration adds:
|
|
-- 1. license_keys table for managing license keys
|
|
-- 2. company_daily_quotas table for tracking daily usage
|
|
-- 3. license_key_id reference in companies table
|
|
|
|
-- ==================== LICENSE KEYS TABLE ====================
|
|
|
|
CREATE TABLE IF NOT EXISTS license_keys (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
key TEXT UNIQUE NOT NULL,
|
|
description TEXT,
|
|
|
|
-- Limits (stored here, NOT in companies table)
|
|
max_employees INT NOT NULL DEFAULT 5,
|
|
max_posts_per_day INT NOT NULL DEFAULT 10,
|
|
max_researches_per_day INT NOT NULL DEFAULT 5,
|
|
|
|
-- Usage tracking
|
|
used BOOLEAN DEFAULT FALSE,
|
|
company_id UUID REFERENCES companies(id) ON DELETE SET NULL,
|
|
used_at TIMESTAMP WITH TIME ZONE,
|
|
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Index for fast lookups
|
|
CREATE INDEX IF NOT EXISTS idx_license_keys_key ON license_keys(key);
|
|
CREATE INDEX IF NOT EXISTS idx_license_keys_used ON license_keys(used);
|
|
CREATE INDEX IF NOT EXISTS idx_license_keys_company_id ON license_keys(company_id);
|
|
|
|
-- RLS Policies (Admin only)
|
|
ALTER TABLE license_keys ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Service role has full access to license_keys"
|
|
ON license_keys FOR ALL
|
|
USING (auth.jwt()->>'role' = 'service_role');
|
|
|
|
-- ==================== EXTEND COMPANIES TABLE ====================
|
|
|
|
-- Add license key reference to companies table
|
|
-- (limits are stored in license_keys table, not duplicated here)
|
|
ALTER TABLE companies ADD COLUMN IF NOT EXISTS license_key_id UUID REFERENCES license_keys(id);
|
|
|
|
-- Index
|
|
CREATE INDEX IF NOT EXISTS idx_companies_license_key_id ON companies(license_key_id);
|
|
|
|
-- ==================== COMPANY DAILY QUOTAS TABLE ====================
|
|
|
|
-- Track daily usage per company
|
|
CREATE TABLE IF NOT EXISTS company_daily_quotas (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
|
|
date DATE NOT NULL,
|
|
posts_created INT DEFAULT 0,
|
|
researches_created INT DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
|
|
UNIQUE(company_id, date)
|
|
);
|
|
|
|
-- Index for fast daily lookups
|
|
CREATE INDEX IF NOT EXISTS idx_company_daily_quotas_company_date ON company_daily_quotas(company_id, date);
|
|
|
|
-- RLS Policies
|
|
ALTER TABLE company_daily_quotas ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Users can view own company quotas"
|
|
ON company_daily_quotas FOR SELECT
|
|
USING (
|
|
company_id IN (
|
|
SELECT company_id FROM profiles WHERE id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Service role has full access to company_daily_quotas"
|
|
ON company_daily_quotas FOR ALL
|
|
USING (auth.jwt()->>'role' = 'service_role');
|
|
|
|
-- ==================== TRIGGERS ====================
|
|
|
|
DROP TRIGGER IF EXISTS update_license_keys_updated_at ON license_keys;
|
|
CREATE TRIGGER update_license_keys_updated_at
|
|
BEFORE UPDATE ON license_keys
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_company_daily_quotas_updated_at ON company_daily_quotas;
|
|
CREATE TRIGGER update_company_daily_quotas_updated_at
|
|
BEFORE UPDATE ON company_daily_quotas
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|