Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/santiagodc8/tu_perfil.net/llms.txt

Use this file to discover all available pages before exploring further.

The database schema is managed as a series of numbered SQL migration files located in supabase/migrations/. Each file is additive — it extends the schema without breaking changes from previous migrations. Run them in order, once, on a fresh Supabase project.

​
How to run migrations

TuPerfil.net does not use the Supabase CLI for migrations. Instead, paste each file’s contents into the SQL Editor in the Supabase dashboard and click Run:
1

Open the SQL Editor

In your Supabase project, click SQL Editor in the left sidebar, then click New query.
2

Paste and run each migration in order

Open supabase/migrations/001_initial_schema.sql in your editor, copy its contents, paste into the SQL Editor, and click Run. Repeat for files 002 through 019, in numeric order.
3

Verify success

After each migration, the SQL Editor shows a success message and the row count affected. If a migration fails, read the error message — most errors indicate that you ran migrations out of order or skipped one.
Always run migrations in numeric order. Many migrations depend on tables or functions created by earlier ones. Running them out of order will cause errors.

​
Migration reference

​
001 — Initial schema

File: 001_initial_schema.sql Creates the foundational schema for the entire application:
  • categories table — id, name, slug, color, created_at
  • articles table — id, title, slug, content, excerpt, image_url, category_id, published, views, created_at, updated_at, author_id
  • contacts table — id, name, email, message, read, created_at
  • update_updated_at() trigger function on articles
  • RLS policies for all three tables
  • article-images storage bucket with public read and authenticated write policies
  • Seeds the six initial categories (Perfil Político, Perfil Judicial, Perfil Salud, Perfil Deportivo, Perfil Regional, Perfil Internacional)
File: 002_add_featured.sql Adds a featured BOOLEAN NOT NULL DEFAULT false column to articles, with a partial index (WHERE featured = true) for fast lookups of the featured article used on the homepage.

​
003 — Update category colors

File: 003_update_category_colors.sql Updates the color values for all six categories to the final design palette:
CategoryNew color
Perfil Político#2563EB
Perfil Judicial#DC2626
Perfil Salud#16A34A
Perfil Deportivo#EAB308
Perfil Regional#9333EA
Perfil Internacional#0891B2

​
004 — Increment views RPC

File: 004_increment_views_rpc.sql Adds the increment_views(article_id UUID) RPC function. It atomically increments the views counter on an article without a read-modify-write cycle.
This function is superseded by the version in migration 014, which also inserts a row into page_views. The 014 version is what runs in production.

​
005 — Breaking news

File: 005_breaking_news.sql Creates the breaking_news table for the urgent banner displayed at the top of the public site. The table is designed for a single active row at a time:
  • text TEXT — banner message
  • link TEXT — optional destination URL
  • active BOOLEAN — whether the banner is visible
Includes the update_updated_at() trigger and RLS (public read, authenticated write). Seeds one inactive row.

​
006 — Scheduled publishing

File: 006_scheduled_publishing.sql Adds published_at TIMESTAMPTZ to articles. When NULL, a published article appears immediately. When set to a future timestamp, the article remains hidden until that time passes. The public read RLS policy is updated to: 
USING (
  (published = true AND (published_at IS NULL OR published_at <= now()))
  OR auth.role() = 'authenticated'
)

​
007 — Author name

File: 007_author_name.sql Adds author_name TEXT NOT NULL DEFAULT 'Redacción TuPerfil.net' to articles. This avoids a JOIN to auth.users (which has no public display name) when rendering the byline. File: 008_gallery.sql Adds gallery JSONB NOT NULL DEFAULT '[]' to articles for storing an ordered array of image URLs used in in-article image galleries.

​
009 — Tags

File: 009_tags.sql Creates a many-to-many tagging system:
  • tags table — id, name (unique), slug (unique), created_at
  • article_tags junction table — article_id, tag_id (composite primary key)
  • Indexes on both foreign keys and tags.slug
  • RLS: public read on both tables, authenticated write

​
010 — Newsletter subscribers

File: 010_newsletter.sql Creates the subscribers table for the email newsletter:
  • email TEXT UNIQUE — subscriber address
  • name TEXT — optional display name
  • active BOOLEAN DEFAULT true
  • unsubscribed_at TIMESTAMPTZ
RLS allows anonymous users to insert (subscribe), while authenticated users can read, update, and delete.

​
011 — Comments

File: 011_comments.sql Creates the comments table with a moderation workflow:
  • article_id UUID — parent article (CASCADE on delete)
  • author_name TEXT, author_email TEXT
  • content TEXT
  • approved BOOLEAN DEFAULT false — comments are hidden until an admin approves them
RLS policy summary: anonymous users can INSERT and SELECT approved comments; authenticated users can SELECT all (for moderation), UPDATE (approve/reject), and DELETE.

​
012 — User roles

File: 012_user_roles.sql Creates the profiles table linked to auth.users, with a role TEXT CHECK (role IN ('admin', 'editor')). Key behaviours:
  • The handle_new_user() trigger automatically creates a profile row whenever a user is added via Supabase Auth. The very first user in the system receives the admin role; all subsequent users receive editor.
  • The is_admin() helper function checks the current user’s role and is used in RLS policies for sensitive operations.
  • A backfill INSERT ... SELECT assigns roles to any users that existed before this migration ran.

​
013 — Ads

File: 013_ads.sql Creates the ads table for the banner advertising system:
  • title TEXT — internal label
  • image_url TEXT — ad creative
  • link_url TEXT — click destination
  • position TEXT DEFAULT 'sidebar' — placement slot (sidebar, header, or between_articles)
  • active BOOLEAN DEFAULT true
  • sort_order INT DEFAULT 0
RLS: anonymous users can read active ads; authenticated users have full CRUD.

​
014 — Page views

File: 014_page_views.sql Creates the page_views table for detailed analytics:
  • article_id UUID — the viewed article
  • viewed_at TIMESTAMPTZ
  • referrer TEXT — raw referrer URL
  • referrer_source TEXT — normalized source (direct, google, facebook, twitter, whatsapp, other)
Also updates increment_views() to atomically increment articles.views and insert a row into page_views in one call.

​
015 — Soft delete

File: 015_soft_delete.sql Adds deleted_at TIMESTAMPTZ DEFAULT NULL to articles. Articles moved to the trash are not deleted from the database; they are excluded from public queries by the updated RLS policy: 
USING (published = true AND deleted_at IS NULL)
Also updates increment_views() to skip soft-deleted articles. File: 016_popular_this_week.sql Adds the popular_articles_this_week(lim INT DEFAULT 5) RPC function, which returns the most-viewed articles in the past 7 days from page_views: 
CREATE OR REPLACE FUNCTION popular_articles_this_week(lim INT DEFAULT 5)
RETURNS TABLE (
  article_id UUID,
  view_count BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT pv.article_id, COUNT(*) AS view_count
  FROM page_views pv
  INNER JOIN articles a ON a.id = pv.article_id
  WHERE pv.viewed_at >= now() - interval '7 days'
    AND a.published = true
    AND a.deleted_at IS NULL
  GROUP BY pv.article_id
  ORDER BY view_count DESC
  LIMIT lim;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
File: 017_related_by_tags.sql Adds related_articles_by_tags(p_article_id UUID, p_category_id UUID, lim INT DEFAULT 4), which returns articles that share the most tags with a given article. Results include the full article fields needed to render an ArticleCard, sorted by number of shared tags descending. File: 018_trending_24h.sql Adds trending_articles_24h(lim INT DEFAULT 5) for the “Trending” widget on the homepage and sidebar: 
CREATE OR REPLACE FUNCTION trending_articles_24h(lim INT DEFAULT 5)
RETURNS TABLE (
  article_id UUID,
  view_count BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT pv.article_id, COUNT(*) AS view_count
  FROM page_views pv
  INNER JOIN articles a ON a.id = pv.article_id
  WHERE pv.viewed_at >= now() - interval '24 hours'
    AND a.published = true
    AND a.deleted_at IS NULL
  GROUP BY pv.article_id
  ORDER BY view_count DESC
  LIMIT lim;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

​
019 — Ad tracking

File: 019_ad_tracking.sql Creates the ad_events table for impression and click tracking:
  • ad_id UUID — references ads(id) (CASCADE on delete)
  • event_type TEXT CHECK (event_type IN ('impression', 'click'))
Also adds ad_metrics(days INT DEFAULT 30), an RPC that returns impression and click counts per ad for any time window: 
SELECT
  e.ad_id,
  COUNT(*) FILTER (WHERE e.event_type = 'impression') AS impressions,
  COUNT(*) FILTER (WHERE e.event_type = 'click') AS clicks
FROM ad_events e
WHERE e.created_at >= now() - (days || ' days')::interval
GROUP BY e.ad_id;
Anonymous users can INSERT events; authenticated users can SELECT (for the admin analytics dashboard).

Build docs developers (and LLMs) love

Get started for freeTalk to us