Design Foursquare (Check-ins and Recommendations)

CREATE TABLE venues (
    venue_id        UUID PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    category_id     INT NOT NULL,
    location        GEOMETRY(Point, 4326) NOT NULL,
    lat             DECIMAL(10,7), lng DECIMAL(10,7),
    address         TEXT, city VARCHAR(100), country_code CHAR(2),
    phone           VARCHAR(20), website TEXT,
    price_tier      SMALLINT, rating DECIMAL(2,1),
    checkin_count   INT DEFAULT 0, tip_count INT DEFAULT 0,
    hours           JSONB, verified BOOLEAN DEFAULT FALSE,
    created_at      TIMESTAMP, updated_at TIMESTAMP
);
CREATE INDEX idx_location ON venues USING GIST(location);

CREATE TABLE checkins (
    checkin_id      UUID PRIMARY KEY,
    user_id         UUID NOT NULL, venue_id UUID NOT NULL,
    lat DECIMAL(10,7), lng DECIMAL(10,7),
    shout TEXT, photo_url TEXT,
    visibility ENUM('public','friends','private') DEFAULT 'friends',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id, created_at DESC),
    INDEX idx_venue (venue_id, created_at DESC)
);

CREATE TABLE tips (
    tip_id  UUID PRIMARY KEY,
    venue_id UUID NOT NULL, user_id UUID NOT NULL,
    text TEXT NOT NULL, photo_url TEXT,
    upvote_count INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);