Audit unused table columns

[Throne3d]

Check the db/structure.sql file for all the columns that are extant, then figure out which of them are unused or unexposed, and either expose them or remove them as necessary.

unused (partial and maybe inaccurate; things to check which may be used):

• [ ] board_sections » status (only used by scrapers? not exposed; #284)
• [ ] boardviews » notify{message,email}?
• [ ] messages » {visible}_{in,out}box
• [ ] post_tags » suggested
• [ ] postviews » notify{message,email}
• [ ] repl{ies,y_drafts} » thread_id

other queries:

• [ ] boards » pinned (expose in CRUD somewhere? is used for Sandboxes)
• [ ] characters » template_name (rename to template_nickname or nickname or something? current phrasing is unclear), pb (rename to facecast perhaps?)
• [ ] icons » has_gallery (use a cache counter? or subquery but cache counter is better and you might be able to get it done boolean-ly or else you can do "> 0" on the column representing number of attached galleries)
• [ ] messages » message (rename to content?)
• [ ] posts » last_{user,reply}_id doesn't update when the last reply in a post is updated etc and so the state can become easily inconsistent if using the development console; see if there's a better way to handle this
• [ ] split post "reply" bit from relevant metadata (see #421), set up posts to have a specific ordering so we can pave the way to move things around later (see #59)
• [ ] users » active_character_id looks like it could be done better – or, since we don't use it in individual threads anymore, only when a user joins a thread… it is probably not all that important to even keep? when joining a thread it's not clear what user you're gonna use, especially if you're using lots at once.
• [ ] users » layout, default_view, should maybe have clear defaults set? don't seem to in the database.
• [ ] should we be using more indexes in different places? I don't actually know where it's recommended to use them nor where we currently use them nor what queries rely on them a lot.
• [ ] foreign keys? since Rails 4.2 supports them properly – what's distinct about them from what we're currently doing, will they automatically nilify if we delete the row that's related, should we investigate them?
• [ ] investigate if more columns should use citext? specifically but probably not limited to characters » template_name, screenname, pb, setting, for search purposes? (this textual setting column will be gone soon as per #34 but still)

[Marri]

messages.visible_*box is definitely used; we use that for deletion, since otherwise deleting something from your outbox would delete it from the recipient's inbox as well. See Messages Controller.

PasswordReset.used? is definitely a thing, see .unused scope.

messages.marked_* was used and got removed cause it was hard with threading and not actually used at all; we can probably drop until and unless we want it back.

board sections notify_* was a forward looking thing. We should probably sit and think about how we're designing notifications before we drop these in case we want them.

post_tags.suggested was also forward looking and seems likely to be helpful once we build it.

exposing board_section.status seems fine but I am worried it may go the way of "series.complete" in AO3, in that no one actually changes it.

ugh threads. ugh. i should probably look/care at some point.

I am disinclined to change db architecture because it's hard to use in the dev console; if that's the only pain point, that feels like the answer is "be more careful in the dev console".

I do not much want to use foreign_keys in the same way I don't much want to use triggers; that feels like an application layer failure that we're making the DB do work to compensate for, but we can look I guess.

421's prior is in fact reply ordering independent of ID. that first.

defaults sound good, index analysis sounds good though the downside is usually just space (and sometimes slower updates but we're probably not big enough to care) and citext sounds good though yes we'll probably wind up pulling some out (I expect PB/facecast to also get pulled into a tag, for multi facecast people)

[Throne3d]

I am disinclined to change db architecture because it's hard to use in the dev console; if that's the only pain point, that feels like the answer is "be more careful in the dev console".

It's also a pain in the scraper, and a thing we plausibly have to take into account in controllers, and could be annoying when we decide to set up ordering of replies – I'm not sure there is a way to make it much easier than our current system but we could at least make it a single "last_reply" then fetch the user from that, instead of having a separate "last_user"? (This could potentially be slightly more memory expensive, but not much more, we'd just have to be slightly careful when making the transition – also I don't think post lists are our main memory concern.)

exposing board_section.status seems fine but I am worried it may go the way of "series.complete" in AO3, in that no one actually changes it.

Most people seem to care about metadata at least a little or else are prodded into caring (and if we get the feature a lot of people would find convenient, where there are helpers allowed to edit specific people's metadata, this seems likely to be a nonissue – #239 or #4 or thereabouts). <3